IF function.

[GEM]

How can I use an IF function and tell excel that if a number is between a
range, use a certain percentage and if it's between another range, use
another percentage.

For example.

Between 0.01 - 10.00 use 8%
Between 10.01 - 20.00 use 8.25%
Between 20.01 - infinite use 9%

 

[Bob Umlas]

=IF(A1<=10,8%,if(A1<=20,8.25%,9%))

 

[Jim Thomlinson]

One way... This way is nice becuase it easily expands if you need to add more
intervals or is you want to change the intervals...

=IF(A1<A4, 0, INDEX($C$4:$C$6, MATCH(A1, $A$4:$A$6, 1)))

Where the number to be evaluated is in A1 and the table of values as you
listed it is in A4:C6.

 

[Marcelo]

"GEM" escreveu:

How can I use an IF function and tell excel that if a number is between a
range, use a certain percentage and if it's between another range, use
another percentage.

For example.

Between 0.01 - 10.00 use 8%
Between 10.01 - 20.00 use 8.25%
Between 20.01 - infinite use 9%

IF(A1>20.01,9%*A1,IF(A1>10.01,8.25%*A1,IF(A1>0.01,8%*A1,0)))

hth

 

[Bob Phillips]

=IF(A2>0,8%+(A2>10)*0.25%+(A2>20)*0.75%,"")

 

[Jacob Skaria]

Another way; (easy to edit)

=LOOKUP(A1,{0,0.01,10.01,20.01},{0,0.8,0.825,0.9})

If this post helps click Yes

 

[GEM]

Thank you this works great, now this is what I really need...

$0.01 - $25.00 ---> 8.75% of the closing value

$25.01- $1,000.00 ---> 8.75% of the initial $25.00, plus 3.50% of the
remaining closing value balance

Equal to or Over $1000.01 ----> 8.75% of the initial $25.00, plus 3.50% of
the initial $25.01 - $1,000.00, plus 1.50% of the remaining closing value
balance

 

[Bob Phillips]

=MIN(A2,25)*8.75%+MAX(0,A2-25)*3.5%+MAX(0,A2-1000)*1.5%