Find last value within a range of formulas...via a formula...

  • Thread starter Thread starter Walshy
  • Start date Start date
W

Walshy

Hi wonder if you could please help me...

I am trying to find a formula which finds me the last
value in a range i.e

1%
2%
3%
#n/a
#n/a
#n/a
formula = ????? (The 1 2 3% & #n/a's are all formulas
also...)

the last value would be 3%... I would much appreciate it
if you could help me... Thanks
 
Hi,

Assuming the data is in Column A, try,

=LOOKUP(9.99999999E+307,A1:A100)

Hope this helps!
 
Try:

=INDEX(A1:A100,MAX(IF(ISNUMBER(A1:A100),ROW(A1:A100))))

After inserting the formula, press ctrl/shift/enter.

HTH
Jason
Atlanta, GA
 
This adaptation will allow it to be entered normally, don't know how much
speed is
lost by adding one more function though?


=INDEX(A1:A100,SUMPRODUCT(MAX((A1:A100<>"")*ROW(1:100))))

or

=INDEX(A1:A100,SUMPRODUCT(MAX((A1:A100<>"")*ROW(INDIRECT("1:100")))))

will work for all values

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
I've a strange problem whit this formulas.

I tested all the 3 ones (Jason and Peo) and works fine,
but if I change the range to A5:A100 ... doesn't work. Why?


I entered the formula
=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*ROW(INDIRECT
("5:100"))))) with ctrl+shift+enter

Situation:
1 - value 5 in cell A11
2 - value 999 in cell A100
Results:
1 - The result is 0
2 - The result is #REF!

If the range in formula is A1:A100 is goes ok in the both
situations.

Can you help me?
Bruno COsta
 
Why would you want to change to A5? Aren't you looking for the last value?
If you somehow would like to exclude the 5 first rows
in case there is no last value you can use

=INDEX(A1:A100,SUMPRODUCT(MAX((A5:A100<>"")*ROW(5:100))))

the index will need to start from the first row regardless because the row
function will count from the first row
also note that the whole point of me adding sumproduct was to avoid of
having to enter the formula
with ctrl + shift & enter, if you are going to do that you might as well use

=INDEX(A1:A100,MAX((A5:A100<>"")*ROW(5:100)))

array entered

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)
 
Bmcosta said:
I've a strange problem whit this formulas.

I tested all the 3 ones (Jason and Peo) and works fine,
but if I change the range to A5:A100 ... doesn't work. Why?


I entered the formula
=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*ROW(INDIRECT
("5:100"))))) with ctrl+shift+enter

Situation:
1 - value 5 in cell A11
2 - value 999 in cell A100
Results:
1 - The result is 0
2 - The result is #REF!

If the range in formula is A1:A100 is goes ok in the both
situations.

Can you help me?
Bruno COsta
Click to expand...
Bruno

It's because ROW(INDIRECT("1:100")) acts as a counter.
No matter where your range starts, the expression must count
1,2,3,4,.. etc. to get to the first, second, third, etc cell
of the range (here A5:A100), So

=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*ROW(INDIRECT
("1:100")))))

It may be easier to spot, if you use the formula:

=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*
(ROW(A5:A100)-Row(A5)+1))))
 
Ok,

Thanks for your explanation (why to use sumproduct). I
will try this one.

Bruno Costa
 
Thanks Leo,

Bruno Costa
-----Original Message-----
"Bmcosta" <[email protected]> skrev i en meddelelse


Bruno

It's because ROW(INDIRECT("1:100")) acts as a counter.
No matter where your range starts, the expression must count
1,2,3,4,.. etc. to get to the first, second, third, etc cell
of the range (here A5:A100), So

=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*ROW(INDIRECT
("1:100")))))

It may be easier to spot, if you use the formula:

=INDEX(A5:A100,SUMPRODUCT(MAX((A5:A100<>"")*
(ROW(A5:A100)-Row(A5)+1))))


--
Best Regards
Leo Heuser

Followup to newsgroup only please.




.
Click to expand...
 
Back
Top