If Number ><

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need to create a formula to apply discount percentage.

If x < 2000001 then apply 56%
If 1000001 < x >2000000 then apply 49%
If 650001 < x >1000000 then apply 47%

And so on.

Thanks
 
Try something like this:

With
A1: (a amount to calculate the discount on)

This formula calculates the appropriate discount percent for the value in A1
B1: =LOOKUP(A1,{0,650001,1000001,2000001},{0,0.47,0.49,0.56})

OR...if your discount structure is more extensive....
You could put this kind of list elsewhere in the sheet or workbook.
0 0%
650001 47%
1000001 49%
2000001 56%

If that list is in cells E2:F5....then
B1: =VLOOKUP(K1,$E$2:$F$5,2,1)

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP
 
Making some guesses about your misprints, and about what you want to do in
the conditions you haven't defined:
=IF(X1<650001,"undefined",IF(X1<=1000000,47%,IF(X1<=2000000,49%,56%)))
 
Thanks,

Actually the restriction is:
Apply discount if X

0 and 2,000 ->0%
2,001 and 5,000 ->12%
5,001 and 50,000 ->28%
50,001 and 300,000 ->36%
300,001 and 650,000 ->45%
650,001 and 1,000,000 ->47%
1,000,001 and 2,000,000 ->49%
2,000,001 and more ->56%
 
So you want a lookup.
And you still haven't defined what you want for the ranges you haven't
included (unless, of course, you've got data validation to exclude those
values).
 
Put this table on a new sheet, for example occupying cells A1 to B8:

0 0%
2001 12%
5001 28%
50001 36%
300001 45%
650001 47%
1000001 49%
2000001 56%

Then in your other sheet you can use this formula:

=VLOOKUP(X1,Sheet2!A$1:B$8,2)

This will give you the appropriate percentage for the value in X1. The
formula can be copied down for other values in column X.

Hope this helps.

Pete
 
Back
Top