# How to include multiple IF statements with different data validations

I'm currently stuck trying to script a formula to populate a total value cell the factors in two different data validation columns (below):

The first column is to deduct a certain value per hour, the second multiply the value per hour both pending the variable selected.

For the first column the script works fine,

``````=IF(B6="senior",AL6*1,IF(B6="19 Y/O",AL6-4.21*AK6,
IF(B6="18 Y/O",AL6-6.32*AK6, IF(B6="17 Y/O",AL6-8.42*AK6, IF(B6="<16 Y/O",AL6-10.53*AK6)))))
``````

and likewise for the second:

``````=IF(C6="Part Time",AL6*1,IF(C6="Casual",AL6*1.25, IF(C6="Full Time",AL6*1,
IF(C6="Duty Manager", AL6*1.125, IF(C6="Supervisor",AL6*1.039, IF(C6="Salary",AL6*1,))))))
``````

However, I can't get them to work in tandem/the same line to populate a singular cell with both. Am I overlooking something simple?

``````=IF(C6="Part Time",    AL6*1,
IF(C6="Casual",       AL6*1.25,
IF(C6="Full Time",    AL6*1,
IF(C6="Duty Manager", AL6*1.125,
IF(C6="Supervisor",   AL6*1.039,
IF(C6="Salary",       AL6*1,))))))-
IF(B6="senior",       AL6*1,
IF(B6="19 Y/O",       AL6-4.21*AK6,
IF(B6="18 Y/O",       AL6-6.32*AK6,
IF(B6="17 Y/O",       AL6-8.42*AK6,
IF(B6="<16 Y/O",      AL6-10.53*AK6)))))``````

• Is there a way to make the data validations work in tandem? For example if column B were to select "17 Y/O" and column C were to be "Duty Manager" the equation would be akin to "x=(AL6*1.25)-(8.42*AK6)" where 'x'=final cost, 'AL6'=\$base rate, and 'AK6'=total hours... Commented Jan 10, 2019 at 11:45
• @MattDavis not sure if I follow you. can you elaborate more? you provided two formulas in your post... do you want to minus the 2nd from 1st? (also, if you would provide copy of your sheet you would already have your answer) Commented Jan 10, 2019 at 11:49
• docs.google.com/spreadsheets/d/… I suppose it could work as a minus 2nd from 1st, but to my understanding that would require more cells populated due to the data validation columns. Commented Jan 10, 2019 at 12:03