Formula Help

[al97233]

Hello All,

I am trying to get a formula to work for the following equation. or discount on a particular product is 50/10/10 which I know is a .405 multiplier. The problem has been trying to get this to function on a spread sheet. I want to be able to enter the discount in cells as 50 [tab] 10 [tab] 10 [tab and have a cell return the value as .405. this will be done on multiple rows but each row will be a different set of values that will return a different multiplier. i thought this would be easy but i have been unable to figure it out.

thanks in advance for any help.
Al

 

[6StringJazzer]

What does 50/10/10 mean with regard to discounts on a product?

How do you get .405 from 50/10/10?

You are not actually showing us an equation, just some numbers.

 

[Toadstool]

Hi Al97233,

Is this what you wanted?

AL97233.xlsx
	A	B	C	D
1	Discount 1	Discount 2	Dicscount 3	Result
2	50	10	10	40.5
3	60	10	15	30.6
4	10	20	30	50.4
5	30	20	10	50.4
6	10	10	10	72.9
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=(100-A2)*(1-(B2/100))*(1-(C2/100))

 

[al97233]

Hi Al97233,

Is this what you wanted?

AL97233.xlsx
	A	B	C	D
1	Discount 1	Discount 2	Dicscount 3	Result
2	50	10	10	40.5
3	60	10	15	30.6
4	10	20	30	50.4
5	30	20	10	50.4
6	10	10	10	72.9
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=(100-A2)*(1-(B2/100))*(1-(C2/100))

Toadstool,

Thank you for replying.
This is what I am looking for but I need the result to be .405 rather than 40.5. I'm sure it is a simple math issue but i have been working on this so long that my brain is mush.

Thanks,
Al

 

[al97233]

What does 50/10/10 mean with regard to discounts on a product?

How do you get .405 from 50/10/10?

You are not actually showing us an equation, just some numbers.

6StringJazzer,

In my business Manufacturers give us a price list with list prices and based on our volume they assign us a discount and most often it is a combination of discounts similar to the 50/10/10.
What this means is I get 50% of the list price and then an additional 10% and another 10%.
Typically this is figured using a multiplier. To get the multiplier I multiply 1 by the inverse of the discount so in this case it would be 1 x .5 x .9 x .9 to get .405.
I hope this helps.

Thanks
Al

 

[Toadstool]

Toadstool,

Thank you for replying.
This is what I am looking for but I need the result to be .405 rather than 40.5. I'm sure it is a simple math issue but i have been working on this so long that my brain is mush.

Thanks,
Al

Just divide by 100

AL97233.xlsx
	A	B	C	D
1	Discount 1	Discount 2	Dicscount 3	Result
2	50	10	10	0.405
3	60	10	15	0.306
4	10	20	30	0.504
5	30	20	10	0.504
6	10	10	10	0.729
Sheet1 (2)

Cell Formulas
Range	Formula
D2:D6	D2	=((100-A2)*(1-(B2/100))*(1-(C2/100))/100)

 

[Fluff]

How about

=(100-A2)*(100-B2)*(100-C2)/10^6

 

[al97233]

Thank you all for the help. You solved my problem and saved me so much time.

Thanks!
Al

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Liverpool1959]

What does 50/10/10 mean with regard to discounts on a product?

How do you get .405 from 50/10/10?

You are not actually showing us an equation, just some numbers.

if something is $1 then 50% off is .50 Then 10% off .50 is .45 Then 10% off .45 is .405