up vote 1 down vote favorite
share [g+] share [fb]

I want to subtract two cells in excel but only if both cells have a value. More specifiaclly in my case the formula in cell E2 is currently =D2-C2 but I only want this to be calculated when D2 has a value. (D2=winnings, C2=stake and E2 = profit/loss. I don't want the profit loss calculated until the winnings cell is completed.)

Can anyone help please?

Steve

link|improve this question
feedback

3 Answers

up vote 1 down vote accepted

Place the following formula in cell E2 to achieve what you want:

=IF(ISBLANK(D2),"",D2-C2)

If you want both cells to have a value:

=IF(OR(ISBLANK(D2),ISBLANK(C2)),"",D2-C2)
link|improve this answer
If both C2 and D2 are required to have a value, shouldn't you use =IF(AND(ISBLANK(D2),ISBLANK(C2)),"",D2-C2) instead? – Gareth Apr 17 '11 at 3:29
@Gareth: No. If you use AND here, it means: If both D2 and C2 is blank, return an empty cell, otherwise (both OR one of them has a value) return D2-C2. We have to use OR so we mean: If any member of the set (D2 or C2) is blank, return an empty cell. Otherwise (both having a value), return D2-C2. – Mehper C. Palavuzlar Apr 17 '11 at 10:00
Thanks for your help, but these don't seem to work. It comes back FALSE. Can you provide a solution please? – Rack Apr 18 '11 at 20:58
Forgive me all terribly. Mehper's solutions worked a treat. I don't know what happened earlier. I think I need to get to bed. I'm ever so grateful you're a star!! – Rack Apr 18 '11 at 21:54
1  
@Mepher Thanks for your help. I've accepted your answer! – Rack Apr 26 '11 at 18:36
show 1 more comment
feedback
up vote 0 down vote

Try this in cell E2:

=IF(AND(D2<>"",C2<>""),D2-C2,"")
link|improve this answer
I'm afraid this doesn't help either.. thanks for your help. Any ideas? – Rack Apr 18 '11 at 20:59
@Gareth Can you help again too? – Rack Apr 18 '11 at 21:32
Ah actually this also worked. Thanks for your time and effort. i don't know why I didn't think it worked earlier. – Rack Apr 18 '11 at 21:55
@Rack, if it was the answer can you please mark it as answer. Regards – Arjang Apr 19 '11 at 2:15
@Rack just to point out I only edited the answer, the answer actually came from @chris neilsen :) – Gareth Apr 19 '11 at 2:20
show 2 more comments
feedback
up vote 0 down vote

I can think of two possible reasons why both solutions didn't work at first: 1) Calculation was set to Manual 2) A cell in question was not really blank (maybe had a space or tab leftover from import)

1 is not too likely. You can cover 2 by using ISNUMBER instead of ISBLANK, and AND instead of OR (say that ten times fast...), as follows:

=IF(AND(ISNUMBER(D2),ISNUMBER(C2)),D2-C2,"")
link|improve this answer
Thx I've accepted Mepher's answer now. – Rack Apr 26 '11 at 18:40
Thx for your help though – Rack Apr 26 '11 at 18:40
feedback

Your Answer

 
or
required, but never shown
discard

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