Formula based on multiple criteria

[Newsgal]

I need to set up a commission plan that drives the following payouts:

Achieve Payout
$0-$4999 5% of total achieved
$5000-$9999 8% of total achieved
$10,000+ 10% of total achieved

Which is the best Excel formula to use?

 

[Steven]

Arrange the payout table as follows - achievement levels in column A
listing the lower limit of the range (e.g. $0, $5,000 and $10,000) and
the % in column B (5%, 8% and 10%).
=LOOKUP(total achieved,A1:A3,B1:B3)

 

[Chip Pearson]

Enter the following values in some range, say A1:B3

0 0.05
5000 0.08
10000 0.10

Then, use a VLOOKUP

=VLOOKUP(amount,A1:B3,2,TRUE)

where amount is the value to be tested. The amount value in the first
column (0, 5000, 10000) must be ascending order. The values in the
second column need not be in any particular order.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)