progressive IF range

BrutalLogiC

Active Member
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
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%)
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,638
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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
 
Solution

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows

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
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Feb 26, 2006
Messages
260
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 22, 2012
Messages
1,638
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
You're Welcome & Thanks for feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,567
Messages
5,625,550
Members
416,116
Latest member
Joemamasuka

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
Top