# 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

#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 