Subtracting cells only if they have a value in Excel

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.)

Steve

-

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)
``````
-
If both C2 and D2 are required to have a value, shouldn't you use `=IF(AND(ISBLANK(D2),ISBLANK(C2)),"",D2-C2)` instead? – 8088 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

Try this in cell E2:

``````=IF(AND(D2<>"",C2<>""),D2-C2,"")
``````
-
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 :) – 8088 Apr 19 '11 at 2:20

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,"")
``````
-
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