Excel 2010 Simple Profit Formula - Please Read

[ladyck3]

This has got to be simple math, I've been retired 2 years now and have lost so much of my know how...can you please help.
I am TRYING to create a formula that shows Profit/Loss on selling some items but if I use the formula that I have, it does not show negative... but oh this is going to be hard to explain.

Here is my formula:
=IF(I19-E19>0,I19-E19,"")

If I use just the simple math, I get the VALUE error and cannot total up the Profit Column
If it don't put the blank value in, ALL items show a negative amount because there are no selling prices in most at this point.
Out of 200+ items, there are 3 sold.... one happens to be a $3 negative so THAT negative I need....

See Examples below:

Paid Price (Col E) Selling Price (Col I) Profit (Col J)
$76.51 (blank not yet sold) $(76.51)
$100.99 $175.00 $74.01 Sold Item
$158.49 $155.00 Blank Sold and should read $(3.49) but due to my formula there is no value

My total profit at this time SHOULD show $70.52 however in totaling that column it shows the profit to be $(5.99) because of the first unsold item without yet a dollar value in Column I.

So can you please help me to remember what in the world to do with this.... ya don't use it, ya lose it and this makes me very angry at myself.

Thank you....

ladyck3
aka: Laurie

 

[SkipVought]

So don't you need a LOSS column, if you have a PROFIT column?

Loss would be when I19-E19<0

 

[WillieMaykit]

Your formula only takes into account 2 of 3 possible outcomes.

=IF(I19-E19>0,I19-E19,"")

1) Profit $74.01
2) if no sell price then insert a text value due to ""

3) Missing outcome is the ($3.49)

As always with excel, there are many ways to 'skin the cat' so to speak.

An option could be:

=IF(ISBLANK(I19),,I19-E19)

This way it takes into account all 3 possible outcomes.

Using speak marks "", forces the cell to be treated as a text cell and not a numerical cell, which can cause other issues.

Hope this helps.

 

[mintjulep]

There is no need for any conditional test.

Sale Price - Purchase Price = Profit.

Always.

 

[kendue]

mintjulep, there IS a need for a conditional test... to test whether or not the item has as yet been sold. An alternate to WillieMaykit's formula (which looks fine) would be =if(I19>0,I19-E19,""). The profit/loss column will show only results for items that are already sold, and can be summed accordingly.

 

[combo]

I would use mintjulep's formula and add another column with information:
=IF(I19=0,"not sold","sold"). This properly returns "not sold" if I19 is blank.

combo

 

[kendue]

combo, while it should be obvious with a blank cell in the Profit/Loss column that the item has not yet been sold, you would be able to show that in the Profit/Loss column without adding another column by amending the formula to =if(I19>0,I19-E19,"not sold"). This would not affect the summation of the Profit/Loss column.

 

[zelgar]

Why don't you use the following formula:

=IF(I19>=E19,I19-E19,)

 

[kendue]

zelgar, your formula does not consider the occasional sale at less than cost (a loss). WillieMaykit's formula, and my suggested formula, both allow for such a possibility. Why is everyone trying to make this so difficult???

I'm curious as to why we haven't heard anything from ladyck3 if any of the solutions offered solve her problem.