Formula Help

[Shmeetbag]

I need help with a formula on the following scenario:

A1 will have a variable number.

I need a formula that will return a different % based on how large the number inputed is.

EX

If the number is between 1000-1999, then return 1%
If the number is between 2000-2999, then return 2%
If the number is between 3000-3999, then return 2.5%
If the number is between 4000-4999, then return 4%

In the same formula, I need 25% of the prior % returned.

So if the number inputed is 2000 it would return 10 (2000x.02x.25)

I have tried the IF function, but can't get past the first part of the calculation.

=IF(F2>=1000>1999,F2*0.01,IF(F2>=2000>2999,F2*0.02,""))

I appreciate any help on this

 

[Brian from Maui]

I need help with a formula on the following scenario:

A1 will have a variable number.

I need a formula that will return a different % based on how large the number inputed is.

EX

If the number is between 1000-1999, then return 1%
If the number is between 2000-2999, then return 2%
If the number is between 3000-3999, then return 2.5%
If the number is between 4000-4999, then return 4%

In the same formula, I need 25% of the prior % returned.

So if the number inputed is 2000 it would return 10 (2000x.02x.25)

I have tried the IF function, but can't get past the first part of the calculation.

=IF(F2>=1000>1999,F2*0.01,IF(F2>=2000>2999,F2*0.02,""))

I appreciate any help on this

Instead of those nested IF's consider a VLOOKUP formula. Can you explain the, "if the number inputed is 2000 it would return 10"?

 

[texasalynn]

Book1
	A	B	C	D	E	F
1	0	0
2	1000	1%			1200	1.0%
3	2000	2%			2500	2.0%
4	3000	2.50%			3750	2.5%
5	4000	4%			4800	4.0%
Sheet1

setup a table like shown here, then use the formula to lookup the percentage
=VLOOKUP(E2,$A$1:$B$5,2)

HTH
texasalynn

 

[Shmeetbag]

Is there a way to make a single formula that would include all of those calculations, so when I type in 2000 it would multiply the number by 2%, which is 40 and then in the same formula multiply it by 25% which is 10.


-Also, I don't know if the table will work if I type in a number like 1245 since that number is not included in the VLOOKUP table.

 

[erik.van.geit]

Hello, Shmeetbag,
would this help you ?

=VLOOKUP(F2,{0,0;1000,1;2000,2;3000,2.5;4000,4},2)*F2*25/10000

kind regards,
Erik

 

[Shmeetbag]

I would need the % of the number entered to be returned. The input number is under Rev Forecast and the Bonus is where the formula would be.

Rev Forecast Bonus
$2,000 10





The formula should return 21.875 if 3500 was entered for Rev Forecast (3500x2.5%)x25%).

 

[texasalynn]

-Also, I don't know if the table will work if I type in a number like 1245 since that number is not included in the VLOOKUP table.

Did you try what I gave you?? If you type in 1245 it will pull in 1%, because the vlookup say to find the number that is between 1000 and not 2000. With vlookup there is a 4th parameter that when left off says to find the closest match. So in your example it would be the 1000. I'm not so fond of Excel's explanation. Because it isn't really the closest match, it is any number between. Your table I have the lowest values listed with the desired percentage.

Hopefully that makes it a little clearer.

 

[erik.van.geit]

The formula should return 21.875 if 3500 was entered for Rev Forecast (3500x2.5%)x25%).

if you play with my formula changing "/10000" to your suits you will get that result
currently it is showing 21.785 for me when entering 3500
I'm quite sure texasalynn's solution works too

 

[Shmeetbag]

I didn't explain myself well the first time. I need the number returned and not the % returned. So if you put in 1245 I need 1% of that number and then 25% of that number. (1245x.01=12.45x.25==3.1125)

Sorry for the poor explaination and thanks for the help.

 

[Brian from Maui]

Along the lines of Lynn and Erik,

Book1
	A	B	C	D
1	1000	1%	1245	3.1125
2	2000	2%
3	3000	3%
4	4000	4%
Sheet1