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

#### ghrek

##### Active Member
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?

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

#### jasonb75

##### Well-known Member
In I44
Excel Formula:
``=IF(E28>=(O23+T21),E28-(O23+T21),"")``
In E44
Excel Formula:
``=IF(I44="",E28-(O23+T21),"")``

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

• ghrek

#### ghrek

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

#### ghrek

##### Active Member
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

##### Active Member
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

##### Active Member
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:
Excel Formula:
``=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:
Excel Formula:
``=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

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

Replies
12
Views
196
Replies
2
Views
67
Replies
7
Views
37
Replies
3
Views
37
Replies
2
Views
67

### Forum statistics

1,143,677
Messages
5,720,259
Members
422,273
Latest member
linds75 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back