0

I know this shouldn't be this difficult, but what I am looking to do is

I want a total in A5, based on a running total in A1 through A4.

For example, if A4 is blank then input A5=A3, if A3 is blank than A5=A2, if A2 is blank then A5=A1. If there was a value in A4 than A5=A4.

Thought: IF(A4>"0",A4,A3),IF(A3>"0",A3,A2),IF(A2>"0",A2,A1),IF(A1>"0","A1",0))

3
  • if -> then -> else -> if
    – Zac Bruce
    Jun 30, 2011 at 21:53
  • From what you're saying, it's less of a total than a selection based on precedence of the source cells, with A4 having precedence over A3, A2 and A1; A3 having precedence over A2 and A1, and so on. At least that's my interpretation. If that's not right, what should happen when there are values in A1, A2, A3 and A4?
    – boot13
    Jun 30, 2011 at 22:09
  • How are you deriving the totals in the preceding cells? Couldn't subtotal(9,a1:a4) provide the same thing? In other words what will give you a zero in A2, A3 or A4? maybe there is another way to look at this
    – datatoo
    Jun 30, 2011 at 23:22

2 Answers 2

2

What you do is this:

If (I want the value from A4) Then A4 Else (I don't want the value from A4, so...)
...If (I want the value from A3) Then A3 etc...

If you write the statement on several rows it will be easier to see what happens.

IF(A4>0, A4,
  IF(A3>0, A3,
    IF(A2>0, A2, A1)
  )
)

Put it all together in one row, and you get

IF(A4>0,A4,IF(A3>0,A3,IF(A2>0,A2,A1)))

which should do what you want it to.

3
  • IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))
    – Zac Bruce
    Jun 30, 2011 at 22:05
  • @Zac, isn't that exactly what I've done? From the last line in the question I inferred that the OP knows the basic syntax of the IF statement. Jul 1, 2011 at 6:43
  • I found your description of "If (I want the value from A4) Then A4 Else (I don't want the value from A4, so...) ...If (I want the value from A3) Then A3 etc..." harder to understand.
    – Zac Bruce
    Jul 1, 2011 at 22:56
0

From what I understand, you're trying to get the value of the last non-blank cell in a range of cells (in your example, range A1:A4). If so, this formula might help you.

=OFFSET($A$1,COUNTA($A$1:$A$4)-1,0)

Enter it into A5 (based on your example, that is). For a larger range of cells, just adjust the references.

The formula will return an #REF! when all cells are blank.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .