Log In
Register
Home
December 12, 2016
Offline
I've written a formula as below to calculate the value difference of the last two cells in the same column while the address of them are not fixed.
The first part in red color of the formula can catch the value of the last cell definitely even been moved up or down. However, I go stuck on the second part in blue of the formula to catch the value of the 2nd last cell. I know very well the address of the cell H15 in this case will be changed dynamically. So, how to re-write it? Thanks for any comment.
{=INDEX(H:H,MAX(ROW(H:H)*NOT(ISBLANK(H:H))))-INDEX(H:H,MAX(ROW(H1:H15)*NOT(ISBLANK(H1:H15))))}
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hello,
Use LARGE instead of MAX, then you can decide if you want the last, second last or fourth last and so forth.
{=INDEX(H:H,LARGE(ROW(H:H)*NOT(ISBLANK(H:H)),1))-INDEX(H:H,LARGE(ROW(H:H)*NOT(ISBLANK(H1:H15)),2))}
Br,
Anders
Update:
I played around a little and you can actually clean up the formula a bit. Try following formula:
=INDEX(H:H,LARGE(IF(H:H<>"",ROW(H:H)),1))-INDEX(H:H,LARGE(IF(H:H<>"",ROW(H:H)),2))
If you are using Excel 365 or 2019 then you don't need press CTRL + SHIFT+ ENTER for this formula to work, but for older versions you of course need to.
Answers Post
1 Guest(s)
