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

enter image description here

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?

(for further reference)


1 Answer 1


=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...
    – Matt Davis
    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)
    – user0
    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.
    – Matt Davis
    Commented Jan 10, 2019 at 12:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.