Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel worksheet display #### | Excel Discussion (Misc queries) | |||
How do you display Greek and Russian languages in excel? | Excel Discussion (Misc queries) | |||
HOW DO I DISPLAY NUMBERS AS WORDS IN EXCEL 2003? | Excel Discussion (Misc queries) | |||
How can I make Excel display HTML code as text | Excel Discussion (Misc queries) | |||
display last entry in Excel | Charts and Charting in Excel |