|
First column with non zero values
Hi,
Can anyone suggest an Excel formulae for column E which will result in the values shown. i.e. ithe last value in any of the rows in columns A:D A B C D E 1 10 20 20 2 20 70 40 40 3 60 70 70 Usual thanks in advance __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
Hi Richard,
If you seek the last populated cell in column E , try the VBA expression: Cells(Rows.Count, "E").End(xlUp) --- Regards, Norman "Richard Buttrey" wrote in message ... Hi, Can anyone suggest an Excel formulae for column E which will result in the values shown. i.e. ithe last value in any of the rows in columns A:D A B C D E 1 10 20 20 2 20 70 40 40 3 60 70 70 Usual thanks in advance __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
From a post by Aladin Akyurek:
To fetch the last numerical value... =LOOKUP(9.99999999999999E+307,C:C) To determine the position of the last numerical value within column C... =MATCH(9.99999999999999E+307,C:C) To fetch the last text value... =LOOKUP(REPT("z",255),C:C) To determine the position of the last text value within column C... =MATCH(REPT("z",255),C:C) so in E1 =LOOKUP(9.99999999999999E+307,A1:D1) -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... Hi, Can anyone suggest an Excel formulae for column E which will result in the values shown. i.e. ithe last value in any of the rows in columns A:D A B C D E 1 10 20 20 2 20 70 40 40 3 60 70 70 Usual thanks in advance __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
Thanks Norman, that was quick :-)
I think the formatting I posted has not come out correctly. The data was A1 10 B1 20 A2 20 C2 70 D2 40 A3 60 C3 70 Ideally I was looking for an excel formula in E1, E2 and E2 which would result in the values, 20, 40 & 70 respectively, i.e the last values in each of the rows 1, 2 & 3 Regards On Tue, 18 Jan 2005 14:38:33 -0000, "Norman Jones" wrote: Hi Richard, If you seek the last populated cell in column E , try the VBA expression: Cells(Rows.Count, "E").End(xlUp) --- Regards, Norman "Richard Buttrey" wrote in message ... Hi, Can anyone suggest an Excel formulae for column E which will result in the values shown. i.e. ithe last value in any of the rows in columns A:D A B C D E 1 10 20 20 2 20 70 40 40 3 60 70 70 Usual thanks in advance __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
Thanks Tom,
That's an interesting one. Just as a supplementary, what's the significance of the 9.99999999999999E+307 particularly the +307 bit? Regards On Tue, 18 Jan 2005 09:54:07 -0500, "Tom Ogilvy" wrote: From a post by Aladin Akyurek: To fetch the last numerical value... =LOOKUP(9.99999999999999E+307,C:C) To determine the position of the last numerical value within column C... =MATCH(9.99999999999999E+307,C:C) To fetch the last text value... =LOOKUP(REPT("z",255),C:C) To determine the position of the last text value within column C... =MATCH(REPT("z",255),C:C) so in E1 =LOOKUP(9.99999999999999E+307,A1:D1) __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
Thanks Tom,
Just realised I didn't completely specify my data I have a table prepopulated with array formulae, with each column representing a day of the month. These read a database which grows larger throughout the month, but of course any days in the table later than the current database evaluate to zero until that date's data is loaded. Hence the formulae you suggest evaluates to zero. Is there any way of modifying it so that it excludes zero values, and justt pick up the latest number that is not zero? TIA On Tue, 18 Jan 2005 09:54:07 -0500, "Tom Ogilvy" wrote: From a post by Aladin Akyurek: To fetch the last numerical value... =LOOKUP(9.99999999999999E+307,C:C) To determine the position of the last numerical value within column C... =MATCH(9.99999999999999E+307,C:C) To fetch the last text value... =LOOKUP(REPT("z",255),C:C) To determine the position of the last text value within column C... =MATCH(REPT("z",255),C:C) so in E1 =LOOKUP(9.99999999999999E+307,A1:D1) __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
It's the largest number that can be held in a cell.
-- HTH RP (remove nothere from the email address if mailing direct) "Richard Buttrey" wrote in message ... Thanks Tom, That's an interesting one. Just as a supplementary, what's the significance of the 9.99999999999999E+307 particularly the +307 bit? Regards On Tue, 18 Jan 2005 09:54:07 -0500, "Tom Ogilvy" wrote: From a post by Aladin Akyurek: To fetch the last numerical value... =LOOKUP(9.99999999999999E+307,C:C) To determine the position of the last numerical value within column C... =MATCH(9.99999999999999E+307,C:C) To fetch the last text value... =LOOKUP(REPT("z",255),C:C) To determine the position of the last text value within column C... =MATCH(REPT("z",255),C:C) so in E1 =LOOKUP(9.99999999999999E+307,A1:D1) __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
An easy solution would be to change your formula so they don't evaluate to
zero =if(laster than some date,"", current formula) The last number in a row is the last Nmber in a row. So you need a formula that discounts zeros. If you want the last number greater than zero then you might use =LOOKUP(9.99999999999999E+307,IF(A1:D10,A1:D1)) Entered with Ctrl+Shift+Enter rather than enter since this is an array formula. -- Regards, Tom Ogilvy "Richard Buttrey" wrote in message ... Thanks Tom, Just realised I didn't completely specify my data I have a table prepopulated with array formulae, with each column representing a day of the month. These read a database which grows larger throughout the month, but of course any days in the table later than the current database evaluate to zero until that date's data is loaded. Hence the formulae you suggest evaluates to zero. Is there any way of modifying it so that it excludes zero values, and justt pick up the latest number that is not zero? TIA On Tue, 18 Jan 2005 09:54:07 -0500, "Tom Ogilvy" wrote: From a post by Aladin Akyurek: To fetch the last numerical value... =LOOKUP(9.99999999999999E+307,C:C) To determine the position of the last numerical value within column C... =MATCH(9.99999999999999E+307,C:C) To fetch the last text value... =LOOKUP(REPT("z",255),C:C) To determine the position of the last text value within column C... =MATCH(REPT("z",255),C:C) so in E1 =LOOKUP(9.99999999999999E+307,A1:D1) __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
Hi Richard
Does the answer have to be in one column or can it be in a number of columns most of which can be hidden? That way you could have another table alongside the original table with a formula to pick up the corresponding value if that is the last value and then have the last column picking up the max value in the row. So F1 would be =A1-A1*(SUM(B1:$E1)<0) And G1 would be =B1-B1*(SUM(C1:$E1)<0) etc And K1 would be =MAX(F1:J1) You could then hide columns F to I Hope this helps Chris "Richard Buttrey" wrote in message ... Hi, Can anyone suggest an Excel formulae for column E which will result in the values shown. i.e. ithe last value in any of the rows in columns A:D A B C D E 1 10 20 20 2 20 70 40 40 3 60 70 70 Usual thanks in advance __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
On Tue, 18 Jan 2005 11:18:47 -0500, "Tom Ogilvy"
wrote: An easy solution would be to change your formula so they don't evaluate to zero =if(laster than some date,"", current formula) Doh, Cheers Tom: so obvious when it's pointed out. Must be the weather here or something :-) Rgds The last number in a row is the last Nmber in a row. So you need a formula that discounts zeros. If you want the last number greater than zero then you might use =LOOKUP(9.99999999999999E+307,IF(A1:D10,A1:D1) ) Entered with Ctrl+Shift+Enter rather than enter since this is an array formula. __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
First column with non zero values
On Tue, 18 Jan 2005 16:24:27 -0000, "Chris Ferguson"
wrote: Hi Richard Does the answer have to be in one column or can it be in a number of columns most of which can be hidden? That way you could have another table alongside the original table with a formula to pick up the corresponding value if that is the last value and then have the last column picking up the max value in the row. So F1 would be =A1-A1*(SUM(B1:$E1)<0) And G1 would be =B1-B1*(SUM(C1:$E1)<0) etc And K1 would be =MAX(F1:J1) You could then hide columns F to I Hope this helps Chris Chris, Many thanks for this idea. I never cease to be amazed by the speed of response in this ng, and particularly the clever ways people have found of overcoming problems. Even where suggestions are not exactly what's required, there's often a good idea to be tucked away for next time. Kind regards, Richard __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
| All times are GMT +1. The time now is 10:07 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com