|
HOW? Find and display the last cell in a column with a value 0
I want to create a formula in a cell that will make that cell display the
value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
Conker10382:
I'm not clear about your doubt. Do you mean that the 500 value is the maximum in the range c1:c4? Or, maybe the 500 value is on the first row of the range? Please, could you please be more explicit? -- Gaspar PM --Adopting methodology-- "Conker10382" wrote: I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
Hi!
Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value
Sorry, I know my example leaves a bit to be desired, LOL. It's sort of hard
to explain, maybe this will help. I am trying to create a spreadsheet that will keep a running total in a colum. Each time the total is added to or subtracted from, the new total is placed below the old total. I want the topmost cell in the column to display the grand total (the last cell in the column greater than zero. See if this example helps a bit mo A B C 1 add/Sub Total 2 50 <--- 3 100 4 -50 50 <--- 5 6 If the total is changed, it then becomes: A B C 1 add/Sub Total 2 40 <---- 3 100 4 -50 50 5 -10 40 <---- 6 Is that easier to understand? Each time the total is added to or subtracted from, the new grand total should show at the topmost cell. I'm pretty sure there is a formula that will search the entire colum for the last cell with a value greater than zero and display that value, but I'm just not sure what it is or how to use it. "PMPLookingForToBe" wrote: Conker10382: I'm not clear about your doubt. Do you mean that the 500 value is the maximum in the range c1:c4? Or, maybe the 500 value is on the first row of the range? Please, could you please be more explicit? -- Gaspar PM --Adopting methodology-- "Conker10382" wrote: I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
You're confusing me Biff.<g
Why the array suggestion? First one works fine for text *AND/OR* numbers, though it *also* returns nulls, which of course can be very confusing if displaying an 'empty' appearing cell. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
biff's last formula entered as an array shift cntrl enter works, I was going to suggest something similar =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) it assumes your data is in columnc the range being c2:c25 you may need to extend the range for your purposes Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563825 |
HOW? Find and display the last cell in a column with a value 0
Why the array suggestion?
It eliminates text including formula blanks. The first formula won't. The way things go for me is, I'd post only the first formula then the OP would follow-up with " it doesn't work, I get a blank cell ???" So, I just tried to cover all the bases without getting into overkill! Biff "Ragdyer" wrote in message ... You're confusing me Biff.<g Why the array suggestion? First one works fine for text *AND/OR* numbers, though it *also* returns nulls, which of course can be very confusing if displaying an 'empty' appearing cell. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
biff's last formula entered as an array shift cntrl enter works
Well, of course it works! <vbg They always work based on the questions and information provided! But of course sometimes the poster forgets to mention some other details that casue problems but we can deal with that in a follow-up! Biff "Dav" wrote in message ... biff's last formula entered as an array shift cntrl enter works, I was going to suggest something similar =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) it assumes your data is in columnc the range being c2:c25 you may need to extend the range for your purposes Regards Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=563825 |
HOW? Find and display the last cell in a column with a value
Well, Biff I'm glad you posed both of them.. You were absolutly right. The
first one kept coming up with a blank cell, and the Index worked great. Thanks! "Biff" wrote: Why the array suggestion? It eliminates text including formula blanks. The first formula won't. The way things go for me is, I'd post only the first formula then the OP would follow-up with " it doesn't work, I get a blank cell ???" So, I just tried to cover all the bases without getting into overkill! Biff "Ragdyer" wrote in message ... You're confusing me Biff.<g Why the array suggestion? First one works fine for text *AND/OR* numbers, though it *also* returns nulls, which of course can be very confusing if displaying an 'empty' appearing cell. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value 0
Well, we could just add another argument to by-pass the nulls, thus
*eliminating* any return of a *blank* cell: =LOOKUP(2,1/((A1:A100<"")*(A1:A1000)),A1:A100) This still returns text, which I think is OK, since in this scenario there should be none, and in others, it could come in handy for an "all-around" general last cell finder (with the 0, and <"" qualifications). Plus of course, it's non-array, but let's not get into that discussion.<bg -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Why the array suggestion? It eliminates text including formula blanks. The first formula won't. The way things go for me is, I'd post only the first formula then the OP would follow-up with " it doesn't work, I get a blank cell ???" So, I just tried to cover all the bases without getting into overkill! Biff "Ragdyer" wrote in message ... You're confusing me Biff.<g Why the array suggestion? First one works fine for text *AND/OR* numbers, though it *also* returns nulls, which of course can be very confusing if displaying an 'empty' appearing cell. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
HOW? Find and display the last cell in a column with a value
You're welcome. Thanks for the feedback!
Biff "Conker10382" wrote in message ... Well, Biff I'm glad you posed both of them.. You were absolutly right. The first one kept coming up with a blank cell, and the Index worked great. Thanks! "Biff" wrote: Why the array suggestion? It eliminates text including formula blanks. The first formula won't. The way things go for me is, I'd post only the first formula then the OP would follow-up with " it doesn't work, I get a blank cell ???" So, I just tried to cover all the bases without getting into overkill! Biff "Ragdyer" wrote in message ... You're confusing me Biff.<g Why the array suggestion? First one works fine for text *AND/OR* numbers, though it *also* returns nulls, which of course can be very confusing if displaying an 'empty' appearing cell. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Try one of these: If the range will ONLY CONTAIN NUMBERS (empty cells are ok) and no TEXT including formula blanks: =LOOKUP(2,1/(C2:C250),C2:C25) Or, if there might be TEXT entries, entered as an array using the key combination of CTRL,SHIFT,ENTER: =INDEX(C2:C25,MAX(IF((ISNUMBER(C2:C25))*(C2:C250) ,ROW(C2:C25)-ROW(C2)+1))) Biff "Conker10382" wrote in message ... I want to create a formula in a cell that will make that cell display the value of the last cell in a column with a value greater than 0. i.e.: A B C D E 1 500 <---- 2 3 17 4 75 5 500 <---- |
| All times are GMT +1. The time now is 06:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com