Math formula

[Kshari]

I'm trying to accomplish this in a formula:

These cells contain these prices:

A1 $36.12 (per bottle price), B1 $400.41 (12 bottle case), C1 $364.45 (12 bottle cases, multi case order)

I would like cell D1 and E1 to show the discount per bottle of B1 and C1 (respectively) compared to the price of an individual bottle in A1. I'm not a math person and am having trouble getting my head around it. I can make a dummy sheet if it makes this easier to understand, I just figured someone with a math background would think this is simple.

[TMS]

Divide the 12 bottle price by 12 to get the discounted unit price. Take that away from the normal/full price to give you the discount. Then divide the discount by the full price and format it as a percentage.

Regards, TMS

[FDibbins]

Translated into excel...
D1=$A2-(B2/12)
E1=$A2-(C2/12)

For the %...
=($A2-(B2/12))/(B2/12)
=($A2-(C2/12))/(C2/12)

[Kshari]

Yes, thank you both, TMS for making me understand it, heartfelt thanks to Ford for putting the translation and making it quicker to implement!

[TMS]

You're welcome. Thanks for the rep.

Better to understand it than just use it.

Regards, TMS

[Kshari]

Can I have the actual price savings displayed instead of a percentage? like: $2.15 (savings per bottle on case discount)

[FDibbins]

use the 1st 2 formulas that I gavce in post # 3

[Kshari]

I'm just getting the total of the cheaper bottle price with the 1st 2 formulas, can I calculate the actual $ savings between the 2 prices not a percentage of the savings?

[FDibbins]

If you want the unit price difference...

	A	B	C	D	E
2	36.12	400.41	364.45	$ 2.75	$ 5.75

D2=$A2-(B2/12)
E2=$A2-(C2/12)

If you want the case difference, use this instead...
=$A2*12-B2
=$A2*12-C2

[Kshari]

This is such a bonus, thank you!

[Kshari]

Ford, I'm impressed at the speed of your follow up answers, I forgot to say it in the reputation/post thingy. This was so helpful, some posts I make never get answers after I mark them as solved and then post a follow up question, you're great.

[FDibbins]

Thanks for the kind words Sometimes, when the forum gets real busy, posts get pushed down to page 2 or 3 (or lower) and they then sometimes get lost

[Kshari]

Some of my cells in column B and C are empty (not available in cases yet, but might be in the future) and therefore D and E display the original price from A, I want it to display a zero value, should I change the formula or add an IF? The math is making my head spin and I'm afraid to change it and get the wrong return without realizing it.

[TMS]

Well, if you get it wrong, just Undo whatever you did, or close the workbook without saving changes ... or re-input the working formula.

[Kshari]

I'm trying to fix up an 10yr old, massive order/price sheet and I'm spending too much time trying to bone-up on excel functions, can someone please tell me how to rewrite this formula so that when B2 and C2 are empty there is either no calculation or it returns a zero value (I still want the calculation to exist in case the info gets put in later)? I need to finish before the end of this month, help!

(I'm using all three in different circumstances)

D2=$A2-(B2/12)
E2=$A2-(C2/12)

D2=$A2*12-B2
E2=$A2*12-C2

D2=($A2-(B2/12))/(B2/12)
E2=($A2-(C2/12))/(C2/12)

[FDibbins]

You can use this approach for all of those...

D2=if(B2="","",$A2-(B2/12))
E2=if(C2="",""$A2-(C2/12))

You could replace the 2nd "" with 0 if you want 0 shown

[TMS]

@Ford: second one's "broken". Needs to be:

Please Login or Register  to view this content.

Regards, TMS

[FDibbins]

oops thanks for the assist Trevor, left out the comma

[Kshari]

Thank you so much, my life seems simpler now...