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?
COUNTA problem
-
-
-
-
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.
-
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.
About halfway down the page.
Excel specifications and limits. -
Re: COUNTA problem
As I said; elegant.
Thanks, skywriter! -
Re: COUNTA problem
My pleasure, thanks for the feedback. :cool:
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!