Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
Hi,
I can find the first empty cell in a column, but I'm hoping someone can help me when the cell 'looks empty', but there's a formula in the cell that results in zero. Specifically, I'm trying to put the value in cell E34 in another worksheet called AP-AR. The value should be the last non-zero result from formuals in column H starting from H9 and going down. Cells from H9 down have formulas in them like this =IF(AND(F14=0, G14=0), 0, +H13-F14+G14). They're cumulative totals of credits and debits with the last H column value being the bank balance. Thanks everyone. What a wonderful resource this newsgroup is. I've learned so much and met so many nice people! I do my share of volunteering in other ways and I hope your kind help here comes back to you, too. Michele J. Jones, PMP |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
On Sun, 29 Jul 2007 08:02:28 -0700, mjones
wrote: Hi, I can find the first empty cell in a column, but I'm hoping someone can help me when the cell 'looks empty', but there's a formula in the cell that results in zero. Specifically, I'm trying to put the value in cell E34 in another worksheet called AP-AR. The value should be the last non-zero result from formuals in column H starting from H9 and going down. Cells from H9 down have formulas in them like this =IF(AND(F14=0, G14=0), 0, +H13-F14+G14). They're cumulative totals of credits and debits with the last H column value being the bank balance. Thanks everyone. What a wonderful resource this newsgroup is. I've learned so much and met so many nice people! I do my share of volunteering in other ways and I hope your kind help here comes back to you, too. Michele J. Jones, PMP This array-entered formula will return the last non-zero numeric value in your range: =LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535)) To array-enter a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
On Jul 29, 11:19 am, Ron Rosenfeld wrote:
On Sun, 29 Jul 2007 08:02:28 -0700, mjones wrote: Hi, I can find the first empty cell in a column, but I'm hoping someone can help me when the cell 'looks empty', but there's a formula in the cell that results in zero. Specifically, I'm trying to put the value in cell E34 in another worksheet called AP-AR. The value should be the last non-zero result from formuals in column H starting from H9 and going down. Cells from H9 down have formulas in them like this =IF(AND(F14=0, G14=0), 0, +H13-F14+G14). They're cumulative totals of credits and debits with the last H column value being the bank balance. Thanks everyone. What a wonderful resource this newsgroup is. I've learned so much and met so many nice people! I do my share of volunteering in other ways and I hope your kind help here comes back to you, too. Michele J. Jones, PMP This array-entered formula will return the last non-zero numeric value in your range: =LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535)) To array-enter a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron- Hide quoted text - - Show quoted text - Hi Ron, Thanks for the response. I never would have figured that one out. Can you explain what 9.99E+307 means? I do understand arrays, and have entered the formula just like you said, but I'm getting #VALUE!. Could it be the cell formats? The last H non-zero value comes from H14 and its format is _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_( ""_)_);_(_(@_)_). Your formula is in a cell with this format _(_($* #,##0.00_)_);[Red]_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_). What do you think? Warm regards, Michele |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
did you enter it by doing Ctrl+Shift+Enter rather than just enter.
when you select the cell and look at it in the formula bar does it look like this: {=LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535))} the brackets are displayed by excel to indicate that it is being interpreted as an array formula. Do you have a number not zero past cell H8? It worked for me when used on the same sheet and if I put sheet references in the formula I would expect it to work refering to another sheet. the technique is to lookup a very large number. It then returns the last number it finds. The IF statement section returns the actual value of all the cells except cells which contain a zero - it returns an empty string for this. This result is addressed by the Lookup formula. -- Regards, Tom Ogilvy "mjones" wrote: On Jul 29, 11:19 am, Ron Rosenfeld wrote: On Sun, 29 Jul 2007 08:02:28 -0700, mjones wrote: Hi, I can find the first empty cell in a column, but I'm hoping someone can help me when the cell 'looks empty', but there's a formula in the cell that results in zero. Specifically, I'm trying to put the value in cell E34 in another worksheet called AP-AR. The value should be the last non-zero result from formuals in column H starting from H9 and going down. Cells from H9 down have formulas in them like this =IF(AND(F14=0, G14=0), 0, +H13-F14+G14). They're cumulative totals of credits and debits with the last H column value being the bank balance. Thanks everyone. What a wonderful resource this newsgroup is. I've learned so much and met so many nice people! I do my share of volunteering in other ways and I hope your kind help here comes back to you, too. Michele J. Jones, PMP This array-entered formula will return the last non-zero numeric value in your range: =LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535)) To array-enter a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron- Hide quoted text - - Show quoted text - Hi Ron, Thanks for the response. I never would have figured that one out. Can you explain what 9.99E+307 means? I do understand arrays, and have entered the formula just like you said, but I'm getting #VALUE!. Could it be the cell formats? The last H non-zero value comes from H14 and its format is _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_( ""_)_);_(_(@_)_). Your formula is in a cell with this format _(_($* #,##0.00_)_);[Red]_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_). What do you think? Warm regards, Michele |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
On Jul 29, 4:32 pm, Tom Ogilvy
wrote: did you enter it by doing Ctrl+Shift+Enter rather than just enter. when you select the cell and look at it in the formula bar does it look like this: {=LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535))} the brackets are displayed by excel to indicate that it is being interpreted as an array formula. Do you have a number not zero past cell H8? It worked for me when used on the same sheet and if I put sheet references in the formula I would expect it to work refering to another sheet. the technique is to lookup a very large number. It then returns the last number it finds. The IF statement section returns the actual value of all the cells except cells which contain a zero - it returns an empty string for this. This result is addressed by the Lookup formula. -- Regards, Tom Ogilvy "mjones" wrote: On Jul 29, 11:19 am, Ron Rosenfeld wrote: On Sun, 29 Jul 2007 08:02:28 -0700, mjones wrote: Hi, I can find the first empty cell in a column, but I'm hoping someone can help me when the cell 'looks empty', but there's a formula in the cell that results in zero. Specifically, I'm trying to put the value in cell E34 in another worksheet called AP-AR. The value should be the last non-zero result from formuals in column H starting from H9 and going down. Cells from H9 down have formulas in them like this =IF(AND(F14=0, G14=0), 0, +H13-F14+G14). They're cumulative totals of credits and debits with the last H column value being the bank balance. Thanks everyone. What a wonderful resource this newsgroup is. I've learned so much and met so many nice people! I do my share of volunteering in other ways and I hope your kind help here comes back to you, too. Michele J. Jones, PMP This array-entered formula will return the last non-zero numeric value in your range: =LOOKUP(9.99E+307,IF(H9:H65535=0,"",H9:H65535)) To array-enter a formula, hold down <ctrl<shift while hitting <enter. Excel will place braces {...} around the formula. --ron- Hide quoted text - - Show quoted text - Hi Ron, Thanks for the response. I never would have figured that one out. Can you explain what 9.99E+307 means? I do understand arrays, and have entered the formula just like you said, but I'm getting #VALUE!. Could it be the cell formats? The last H non-zero value comes from H14 and its format is _(_($* #,##0.00_)_);_(_($* (#,##0.00)_);_(_( ""_)_);_(_(@_)_). Your formula is in a cell with this format _(_($* #,##0.00_)_);[Red]_(_($* (#,##0.00)_);_(_($* "-"?? _)_);_(_(@_)_). What do you think? Warm regards, Michele- Hide quoted text - - Show quoted text - Hi Tom and Ron, I'm an idiot. H9 is in another worksheet called Bank so I needed to say: =LOOKUP(9.99E+307,IF(Bank!H9:H65535=0,"",Bank!H9:H 65535)). Now it works. I really appreciate the help and info, Michele |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Get value in last non-zero value cell in column
On Sun, 29 Jul 2007 15:19:22 -0700, mjones
wrote: Hi Tom and Ron, I'm an idiot. H9 is in another worksheet called Bank so I needed to say: =LOOKUP(9.99E+307,IF(Bank!H9:H65535=0,"",Bank!H9: H65535)). Now it works. I really appreciate the help and info, Michele Glad to help and glad you got it working. Thanks for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Maddening Dilemma - Compare each cell within column a to each cell in column b and find unique matches | Excel Worksheet Functions | |||
Sum Cell Values of one column based on Another Cell Value in a different column | Excel Worksheet Functions | |||
compare cells in column to criteria, then average next column cell | Excel Worksheet Functions | |||
Loop through column headers to search from column name and get cell range | Excel Programming | |||
Format cell in column B based on value in the next cell (column c) | Excel Discussion (Misc queries) |