Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display last value in the row
Hello, I asked this questions some time back in a previous post, and received two formulas that doesn't seem to work: =INDEX(A1:A12,MAX(IF(A1:A12<0,ROW(A1:A12)))) =OFFSET(A1,-1+MAX((A1:A120)*ROW(A1:A12)),0) This is what I want to happen: Example: I have a list of figures ranging from A1:A12. These cell contain formulas. If I have currency values in A1:A5, A:6 down to A:12 will have $0.00 because those cells haven't been updated yet. How do I display the last figure in the list in cell B1 (which in thi case would be in A5); Without the $0's showing up as a value? Then a A6 updates, and it's the last value in the list, then that's what shown in B1. I know that both formulas are array formulas (Ctrl,Shift,Enter). Th first formula gives me a #Ref error. The second one just yields a 0 i the cell. I don't know whats wrong. Please help with a formula to do the above. Thanks, EMo -- EMo ----------------------------------------------------------------------- EMoe's Profile: http://www.excelforum.com/member.php...fo&userid=2318 View this thread: http://www.excelforum.com/showthread.php?threadid=47900 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display last value in the row
Try this: Hit F2 to edit the formula, then hit Control+Shift+Enter. These
are array formulas. Also, if you use the first suggested formula, you will run into a problem if you move the befinning of your table to a row other than the first row or insert rows above it. You could modify as follows to prevent this if it is an issue. =INDEX(A1:A12,MAX(IF(A1:A12<0,ROW(INDIRECT("1:"&R OWS(A1:A12)))))) "EMoe" wrote: Hello, I asked this questions some time back in a previous post, and I received two formulas that doesn't seem to work: =INDEX(A1:A12,MAX(IF(A1:A12<0,ROW(A1:A12)))) =OFFSET(A1,-1+MAX((A1:A120)*ROW(A1:A12)),0) This is what I want to happen: Example: I have a list of figures ranging from A1:A12. These cells contain formulas. If I have currency values in A1:A5, A:6 down to A:12 will have $0.00, because those cells haven't been updated yet. How do I display the last figure in the list in cell B1 (which in this case would be in A5); Without the $0's showing up as a value? Then as A6 updates, and it's the last value in the list, then that's whats shown in B1. I know that both formulas are array formulas (Ctrl,Shift,Enter). The first formula gives me a #Ref error. The second one just yields a 0 in the cell. I don't know whats wrong. Please help with a formula to do the above. Thanks, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=479000 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Display last value in the row
Thanks a lot! Maybe that was it. I was trying to use another range of cells besides the first column. Everything works good now. I appreciate that! Regards, EMoe -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=479000 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cells display as R5C5. How do I change it to display E5? | Excel Discussion (Misc queries) | |||
some cells display formulas, some display values | Excel Discussion (Misc queries) | |||
How to display remaining txt file which overflowed MsgBox display? | Excel Discussion (Misc queries) | |||
Display every 3rd category name but still display latest month | Charts and Charting in Excel | |||
Can I display an Excel chart as my screensaver display? | Charts and Charting in Excel |