# progressive IF range

#### BrutalLogiC

##### Active Member

A1 is a value
B1 is the formula I am trying to write

If A1 is above zero and up to and including 200,000 then I want the percentage to be 2%
If A1 is 20,000,000 or more then I want the percentage to be 15%
but how to have a 'progressive' result in B1 based on the value in A1?

for example, if A1 was exactly in the middle of 200k and 20m (which would be 10,100,000) then the percentage would be 8.5% (halfway between 2% and 15%)

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

##### Well-known Member
Try this:
Mail Generator2.xlsm
ABCDEF
1Fixed % before 200000Additive at 0 to 20000000
2-2.00%00.00%0
31,0002.00%200.01%0.1
450,0002.00%10000.50%250
5200,0002.00%40002.00%4000
61,000,0002.65%265002.65%26500
710,000,0008.50%8500008.50%850000
820,000,00015.00%300000015.00%3000000
950,000,00015.00%750000015.00%7500000
PL2002
Cell Formulas
RangeFormula
B2:B9B2=IF(AND(A2>=0,A2<=200000),0.02,IF(AND(A2>200000,A2<=20000000),0.02+(15-2)*0.01*A2/20000000,0.15))
C2:C9C2=(IF(AND(A2>=0,A2<=200000),0.02,IF(AND(A2>200000,A2<=20000000),0.02+(15-2)*0.01*A2/20000000,0.15)))*A2
E2:E9E2=IF(AND(A2>=0,A2<=200000),0.02*A2/200000,IF(AND(A2>200000,A2<=20000000),0.02+(15-2)*0.01*A2/20000000,0.15))
F2:F9F2=(IF(AND(A2>=0,A2<=200000),0.02*A2/200000,IF(AND(A2>200000,A2<=20000000),0.02+(15-2)*0.01*A2/20000000,0.15)))*A2

#### footoo

##### Well-known Member
Or :
=IF((1>0)*(A1<=200000),A1*0.02,IF(A1>=20000000,A1*0.15,((A1-200000)/19800000*13+2)/100*A1))

#### BrutalLogiC

##### Active Member
hi i should clarify that I wanted the answer to be 2% regardless of the number in A1...... as long as that number is more than zero and less than or equal to 200,000
same for if it is 20m or more, whatever number it is I want B1 to show 15%

if the number in A1 was 10 then cell B1 would show 2%.... and not 10*2%=0.02
if the number in A1 was 250,000,000 then the cell in B1 would show 15%..... and not 250m*15%=37.5m

#### footoo

##### Well-known Member

Is this it? :

=IF(A1="","",IF((A1>0)*(A1<=200000),2,IF(A1>=20000000,15,ROUND(((A1-200000)/19800000*13+2),2))))

Last edited:

#### footoo

##### Well-known Member
Maybe this :

=IF((A1="")+(A1<=0),"",IF((A1>0)*(A1<=200000),2,IF(A1>=20000000,15,ROUND(((A1-200000)/19800000*13+2),2))))

#### BrutalLogiC

##### Active Member
the one maabadi provided seems to do the trick thanks
=IF(AND(A3>=0,A3<=200000),0.02,IF(AND(A3>200000,A3<=20000000),0.02+(15-2)*0.01*A3/20000000,0.15)

is the number is 342,139 then the answer should be 2.22%

##### Well-known Member
You're Welcome & Thanks for feedback.

Replies
6
Views
114
Replies
0
Views
247
Replies
13
Views
140
Replies
3
Views
92
Replies
3
Views
211

1,126,965
Messages
5,621,871
Members
415,862
Latest member
nascaline

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back