COUNTA problem

nioc98 · Mar 15th 2016

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?

skywriter · Mar 15th 2016

Re: COUNTA problem

Try this:

Code

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

nioc98 · Mar 15th 2016

Re: COUNTA problem

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

skywriter · Mar 15th 2016

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.

nioc98 · Mar 15th 2016

Re: COUNTA problem

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

skywriter · Mar 15th 2016

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.

nioc98 · Mar 17th 2016

Re: COUNTA problem

As I said; elegant.
Thanks, skywriter!

skywriter · Mar 17th 2016

Re: COUNTA problem

My pleasure, thanks for the feedback. :cool:

COUNTA problem

Participate now!

Share