Formula Help

[Firefightjim]

Here is a hard one. I am a sales rep and I get paided based on how much
profit the order makes and at what margin it was sold at. To make it
even harder there is two scales. On for warehouse orders and on for
direct orders. I want to make a formula that will tell me what I make
on an order.

So what I want is to be able to enter the dollar amount in a cell,
Enter the profit amount in a cell, enter if it is a stock or a direct
in a cell

and from there I want it to go the right cell in the table.
I would want below line 6 for it to look at B6,v6 and d6, the correct
cell would be H-6.

A B C D E F G H I
4 Profit Margin % Stock or Direct
5 Stock
6 220 12.5 S 200-300 14 15 16
7 220 12.5 D 100-200 13 14 15
8 0-100 12 13 14
9 10-12 12.1-13 13.1-15
10
11 Direct
12 200-300 14 15 16
13 100-200 13 14 15
14 0-100 12 13 14
15 10-12 12.1-13 13.1-15

 

[edcosoft]

I can't read your table and intent very well , but If your pay is B X C
X a factor (either S or D) do this. Basically put a formula in H-6
reading =B6*C6*if(D6="S",0.92,0.97). Then copy it down to the bottom
of your page. Where .92 is your factor for D and .97 is your factor
for S. ed

 

[Stefi]

Rearrange your table a little bit:

Column F should be:
0
100
200
under both Stock and Direct,
Row 9 and 16 should be: 10 12 13

I used in my formula cells B20, C20, D20 for input. Insert this formula in
cell E20:

=INDEX(INDIRECT("G"&IF(D20="S",6,13)&":I"&IF(D20="S",6,13)+3),MATCH(B20,INDIRECT("F"&IF(D20="S",6,13)&":F"&IF(D20="S",6,13)+3),1),MATCH(C20,INDIRECT("G"&IF(D20="S",6,13)+3&":I"&IF(D20="S",6,13)+3),1))


Regards,
Stefi

„Firefightjim†ezt írta: