Microsoft Office excel 2003

Hi, everybody! Extremely limited Excel 2003 usage. I need a formula that will calculate maximum compensation for a requested loan amount, based on "greater than/less than" dollar amount ranges. What my employer wants is to input the requested amount on one cell and have the calculated results on the adjacent cell.

I can figure it out on a calculator, but have no idea how to write the formula he wants. Any help would be appreciated, as it would help me stay employed during these troubling times. :)

Loan Amount (A1) Maximum Net Compensation (B1)

<= $285,714.29 3.50%

$285,714.30 to $500,000 $10,000

$500,000.01 <= $750,000 $12,000

<$750,000.01 $15,000(PS - I thought I posted this already, but couldn't find the previous message...I apologize for the duplicate posting!)

There's a problem with your ranges. <= $750,000 includes all of the other values, as does <$750,000.01.

I assume that this is what you really mean:

< $285,714.30 3.50% $285,714.30 to $500,000 $10,000 $500,000.01 to $750,000 $12,000 >$750,000 $15,000There are (at least) 2 ways to do this.

One is a brute force method using an IF function.

=IF(A1<285714.3,A1*0.035,IF(A1<500000.01,10000,IF(A1<750000.01,12000,15000)))

The problem with this method is that if the compensation ever changes, you'll need to change the formula.

Another, more flexible option is to use a VLOOKUP table. You should review the Help files on VLOOKUP to help you understand how this works.

Put this table somewhere in your spreadsheet...I'll use A11:B14.

A B 11 0.00 =A1*.035 12 285714.29 10000 13 500000.01 12000 14 750000.01 15000Then put this formula in B1:

=VLOOKUP(A1, $A$11:$B$14, 2, 1)

If the compensation ranges and/or the compensation amounts change, you can just change the table and never touch the formula.

Posting Tip:Before posting Data or VBA Code, read thisHow-To.

Wow, thanks for the quick reply, DerbyDad03! I will look into it and follow-up in the next day or so, after I've had a chance to read-up on "VLOOKUP"...

I appreciate the help!

Ask Your Question

Weekly Poll