More than 7 IFs

[catzmeow]

I am working on a sheet that will automatically calculate staff commissions based on sales and points. This formula needs to go in the cell which will display earned commission. Commission is only earned if three conditions are met - SalesA must be at least 8, SalesB must be at least 12, and the salesperson must have met Level 1 in points. If all three conditions are met, commission should be calculated based on the Level reached - there are 7 levels. These levels are located on a separate sheet called 'Office Setup'.

I was doing this with =SUM(IF... and nesting my IFs. This works fine when I'm only summing based on levels, but I would like it to also test the three conditions and that makes more than seven IFs.

If you could please help me write this formula... here is my best description of what I'm trying to do:

IF SalesA>=8 AND SalesB>=12 AND Points>='OfficeSetup!'Level1, THEN
-

IF Points>='OfficeSetup'!Level7, THEN (SalesA*Level7)+(SalesB*Level7),
IF Points>='OfficeSetup'!Level6, THEN (SalesA*Level6)+(SalesB*Level6),
IF Points>='OfficeSetup'!Level5, THEN (SalesA*Level5)+(SalesB*Level5)... all the way to Level1.

If Level1 is not met, it should display $0.00 because that is one of the three major conditions above.

To further complicate things, there are SalesC and SalesD categories that are not based on the level. As long as the three conditions are met, SalesC/D are awarded on a flat rate listed in the commission schedule... (SalesC*FlatRateC)+(SalesD*FlatRateD)

I have never worked with VBA and I'm not super experienced with Excel in general, but I have no problem with an advanced formula, especially if you can explain it to me. I've tried to set up the worksheet so that once this formula is in, it can be locked and all of the variables can be changed on a separate sheet without unlocking the formula. As long as it works, I never need to touch it again.

I would be happy to send you the sheet if it helps.

Thank you in advance, and I hope my explanation is clear enough.

[DonkeyOte]

A sample file will help - dummy names etc so as to avoid confidentiality issues... also outline expected results based on dummy values etc...

Try to make sure whatever you upload is a super accurate reflection of your real life file in terms of ranges, data types, sheet names etc etc....

A key consideration will be whether or not the Commission payments are tiered - ie first $ at rate 1, then next $ at rate 2 etc or whether (as implied) it's a flat % but the % will vary based on a number of factors.

[catzmeow]

commission.xls

here is an example. The cells in question are B4 and B5 on sheet 'Production.' Based on the fact that B7 (Red Entered) is only 11, the commission for this sales ID should be $0. I've got the 7 nested ifs that I am allowed, but can't figure out how to add my three conditions as stated above. All the info for the commission schedule is on sheet 'Office Setup.'

It is not tiered in the manner you described. It's a flat rate per sale in each category, but the rate depends on the number of points and having a minimum of 12 Red sales and 8 Blue sales.

[catzmeow]

sorry to bump, but I really need some help. I've been trying to get this to work on my own for over a week now and can't seem to get it. The example sheet is exactly like the one I am using, I just used generic names for sales people and products.

[DonkeyOte]

I did have a quick look at the file yesterday but I'm afraid I couldn't follow it and I have not had the time since to look at it properly... sorry.

Hopefully by replying to this post and bumping back up someone else may be in a position to look at it in more depth for you ?

Thanks,
D.O.

[ChemistB]

Okay, I think I can help. SalesA >= 8 and SalesB >= 12. What/where are SalesA and B?

[catzmeow]

at this moment, A is 20 and B is 7. I would need it to update automatically though if the value in B was to reach 8 or more.

[ChemistB]

On your "Red & Blue Commissions Paid" table what cell (or cells) need to be <$200 or >200. (Which column if = 200)?

[catzmeow]

On your "Red & Blue Commissions Paid" table what cell (or cells) need to be <$200 or >200. (Which column if = 200)?

On the setup sheet, it is H18:H24 for <$200 and I18:I24 for >=$200. Is that what you mean?

[shadestreet]

catzmeow - I don't fully understand your issue, however, I will give you some general tips that may come in useful:
-To get around the 7 nested IFs constraint you can have the 7th IF statement refer to another cell in excel which contains another nested IF argument.

-Have you considered the vlookup formula to find the commision to apply? You could concatenate the results of "Line" "Type" and any other criteria to create a 'code' that would be looked up in an array. (to concatenate use the ampersand '&' between values). Then an array with a column containing every unique code (i.e., RedRed1, PurplePurple2, etc) and the adjacent column with the commison rate. Then vlookup against that array.

[ChemistB]

First I simplified the formula currently existing in B4 to

=(B7+B11)*VLOOKUP(B3,ComTable1,3,TRUE)+B10*VLOOKUP(B3,ComTable1,2,TRUE)+IF(B3>='Office Setup'!G18,((B33*'Office Setup'!M16)+(B32*'Office Setup'!M15)+(B31*'Office Setup'!M13)+(B30*'Office Setup'!M12)+(B29*'Office Setup'!M11)+(B28*'Office Setup'!M10)+(B27*'Office Setup'!M9)+(B22*'Office Setup'!M8)+(B20*'Office Setup'!M6)+(B18*'Office Setup'!M4)+(B15*('Office Setup'!M3/'Office Setup'!N3))),0)

It is not an array formula. I assigned the name ComTable1 to the Office Setup cells G18:I25. In that Table (row 18), I added a level 0 (0,0,0,0) so that values of less than 500 points will give a result of 0. Not sure where to go from here until I understand the spreadsheet a little better.

The second part of that formula where you're taking values from each color and multiplying it by the corresponding value in your commission table schedule could be greatly simplified with SUMPRODUCT if you brought all those cells together (B15, B18, B20...) in another table. Just a thought.

[ChemistB]

at this moment, A is 20 and B is 7

Okay, where can I see this? Which cells? Thanks.

[martindwilson]

MS Office Version:Excel 2007

64 nested ifs in 2007! but there is probably a better way!