mercredi 3 janvier 2018

Nested IF statements assistance

I have a workbook that allows users to input different employee/bonus combinations and I assigned a number 1-4 based on the specific combination (simple IF statement in cell E10).

Example:

  1. A=1
  2. A*D=2
  3. B+C=3
  4. B-A=4

I am trying to combine combination 4 formula into the 1-3 if formulas, but I receive the Too many arguments error message. I know why I am receiving the error due to combo 3 & 4 both having false statements, but I am wondering if these two formulas can be combined?

Combo 1-3 formula:

=IF('Input Data'!$E$10=2,IF(SUM(Rates!$E11:$F11)<1,0,1)*'Input Data'!$F$23+'Input Data'!$F$21,IF('Input Data'!$E$10=6,VLOOKUP(VLOOKUP(Summary!$A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,2,FALSE)*Rates!M11+VLOOKUP(VLOOKUP($A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,3,FALSE)*(Rates!N11+Rates!O11),IF('Input Data'!$E$10=1,'Input Data'!$F$17*Rates!M11+'Input Data'!$F$19*(Rates!N11+Rates!O11),IF('Input Data'!$E$10=3,IF(VLOOKUP(Summary!$A22,'Base Plan Rates'!$A$11:$M$60,13,FALSE)<VLOOKUP(Summary!$A22,Rates!$A$11:$M$60,13,FALSE),VLOOKUP($A22,'Base Plan Rates'!$A$11:$M$60,13,FALSE)*'Input Data'!$F$17+'Input Data'!$F$19*(Rates!N11+Rates!O11),VLOOKUP(Summary!$A22,Rates!$A$11:$M$60,13,FALSE)*'Input Data'!$F$17+'Input Data'!$F$19*(Rates!N11+Rates!O11))))))

Combo 4 formula:

=IF('Input Data'!$E$10=4,IF(VLOOKUP(Summary!$A22,'Base Plan Rates'!$A$11:$M$60,13,FALSE)<VLOOKUP(Summary!$A22,Rates!$A$11:$M$60,13,FALSE),VLOOKUP($A22,'Base Plan Rates'!$A$11:$M$60,13,FALSE)*VLOOKUP(VLOOKUP(Summary!$A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,2,FALSE)+(Rates!N11+Rates!O11)*VLOOKUP(VLOOKUP(Summary!$A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,3,FALSE),VLOOKUP(Summary!$A22,Rates!$A$11:$M$60,13,FALSE)*VLOOKUP(VLOOKUP(Summary!$A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,2,FALSE)+(Rates!N11+Rates!O11)*VLOOKUP(VLOOKUP(Summary!$A22,'Input Data'!$L$35:$M$84,2,FALSE),'Input Data'!$E$26:$G$30,3,FALSE)))

Thank you!

Aucun commentaire:

Enregistrer un commentaire