If Number <
 

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

If Number <
 

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


"juanpablo" wrote:

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

If Number <
 

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%)))
--
David Biddulph

"juanpablo" wrote in message
...
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

If Number <
 

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%

"David Biddulph" wrote:

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%)))
--
David Biddulph

"juanpablo" wrote in message
...
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

If Number <
 

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).
--
David Biddulph

"juanpablo" wrote in message
...
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%

"David Biddulph" wrote:

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%)))

"juanpablo" wrote in message
...
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

If Number <
 

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

On Apr 16, 10:46 pm, juanpablo
wrote:
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%



"David Biddulph" wrote:
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%)))
--
David Biddulph

"juanpablo" wrote in message
...
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- Hide quoted text -

- Show quoted text -