First column with non zero values

[Richard Buttrey]

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

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
__________________________

 

[Norman Jones]

Hi Richard,

If you seek the last populated cell in column E , try the VBA expression:

Cells(Rows.Count, "E").End(xlUp)

 

[Tom Ogilvy]

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,A11)

 

[Richard Buttrey]

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

Hi Richard,

If you seek the last populated cell in column E , try the VBA expression:

Cells(Rows.Count, "E").End(xlUp)

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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

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,A11)

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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

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,A11)

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Bob Phillips]

It's the largest number that can be held in a cell.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

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(A11>0,A11))

Entered with Ctrl+Shift+Enter rather than enter since this is an array
formula.

 

[Chris Ferguson]

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]

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(A11>0,A11))

Entered with Ctrl+Shift+Enter rather than enter since this is an array
formula.

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

 

[Richard Buttrey]

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
__________________________