Calculating sales margin with a negative number

[snezana]

Hi,

I've got this huge file where I calculate the sales margin for certain products through (price sold - costs) / price sold.
so e.g. if a product cost 1000 to make, and was sold for 3000 you would get (3000-1000)/3000 = 0,6667 sales margin.

However, sometimes the product is given to a customer free of charge as a sample. This is registered through a negative sales price (since technically you lose out on money you could have had). I know it's not the most logical thing to do, but this is how it's done in the data set. for example one of the entries in the data set says:
Price sold = -594 and cost 382 to make. So the formula would be (-1000 - 500) / -1000. Here i get a sales margin of 1,64, which makes it seem like a positive sales marging, which it isn't. (it would need to be what, appr. -2,5?).

Can't seem to make this work once the price sold is a negative number. Anyone know how to do this?

Thanks!!

 

[Eric W]

You could do something very basic like:

=(A5-B5)/A5*SIGN(A5)

where A5 is the selling price and B5 is the cost to make.