IF/OR Equation Difficulties

[DocWats]

So I am trying to make a Sales Revenue (SR) Tracker for my office. Since I work in a call center there isn't much time to be filling in a tracker in between calls. So to make things simple for my employees I'm trying to have excel do most of the work.

So I have 8 products that generate sales revenue that I have put into a drop-down menu for cells C3 -> C22 as each rep is supposed to achieve 20 sales a day. Then the D column is for the amount the product was sold for, and then finally I have my Sales Revenue Column (F) that I want to use a fairly complicated equation which I am having trouble building.

Essentially I am trying to have the eq'n read such that IF C3 = product 1 then Sales Revenue of $X is made based on the $ value of the product. This sounds confusing so I will use an example to help clarify:

If a rep sells a Boat they get $20 SR for every $1000 of the boats value. Therefore if they sell a $10000 boat the rep makes $200 in SR.

This is a simple enough calculation however because cell C3 could be one of 8 possible values cell F has to be able to determine what the agent puts into cell C and the dollar value in cell D and determine which calculation to use because each product has a different SR calculation based on what it makes for the company. I want the spreadsheet to be simple to use thus when the agent selects one of the 8 product types from the drop down menu and the $ value of what they sold I want excel to automatically generate the SR value.

So far I have:

=IF(C3=(I3:K3:M3:O3:Q3:S3:U3:W3),(IF(C3=I3,J3, IF(C3=K3,L3, IF(C3=M3,N3, IF(C3=O3,P3, IF(C3=Q3,R3, IF(C3=S3,T3, IF(C3=U3,V3, IF(C3=W3, X3))))))))), False)

Where I3 = product 1, K3 = product 2, M3=product 3, O3=prod 4, Q3=prod 5, S3=prod 6, U3=prod7, W3=prod8

And the corresponding J3, L3, N3, P3, R3, T3, V3 and X3 are the associated eq'n fields.

Any help would be appreciated!

 

[Jose_Chasez]

One approach is to create a two-column table within your file. The first column would contain the list of 8 products (I.e., the valid choices from the drop down box), and the second column would contain the corresponding commission %.

Then, you could do a lookup based on the product selected in the drop down box, and multiply the value of the item sold by the appropriate commission rate (returned by your vlookup).

 

[ukmikeb]

Hi

If your commission values in J3, L3..etc are not likely to match your Product Numbers then try -

=IF(ISERROR(MATCH($C3,$I3:$W3,0)),FALSE,INDEX($I3:$X3,,MATCH($C3,$I3:$W3,0)+1))

hth