Is this a chart or a formula or something else?

[Guest]

Let me try this again and be more complete. Maybe someone can help me. I
want to be able to enter information of sales and margin for certain orders
into a & b on a spreadsheet and then have excel return a certain number into
c based on the following chart. Which I can then add up at the end of the
month for each sales person. Unfortunately I am not an expert in excel. Is
this a chart I need to reference or can it be entered as a formula and how
would I do it to get the corret result? Is excel even capable of doing this?

sales margin points

$500-749 >=50% 1
$750-999 >=50% 2
$1000-1999 35-49% 2
$1000-1999 >=50% 4
$2000-4999 >=30-34% 2
$2000-4999 >=35-39% 3
$2000-4999 >=40% 5
$5000-9999 >=26-29% 8
$5000-9999 >=30-34% 12
$5000-9999 >=35% 16
$10,000 + >=23-27% 12
$10,000 + >=28-31% 16
$10,000 + >=32% 24

Eg. we enter the information in a & b and the worksheet puts the number in
c. then I can sum up the total of c. as in this example below.
a b c
sales margin points
1243 37 2
2457 44 5
556 52 1

Anyone who could help with an answer, I would greatly appreciate it.

 

[Jason Morin]

First, set your table up as such:

A B C D
1 500 0.5 1 1
2 750 0.5 1 2
3 1000 0.35 0.49 2
4 1000 0.5 1 4
5 2000 0.3 0.34 2
6 2000 0.35 0.39 3
7 2000 0.4 1 5
8 5000 0.26 0.29 8
9 5000 0.3 0.34 12
10 5000 0.35 1 16
11 10000 0.23 0.27 12
12 10000 0.28 0.31 16
13 10000 0.31 1 24

With sales in F1 and margin in G1, use the formula:

=INDEX(D113,MATCH(2,1/((F1>=A1:A13)*(G1>=B1:B13)*
(G1<=C1:C13))))

Array-entered, meaning press ctrl/shift/enter.

If you'd like the workbook I used to test this, send me
an email. Replace OPPOSITEOFCOLD with you know what.

HTH
Jason
Atlanta, GA