Formula with multiple IF(AND statements

Victoria Barrios

I am having trouble with a formula that will return a 1, 3, 5, or 10 based on a combination of two columns.

Anything selected as REPORTED will equal 1. Easy enough. But in the same column, If FIXED Other points will be rewarded based on the Category it is paired with. Here is what I have so far:

=IF([Reported or Fixed?]1 = "Reported", 1, IF(AND([Reported or Fixed?]1 = "fixed", Category1 = "Find & Fix"), 5, IF(AND([Reported or Fixed?]1 = "fixed", Category1 = "Procedure Review", 3, IF(AND([Reported or Fixed?]1 = "Fixed", Category1 = "Hands Free / Ergonomics Find & Fix", 10))))))

It works for the first part of the formula, but as I added to it, it now returns INCORRECT ARGUMENT SET.

Paul Newcome

Are the only two options for the column "REPORTED" or "FIXED"? If so, you can use some built in logic to simplify. The nested IF statement will work from left to right and stop on the first true value. So if the first argument is if it equals "REPORTED" then output 1, everything after that (if the only other option is "FIXED") will be assumed to be "FIXED" which means that you don't have to specify that part.

=IF([Reported or Fixed?]1 = "Reported", 1, IF(Category1 = "Find & Fix", 5, IF(Category1 = "Procedure Review", 3, IF(Category1 = "Hands Free / Ergonomics Find & Fix", 10))))

Victoria Barrios

Thank you very much!😎

Paul Newcome

Happy to help. 👍️