Tech Support Guy banner
Status
Not open for further replies.

Excel index error?

Solved 
1K views 7 replies 3 participants last post by  nioc98 
#1 ·
Problem:
I am using the formula: =INDEX(G3:G12,COUNTA(G3:G12)) to extract the last number in a range of 10 cells G3:G12. Some of the cells are blank.

It works until I enter a formula (for example in cell G4: =IF(B4="","",G3) to keep the G3:G15 cells blank if an adjacent cell, say B4, is blank, but reveal a value from the preceding cell, G3, if the cell B4 is NOT blank.

The =INDEX(G3:G12,COUNTA(G3:G12)) formula only works if all the cells G3:G15 contain values.

Solution?
 
#2 ·
Hi Nioc,
you've got 2 different ranges G3:G12 and G3:G15 is the second range a typo?

Using COUNTA you are counting the number of cells that are not empty, but that is not guaranteed to give you the last number in a range.

If this is like your earlier problem I assisted with, and the numbers are increasing sequentially, try just using the MAX function, because the last number will be the highest.

If not give a bit more detail and I'll try and help.
 
#3 ·
Hi, Promor!
Thanks for help on last item.
Yes the G3 thing IS a typo. It should be G3:G12 in both cases.
Problem is that, in this case, the numbers are not increasing sequentially. They are differing randomly [fuel price dependent], so the last number is not necessarily greater or lesser.
When I get home, I'll send you a copy of the spreadsheet so you can see what I'm at. It's not complex, unless you are me!
Best
Nioc98
 
#7 ·
That was me being dopey, sorry. Here's the link.

https://exceljet.net/formula/get-value-of-last-non-empty-cell

The reason your formula didn't work is that your index function wants you to return the Nth row in that series. If you use count or counta, to see how many valid cells there are, you might have 5 valid values, but using that one piece of information would only point to the fifth row in your series - your fifth valid value might be on a different row entirely.
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top