Simple (for some!) Excel Formula help please!

[Petal]

I am doing up a cute little spreadsheet and want what I thought was a fairly simple formula. It goes like this.

Purchase price $300,000
Sale price $320,000

Profit/Loss $20,000

But what if it went like this:

Purchase price $300,000
Sale price $280,000

Profit/Loss $20,000-

I got this forumla from another spreadsheet and tried to mimic it on the new one but it definitely did not work. Also I have heard you can make a loss be in a different colour (say red) but I would be happy for a loss just to be expressed with a - beside it. Anyway here is the formula (don't laugh!!)

=IF(E14<1," ",IF(E14>E16,E14-E16,IF(E16>E14,E14,E14-E16)))

I can PM the spreadsheet if anyone wants me to

 

[gooram]

Excel can handle negative values. So you should just be able to subtract purchase price from sale price and get a negative number. In your cell formatting you can choose how negative values are presented, i.e. in red text or with a negative sign.

 

[Electro]

why not just do

A1 = 300000
A2 = 280000

A3 = A2-A1

It will show a negative sign.

 

[Propertunity]

Anyway here is the formula (don't laugh!!)

=IF(E14<1," ",IF(E14>E16,E14-E16,IF(E16>E14,E14,E14-E16)))

Who wouldn't want a laugh when you can do what gooram said

 

[Petal]

Hardy har har!! Well I have to admit it IS pretty funny

But maybe I wasn't specific enough. What I mean is sometimes the answer will be negative and sometimes the answer will be positive, so the formula has to handle either situation.

So I know how to do formulas for one or the other but not when it could be either.

Yes I just reread my post and I definitely wasn't specific enough, no wonder you guys laughed

 

[Cherry Pro]

Petal ...have just pm'd you.

 

[wealth101]

Petal Try this

 

[wealth101]

Forgot to say when its +ve its green, -ve its red and when its neutral its yellow

 

[aaarghhh]

Conditional Formating

Yeah
What wealth101 did - Conditional Formating

 

[Petal]

Thanks Wealth101 that worked just fine. So simple really, just swap the lines around and it comes up - for negative and nothing for positive.

I also now know that every forumla does not have to begin with =SUM(

I will have a go now at the conditional formatting and try and make the colours work.

Thanks also to Cherry Pro, I have PM'd you.

 

[Petal]

I worked out the conditional formatting and now the minus is in red text (YAY).

Tried to PM you Cherry Pro with this attachment that I am also trying to work out how to do a formula for but the little attach symbol isn't available

Anyway here it is. I have put the values in the Balance column but would like a forumla to work this out automatically. I think this would be one of those IF forumlas.

Thanks very much!!

 

[Petal]

Update - Worked it out!!

just to let you know I have worked out how to do this formula. It is the same as a Checkbook (yes US spelling!) forumla that I found on the internet so I used that. Very exciting, I love learning new excel formulas!!