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

ghrek

Active Member
Joined
Jul 29, 2005
Messages
315
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
Joined
Dec 30, 2008
Messages
13,790
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Jul 29, 2005
Messages
315
Many thanks, If its zero profit or loss I dont need to do anything.
 

ghrek

Active Member
Joined
Jul 29, 2005
Messages
315
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 29, 2005
Messages
315
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 29, 2005
Messages
315
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,397
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

ghrek

Active Member
Joined
Jul 29, 2005
Messages
315
Had to do the ZERO bit as you stated later but sorted. Many thanks
 

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top