mercredi 19 juillet 2017

Complicated formula for nested ifs based on multiple conditions

I am trying to develop a formula for use in a Google Sheet. We have about 20 categories of customer complaints. We've connected each of these customer complaints to different sections of our internal standard evaluation, which are all scored 1-3. Internal QA performs an evaluation to a Google Form, and I need a formula that analyzes the evaluation according to the following pseudo-code

If(tag1)
    Check(vlookup(eval_ID, eval_range, category A col_index#,0)<3)
        if true, return "Valid"
        if false, continue
    Check(vlookup(eval_ID, eval_range, category B col_index#,0)<3)
        if true, return "Valid"
        if false, continue
    Check(vlookup(eval_ID, eval_range, category A col_index#,0)<3)
        if true, return "Valid"
        if false, return "invalid"
If(tag2)
    Check(vlookup(eval_ID, eval_range, category X col_index#,0)<3)         
        if true, return "Valid"
        if false, continue
    Check(vlookup(eval_ID, eval_range, category Y col_index#,0)<3)         
        if true, return "Valid"
        if false, return "invalid"
If(tag3)
    ...

Each tag has different categories, and different numbers of categories. Does anyone have a suggestion for how to write this formula? I don't want to use VBA because I want it to automatically calculate as the form is populated rather than manually running the macro.

I'm not sure where to start. Any advice is appreciated, thanks in advance!

Aucun commentaire:

Enregistrer un commentaire