progressive IF range

[BrutalLogiC]

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

 

[maabadi]

Try this:

Mail Generator2.xlsm
	A	B	C	D	E	F
1		Fixed % before 200000			Additive at 0 to 20000000
2	-	2.00%	0		0.00%	0
3	1,000	2.00%	20		0.01%	0.1
4	50,000	2.00%	1000		0.50%	250
5	200,000	2.00%	4000		2.00%	4000
6	1,000,000	2.65%	26500		2.65%	26500
7	10,000,000	8.50%	850000		8.50%	850000
8	20,000,000	15.00%	3000000		15.00%	3000000
9	50,000,000	15.00%	7500000		15.00%	7500000
PL2002

Cell Formulas
Range	Formula
B2:B9	B2	=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:C9	C2	=(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:E9	E2	=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:F9	F2	=(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]

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

 

[BrutalLogiC]

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]

Is this it? :

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

 

[footoo]

Maybe this :

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

 

[BrutalLogiC]

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]

You're Welcome & Thanks for feedback.