Formula Help

Shmeetbag

Board Regular
Joined
Nov 30, 2004
Messages
88
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"?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
Hello, Shmeetbag,
would this help you ?
Code:
=VLOOKUP(F2,{0,0;1000,1;2000,2;3000,2.5;4000,4},2)*F2*25/10000

kind regards,
Erik
 
Upvote 0
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%).
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

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

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
Back
Top