Need Help on Excel IFs

[sanur10xc]

Hi Guys,

I've been stuck to find an excel formula for these particular case and do really need some help. Any help is greatly appreciated. Thank you so much
Basically I'm trying to make a discount plan and the case is like this.

Say Cell A1 will contain the price

<15000 = 0 discount (or A1)
15000<A1<30000 = Ceiling(A1*0.908,50)
30000<A1<33334 = 30000 (I'm trying to limit all the numbers between this to not go lower than 30,000, say for example 32,000 wont to to 29,000 due to the ceiling discount)
Rest = Ceiling(A1*0.908,50)

So basically if the A1 is lower than 15k, then A1 will be the final price, if the value will be between 15000-30000, since it won't reach the 30k anyway, I'll give the 10% discount (using the ceiling), and if it's higher than 30k, I need some formula that will keep the lowest amount for the price after discount is 30k (for example so that 32000 wont go to 29k ish). I'm having an event that is related to 30,000 as a number. I'm sorry for all the bad explanation (english is not my first language) and confusion but hopefully someone can help. Also I'm using the ceiling so that the number is neat (nothing like 29610 or something).

I tried with this but It returns 28,600 although it should be 30,000
A1=31,500
=IF(A1<=15000,A1, IF(15000<A1<30000, CEILING(A1*0.908,50), IF(30000<A1<33334, 30000, CEILING(A1*0.907,50))))


Thank you so much guys

 

[Fluff]

How about

=IF(A1<=15000,A1, IF(A1<30000, CEILING(A1*0.908,50), IF(A1<33334, 30000, CEILING(A1*0.907,50))))

 

[kweaver]

Maybe this using the strict ranges? Fluff's is more compact.

Book5
	A	B
1	price
2	10000	0
3	18000	16345
4	35000	31780
5	31500	30000
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2<15000,0,IF(AND(A2>=15000,A2<30000),CEILING(A2*0.908,5),IF(AND(A2>=30000,A2<33334),30000,CEILING(A2*0.908,5))))

 

[sanur10xc]

Maybe this using the strict ranges? Fluff's is more compact.

Book5
	A	B
1	price
2	10000	0
3	18000	16345
4	35000	31780
5	31500	30000
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2<15000,0,IF(AND(A2>=15000,A2<30000),CEILING(A2*0.908,5),IF(AND(A2>=30000,A2<33334),30000,CEILING(A2*0.908,5))))

 

[sanur10xc]

How about

=IF(A1<=15000,A1, IF(A1<30000, CEILING(A1*0.908,50), IF(A1<33334, 30000, CEILING(A1*0.907,50))))

Thank you so much for the help really appreciate it

 

[sanur10xc]

Maybe this using the strict ranges? Fluff's is more compact.

Book5
	A	B
1	price
2	10000	0
3	18000	16345
4	35000	31780
5	31500	30000
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(A2<15000,0,IF(AND(A2>=15000,A2<30000),CEILING(A2*0.908,5),IF(AND(A2>=30000,A2<33334),30000,CEILING(A2*0.908,5))))

Thanks a lot, finally got it solved. You're a hero, thank you

 

[Fluff]

Glad we could help & thanks for the feedback.