# progressive IF range

#### BrutalLogiC

##### Active Member
Help please

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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

#### maabadi

##### 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

• BrutalLogiC

#### 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

#### 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

ADVERTISEMENT

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%

#### maabadi

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

Replies
6
Views
115
Replies
0
Views
252
Replies
13
Views
142
Replies
3
Views
98
Replies
3
Views
214

Threads
1,127,205
Messages
5,623,364
Members
415,969
Latest member
Rey99

### Share this page ### 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.
Allow Ads at MrExcel

### Which adblocker are you using?    ### Disable AdBlock

Follow these easy steps to disable AdBlock

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

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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