Help needed with a formula.

[ghrek]

Hi

Im trying to find a formula that calculates as follows.

if the value of data in cells E30&E31 combined are MORE than the value of O23&Q45 combined then I need the difference shown as a positive figure in cell I45.

Any ideas?

 

[Joe4]

I understand that but I have exactly the same function in E31 and works fine. I need the formula to calculate so is there anyway to get over that

I don't think you do.

The issue is that the result of the formula in cell E31 is returning a valid number, so it can be used in addition/subtraction.
The result of the the formula in cell E33 is "", which is NOT a valid number, so it CANNOT be used in addition/subtraction.

I told you how to get around that. Is there an issue with doing it that way?

 

[ghrek]

I don't think you do.

The issue is that the result of the formula in cell E31 is returning a valid number, so it can be used in addition/subtraction.
The result of the the formula in cell E33 is "", which is NOT a valid number, so it CANNOT be used in addition/subtraction.

I told you how to get around that. Is there an issue with doing it that way?

That’s fine understand now thanks

 

[Eric W]

You could try:

=IF(N(E30)+N(E31)>N(O23)+N(Q45),N(E30)+N(E31)-N(O23)-N(Q45),"")

But I really agree with Joe. It makes more sense to return a 0 than a space if you're going to do math on it. This really complicates a simple formula. There are several ways to show an empty cell besides the way Joe suggested. See the link below.

Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most...

www.mrexcel.com