 # COUNTA problem

• Problem:
I am using the formula =INDEX(G3:G12,COUNTA(G3:G12)) to extract the last number from a column of numbers.
It will only work when all 10 cells in the column are filled.
However, I need it to work no matter how many cells are filled. The cells G3:G12 contain formulas of the type: =IF(B5="","",G4).
Why will it not work? How do I make it work?

• Re: COUNTA problem

Try this:

Code
``=LOOKUP(9.99999999999999E+307,G3:G12)``

Bruce :cool:

• Re: COUNTA problem

Many thanks, skywriter. I don't see why it works, but it works!

• Re: COUNTA problem

My pleasure, thanks for the feedback. :cool:

Let me know if you want to know how it works.

I'll explain it to you.

Bruce :cool:

• Re: COUNTA problem

Hi,
Yes, I would like to know how it works; the 9.99999999999999E+307 bit is mad!
regards,
Nioc

• Re: COUNTA problem

That's the largest number that Excel can handle, from what I've read.

Lookup will either find that number or return the last number it finds that is smaller.

So it will always return the last number in the range it is looking at, or the first instance of that very large number, if it's in one of the cells.