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

share|improve this question

3 Answers 3

up vote 4 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)
share|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? –  8088 Apr 17 '11 at 3:29
1  
@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

Try this in cell E2:

=IF(AND(D2<>"",C2<>""),D2-C2,"")
share|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 :) –  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,"")
share|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

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.