# Formula Help

#### Shmeetbag

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

### Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Shmeetbag said:
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"?

Book1
ABCDEF
100
210001%12001.0%
320002%25002.0%
430002.50%37502.5%
540004%48004.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

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.

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

kind regards,
Erik

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

Shmeetbag said:
-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.

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

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.

Along the lines of Lynn and Erik,
Book1
ABCD
110001%12453.1125
220002%
330003%
440004%
Sheet1

Replies
0
Views
142
Replies
5
Views
192
Replies
3
Views
103
Replies
3
Views
689
Replies
3
Views
393

1,211,435
Messages
6,101,848
Members
447,758
Latest member

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