Display Last Figure

[EMoe]

Hello Programmers,

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.

Thanks,
EMo

 

[Bob Phillips]

Try this formula

=INDEX(A1:A12,MAX(IF(A1:A12<>0,ROW(A1:A12))))

which is an array formula, so commit with Ctrl-Shift-Enter

 

[Ron Rosenfeld]

Hello Programmers,

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.

Thanks,
EMoe

Formula Solution:

You could use this **array** formula in B1:

=OFFSET(A1,-1+MAX((A1:A12>0)*ROW(A1:A12)),0)

To enter an array formula, hold down <ctrl><shift> while hitting <enter>.
Excel will place braces {...} around the formula.

----------------------
VBA Solution

Right click on the sheet tab and select View Code.
Paste the code below into the window that opens.

========================
Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim c As Range
Dim Res As Range

Application.EnableEvents = False

Set AOI = [A1:A12]
Set Res = [B1]

For Each c In AOI
If c.Value = 0 Then
Res.Value = c.Offset(-1, 0).Value
GoTo X
End If
Next c

X: Application.EnableEvents = True

End Sub
======================

Neither routine checks for invalid entries but rather assumes that the only
entries will be 0's (to be ignored) or other values to be considered.



--ron

 

[EMoe]

Thanks Guys,

I'll try both solutions.

Regards,
EMoe