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 •

#1
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.

=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	    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 •

#2
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