Display last value in the row

[EMoe]

Hello,
I asked this questions some time back in a previous post, and I received two formulas that doesn't seem to work:

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

This is what I want to happen:
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.

I know that both formulas are array formulas (Ctrl,Shift,Enter). The first formula gives me a #Ref error. The second one just yields a 0 in the cell.

I don't know whats wrong. Please help with a formula to do the above.

Thanks,
EMoe

[JMB]

Try this: Hit F2 to edit the formula, then hit Control+Shift+Enter. These
are array formulas. Also, if you use the first suggested formula, you will
run into a problem if you move the befinning of your table to a row other
than the first row or insert rows above it. You could modify as follows to
prevent this if it is an issue.

=INDEX(A1:A12,MAX(IF(A1:A12<>0,ROW(INDIRECT("1:"&ROWS(A1:A12))))))

"EMoe" wrote:

>
> Hello,
> I asked this questions some time back in a previous post, and I
> received two formulas that doesn't seem to work:
>
> =INDEX(A1:A12,MAX(IF(A1:A12<>0,ROW(A1:A12))))
> =OFFSET(A1,-1+MAX((A1:A12>0)*ROW(A1:A12)),0)
>
> This is what I want to happen:
> 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.
>
> I know that both formulas are array formulas (Ctrl,Shift,Enter). The
> first formula gives me a #Ref error. The second one just yields a 0 in
> the cell.
>
> I don't know whats wrong. Please help with a formula to do the above.
>
> Thanks,
> EMoe
>
>
> --
> EMoe
> ------------------------------------------------------------------------
> EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
> View this thread: http://www.excelforum.com/showthread...hreadid=479000
>
>

[EMoe]

Thanks a lot!

Maybe that was it. I was trying to use another range of cells besides the first column. Everything works good now.

I appreciate that!
Regards,
EMoe