EXCEL (2003) - Formulas - Check value in one cell and set another 2

[AndieB]

Hi all!


My problem is:

I have a cell, which should display a percentage number, between 2 and 8 percent. The thing is, the percentage value is depended on a value. This is the background.

2% if a value is between 0 and 14999
3% if a value is between 15000 and 29999
4% if a value is between 30000 and 44999
5% if a value is between 45000 and 59999
6% if a value is between 60000 and 74999
7% if a value is between 75000 and 89999
8% if a value is between 90000 and above

Lets say I have cell A1 with the formula that detects
what value is in cell B1 and depending on what the value
is in cell B1, the right percentage should be displayed
in cell A1.

Now, my question is, HOW do it make this formula in
cell A1??

Would be really gratefull for any kind of help!
I HAVE studied the HELP but my knowledged and understanding
appears to be limited!

Best regards,

Andreas B

________________________________________
"Everything begins with a THOUGHT..."

 

[jdhilljr]

=IF(B1<15000,0.02,IF(B1<30000,0.03,IF(B1<45000,0.04,IF(B1<60000,0.05,IF(B1<75000,0.06,IF(B1<90000,0.07,0.08))))))

Then format the cell for percentage with no decimal.

Jim

 

[SkipVought]

Hi,

It is simply a methematical formula
[tt]
=(INT(B1/15000)+2)/100
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[jdhilljr]

Skip

Good one. I was out late last night. Only excuse I got.
The only problem he might have with that formula is it doesn't stay at 8% if he goes above 105000
=IF(B1>90000,0.08,(INT(B1/15000)+2)/100)
Jim

 

[SkipVought]

Then you just stick it in an IF
[tt]
=if(b1<90000,(INT(B1/15000)+2)/100,.08)
[/tt]


Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244

 

[AndieB]

Thank you guys!!

You are awesome mathematical guys!!

With the last solution from SkipVought it almost
worked! It always said Error in Formula once I had
it inserted. But I'm not a real dumb guy, I saw what was wrong.

Here is the WORKING solution:

=IF(B1<90000;(INT(B1/15000)+2)/100;0,08)

The thing that was wrong was that you guys were using "," instead of ";".

Have a wonderful day!


________________________________________
"Everything begins with a THOUGHT..."

 

[AndieB]

Hi again Guys!!

Hmm, too bad, when changeing the value in the B1 cell to 15000 or anything else, even above 90000 there is no change at all with the percentage, it still says 2%.

What can be wrong??


Hope you are up to this challange again!!



________________________________________
"Everything begins with a THOUGHT..."

 

[jdhilljr]

I used each of the formulas and they both work.
Check to make sure your calculations are on automatic.
Go to 'Tools' 'Options' 'Calculations' tab.

Jim

 

[SkipVought]

Are you using commas for decimal points?

It must be your regional settings. If I set mine to French, then then

SEMI COLON is the argument separator and

COMMA is the decimal point.

Difference between USA and Europe, I am guessing.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244