How do I get a formula to show if a surplus or loss.

[ghrek]

Hi

I have a workbook that im trying to get it to work out if a surplus or loss and then put in one specific place.

I have a value in cell E28 and also values in cells O23 and T31

What im trying to do is the following..

If the value of cell E28 is GREATER than the values of cells O23&T31 combined then I need the difference between the two put into cell I44
or
If the value of cell E28 is LESS than the values of cells O23&T31 combined then I need the difference between the two put into cell E44

Any Ideas?

 

[jasonb75]

In I44

=IF(E28>=(O23+T21),E28-(O23+T21),"")

In E44

=IF(I44="",E28-(O23+T21),"")

What do you want in the event of breaking even (zero profit or loss)?

 

[ghrek]

Many thanks, If its zero profit or loss I dont need to do anything.

 

[ghrek]

Just tried the formulas and it works OK with this formula =IF(E28>=(O23+T21),E28-(O23+T21),"")

When I copy the other formula in E44 =IF(I44="",E28-(O23+T21),"") it comes up with VALUE!

 

[Joe4]

When I copy the other formula in E44 =IF(I44="",E28-(O23+T21),"") it comes up with VALUE!

What exactly is in cells E28, O23 and T21?
Are they all numeric entries?
Is I44 showing some sort of error?

 

[ghrek]

Cell E28 is a debit figure

Cells O23 and T21 are credit figures.

They are all numeric entries.

What im trying to is where the figure of O23 and T21 combined is MORE than whats shown in E28 I need the difference shown in E44.

 

[Joe4]

You said:

When I copy the other formula in E44 =IF(I44="",E28-(O23+T21),"") it comes up with VALUE!

That would only happen if I44 was returning an error, or at least one of E28, O23, or T21 did not contain a valid numeric value.

Can you list exactly what is in each of these four cells when you get the #VALUE error?
Also note that a number entered as text is NOT a valid numeric entry. Usually the dead give-away is if you see the entry is left-justified (numeric values are right-justified, by default).

 

[ghrek]

My apologies got all the cells mixed up. Let me start again.

All monetary values...

If value of cells O21+T29 combined are MORE than value in cell E26 then I need difference putting in cell E42

If value of cells O21+T29 combined are LESS than value in cell E26 then I need difference putting in cell I42

 

[Joe4]

If value of cells O21+T29 combined are MORE than value in cell E26 then I need difference putting in cell E42

Formula to put in cell E42:

=IF(O21+T29>E26,E26-O21-T29,"")

If value of cells O21+T29 combined are LESS than value in cell E26 then I need difference putting in cell I42

Formula to put in cell I42:

=IF(O21+T29<E26,E26-O21-T29,"")

Note that then if you are trying to then use cells E42 and I42 in other calculations, they may not like the value of "", so you may need to account for that in other calculations.
Another option is to replace the "" in the formulas above with 0, so it will return 0 instead of blank.
You can then always use Conditional Formatting or a Custom Format to suppress the display of 0 values.

 

[ghrek]

Had to do the ZERO bit as you stated later but sorted. Many thanks