Formula to calculate maximum compensation

Microsoft Office excel 2003
November 15, 2010 at 14:01:01
Specs: Windows XP, no idea
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!)

See More: Formula to calculate maximum compensation

Report •

November 15, 2010 at 15:58:34
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,000 

There are (at least) 2 ways to do this.

One is a brute force method using an IF function.


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	    15000

Then 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 this How-To.

Report •

November 15, 2010 at 16:21:56
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!

Report •

Related Solutions

Ask Question