NEED HELP WITH DIFFICULT FORMULA
Posted by Jennifer Jadin on February 07, 2002 8:56 AM
I hope that someone here can help me. I am some what knowledgable about Excel Functions and Formula but this one is over my head. PLEASE HELP! I am looking for a formula that will do the following...... If Cell F5 is > than or = to $50,000 and < than or = to $74,999 then multiply by .75%, or if cell F5 is > than or = to $75,000 and < then or = to $99,999 then multiply by 1%, or if F5 is > than or = to $100,000 than multiply by 1.5%.
Posted by IML on February 07, 2002 9:13 AM
You could try
=F5*VLOOKUP(F5,{0,0;50000,0.0075;74999.99,0.01;99999.99,0.015},2)
I assumued F5 would only be positive or 0, and amounts under 50K should calc to zero.
If this isn't the case, just say so.
Posted by Jennifer Jadin on February 07, 2002 9:32 AM
What is VLOOKUP ? The function is not working. And yes the numbers will be greater than or equal to 0.
Posted by IML on February 07, 2002 9:55 AM
I full description of vlookup can be found in the help file. In this case, it was used instead of a nested if statement.
What do you mean by not working, are the calcuations wrong, or are you getting an error?
If it's an error, does it go away when changed to
=F5*VLOOKUP(F5+0,{0,0;50000,0.0075;74999.99,0.01;99999.99,0.015},2)
Posted by Jennifer Jadin on February 07, 2002 10:57 AM
YEEEAHHHHHHHHH, It worked! I was having a problem because I was using the wrong cell Name. But I figured the problem. Thanks much for your help. Where should I send you thank you cookie?
;-) Jennifer
