Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

Thanks!

Thought:

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

share|improve this question
    
if -> then -> else -> if –  Zac Bruce Jun 30 '11 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 '11 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 '11 at 23:22
add comment

2 Answers 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.

share|improve this answer
    
IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 )) –  Zac Bruce Jun 30 '11 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. –  Tomas Lycken Jul 1 '11 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 '11 at 22:56
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.