Excel: repeat the last entry in a column?

Question

In MS Excel, is there a formula that will repeat the last entry in a column?  For example, here are 5 cells in column A:

A1= 300
A2= 400
A3= (blank)
A4= (blank)
A5 = the last entry above

In A5, I'd like a formula that displays the last cell above it that has an entry.  In the above example, A5 would show "400."  If I enter a new value in A3, then A5 would automatically show the value in A3.

This should be a common function, as A5 would be the balance due on a simple accounts receivable statement.  I tried Excel Help and Search Answers in Yahoo for this and found nothing.  Thank you in advance.

Michael E · Accepted Answer

If the entries are all numeric, try =LOOKUP(.99E+305, A1:A4)
If they are all text =LOOKUP("zzzzzz",A1:A4)
if they are mixed
=INDEX(A:A, MAX(IFERROR(MATCH(9.9E+305,A1:A4),0), IFERROR(MATCH("zzzzz",A1:A4),0)), 1)

Anonymous · Answer

you need to build nested IF statements

IF A4=0 then A5 = a3    something like that.

Excel: repeat the last entry in a column?

2 Answers