Excel 2007 : #VALUE problem

[Rob44]

Can somebody tell me why I'm getting #VALUE returned with the following formula when I15 and J15 are empty. It comes up when I try the I15+J15=10. I tried using it in the OR statement, I tried putting in an AND statement but no matter what I do the error keeps coming up when the two cells are empty.

IF(I15+J15=10,"",IF(OR(I15="",J15=""),"",10-(I15+J15)))

Below was the formula before but I now have to look for the value of 10 where I15+J15=10

IF(OR(I15="",J15=""),"",10-(I15+J15))

Thanks in advance.....

[JBeaucaire]

How about:

=IF(OR(I15="", J15=""), "", IF(I15+J15=10, "", 10-(I15+J15)))

[TMS]

Try:

IF(SUM(I15,J15)=10,"",IF(OR(I15="",J15=""),"",10-(SUM(I15,J15))))

Regards

[Cutter]

Or

=IF(OR(I15="",J15="",I15+J15=10),"",10-(I15+J15))

[Rob44]

Thanks fellas,
I used Jerrys, as it was the first response, and it worked (I'm sure I tried it that way around before, maybe I did something wrong).

Why did this #VALUE come up in the first place and why didn't my formula work....? (it worked but the error code came up)

Cutter...just saw you response.....I had tried that but still got #VALUE

[Cutter]

What were the values in I15 and J15 when you got the error?

I don't get an error using your original formula with 20 & 15 in those cells.

[Rob44]

Hi Cutter,

I15 and J15 were blank......

I was wondering if the formula in I15 was in any way to blame even though it had returned nothing in the cell.

[Rob44]

Formula works fine except where the two cells are blank

[Cutter]

Yes, the "" that your I15 and/or J15 held was the cause.

Using the I15+J15 in your first test would be to blame, you can't add "" + ""

You can SUM("":"") though

[Cutter]

Better go with this one:

=IF(OR(SUM(I15,J15)=10,I15="",J15=""),"",10-(SUM(I15,J15)))

[Rob44]

Cutter,
Thanks for taking the time to explain......I'll try to remember that.

Thanks TMShucks your answer shows what Cutter is saying.....

Ah well back to work now.

[JBeaucaire]

Nah, mine aborts the formula earlier if I15 or J15 is blank (staying with your original approach), the last suggestion one does more needless calcs because OR() evaluates all options before moving on.

[Cutter]

JB
With that point in mind maybe this should be used:

=IF(I15="", "",IF(J15="","",IF(I15+J15=10,"",10-(I15+J15))))

It might save one extra calc.

[Rob44]

Thanks Jerry,
I can see why you say that, I have already copied down your formula with no problems.
Thanks everyone for your input.

[Rob44]

Cutter,
Now why didn't I think of doing that in the first place