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?

 

[Eric W]

Try:

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

 

[EXCEL MAX]

=IF(VALUE(E30&E31)>VALUE(O23&Q45);VALUE(E30&E31)-VALUE(O23&Q45);"")

 

[ghrek]

Thanks for that

Ive tried copying the formula below in next set of cells below and get #VALUE! Any ideas?

=IF(E32+E33>S23+Q46,E32+E33-S23-Q46,"")

 

[Joe4]

Ive tried copying the formula below in next set of cells below and get #VALUE! Any ideas?

You may get that if you have some nun-numeric values in the cells.
Are there only numbers (no "spaces")?
Are all the numbers entered as numbers, and not as spaces?

 

[ghrek]

The cells that I am getting the data from have a link pasted to it from a different worksheet.

Would that be it?

 

[Joe4]

The cells that I am getting the data from have a link pasted to it from a different worksheet.

It depends. Links can pull back both text and valid numbers.
One easy way to tell is to look at the cell. Are the numbers left-justified or right-justified in the cell?
Text is usually left-justified, and numbers are usually right-justified.

Is there are chance you can post images of the cells that the formula is referencing so we can see what they look like?

 

[ghrek]

This is the actual workbook im using and the first one works fine but then onwards is when im having issues.

Book1.xlsm

1drv.ms

 

[Joe4]

In cell E33, you have an IF function that returns "" if not true.
Don't do that. If you want to use these cells in mathematical computations, return only numbers, not text or "". Addition won't work with those.

I would recommend having that formula return 0 instead of "".
If you do not want to show the zero in that cell, you can use Conditional Formatting to change the font of the cell to be white if it is zero so that it matches the background, and does not show.
And then your other computations that depend on that cell will work like they are supposed to.

 

[ghrek]

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