Display Last Entry in Excel
 

Hello,

I tried a suggestion from another board (the Charting board, actually),
but I am not yielding the intended result. I hope someone from this
fine group might be able to assist.

I have a short column list (B3:B6, which is Q1, Q2, Q3, Q4
respectively)in which I want to input quarterly wages. B7 calculates
the running total for another purpose.

In B15, I need to be able to multiply the most current wages(therefore
the LAST cell with a number in the B3:B6 range) by .047 . When I
entered the formula suggested on the other board,
=INDIRECT("B"&COUNTA(B3:B6)) , it keeps giving me the FIRST number in
the list not the last number. so, for example, I have

B3 $5,857.65
B4 $6,617.79
B5 $5,384.00
B6 EMPTY (Q 4 hasn't happened yet)

I need my formula to multiply $5,384.00 by .047, but the formula keeps
giving me $5,857.65 instead. I would like the formula to keep rolling,
so that I don't have to update it each quarter,so that it just takes
the LAST number in this range for the multiplication.

It could be that I just don't understand these functions well enough to
properly adjust them for my specific scenario, because it really
doesn't make any sense to me...but the poster suggested with tremendous
certainty!

Gosh, it seems like Excel should just have a FIRST or LAST function!

Assistance would be appreciated. Thanks,

Bobbie

Display Last Entry in Excel
 

Try...

=LOOKUP(2,1/(B3:B6<""),B3:B6)*0.047

Hope this helps!

In article .com,
"Bobbie" wrote:

Hello,

I tried a suggestion from another board (the Charting board, actually),
but I am not yielding the intended result. I hope someone from this
fine group might be able to assist.

I have a short column list (B3:B6, which is Q1, Q2, Q3, Q4
respectively)in which I want to input quarterly wages. B7 calculates
the running total for another purpose.

In B15, I need to be able to multiply the most current wages(therefore
the LAST cell with a number in the B3:B6 range) by .047 . When I
entered the formula suggested on the other board,
=INDIRECT("B"&COUNTA(B3:B6)) , it keeps giving me the FIRST number in
the list not the last number. so, for example, I have

B3 $5,857.65
B4 $6,617.79
B5 $5,384.00
B6 EMPTY (Q 4 hasn't happened yet)

I need my formula to multiply $5,384.00 by .047, but the formula keeps
giving me $5,857.65 instead. I would like the formula to keep rolling,
so that I don't have to update it each quarter,so that it just takes
the LAST number in this range for the multiplication.

It could be that I just don't understand these functions well enough to
properly adjust them for my specific scenario, because it really
doesn't make any sense to me...but the poster suggested with tremendous
certainty!

Gosh, it seems like Excel should just have a FIRST or LAST function!

Assistance would be appreciated. Thanks,

Bobbie

Display Last Entry in Excel
 

I think if you change the formula to:

=INDIRECT("B"&(COUNTA(B3:B6)+2))

it should work.

The problem is the COUNTA(B3:B6) is yielding 3, so the formula is
looking at cell B3, instead of B6.

HTH, rg

Display Last Entry in Excel
 

=LOOKUP(9.99999999999999E+307,B3:B6)*0.047

Bobbie wrote:
Hello,

I tried a suggestion from another board (the Charting board, actually),
but I am not yielding the intended result. I hope someone from this
fine group might be able to assist.

I have a short column list (B3:B6, which is Q1, Q2, Q3, Q4
respectively)in which I want to input quarterly wages. B7 calculates
the running total for another purpose.

In B15, I need to be able to multiply the most current wages(therefore
the LAST cell with a number in the B3:B6 range) by .047 . When I
entered the formula suggested on the other board,
=INDIRECT("B"&COUNTA(B3:B6)) , it keeps giving me the FIRST number in
the list not the last number. so, for example, I have

B3 $5,857.65
B4 $6,617.79
B5 $5,384.00
B6 EMPTY (Q 4 hasn't happened yet)

I need my formula to multiply $5,384.00 by .047, but the formula keeps
giving me $5,857.65 instead. I would like the formula to keep rolling,
so that I don't have to update it each quarter,so that it just takes
the LAST number in this range for the multiplication.

It could be that I just don't understand these functions well enough to
properly adjust them for my specific scenario, because it really
doesn't make any sense to me...but the poster suggested with tremendous
certainty!

Gosh, it seems like Excel should just have a FIRST or LAST function!

Assistance would be appreciated. Thanks,

Bobbie

Display Last Entry in Excel
 

Ouch! I missed the obvious... :)

In article ,
Aladin Akyurek wrote:

=LOOKUP(9.99999999999999E+307,B3:B6)*0.047

Bobbie wrote:
Hello,

I tried a suggestion from another board (the Charting board, actually),
but I am not yielding the intended result. I hope someone from this
fine group might be able to assist.

I have a short column list (B3:B6, which is Q1, Q2, Q3, Q4
respectively)in which I want to input quarterly wages. B7 calculates
the running total for another purpose.

In B15, I need to be able to multiply the most current wages(therefore
the LAST cell with a number in the B3:B6 range) by .047 . When I
entered the formula suggested on the other board,
=INDIRECT("B"&COUNTA(B3:B6)) , it keeps giving me the FIRST number in
the list not the last number. so, for example, I have

B3 $5,857.65
B4 $6,617.79
B5 $5,384.00
B6 EMPTY (Q 4 hasn't happened yet)

I need my formula to multiply $5,384.00 by .047, but the formula keeps
giving me $5,857.65 instead. I would like the formula to keep rolling,
so that I don't have to update it each quarter,so that it just takes
the LAST number in this range for the multiplication.

It could be that I just don't understand these functions well enough to
properly adjust them for my specific scenario, because it really
doesn't make any sense to me...but the poster suggested with tremendous
certainty!

Gosh, it seems like Excel should just have a FIRST or LAST function!

Assistance would be appreciated. Thanks,

Bobbie

Display Last Entry in Excel
 

Awesome, folks, the last suggestion worked the best. THANK YOU ALL
VERY MUCH!

Aladin or Domenic, if you had an extra moment, it would be great to get
an explanation for the 9.99999999999999E+307 part of your formula (I
understand the rest of it). What is this? What does it do?

If you don't have the time, that is OK, I have just never seen this
before and I would love to understand it further.

Thanks Again, you guys rock!
Bobbie