# IF formulas

✭✭

Hi there

I'm pretty new with SmartSheet and am in trouble with a formula...

I want to have a formula for the following:

I have four competency levels; basic, proficient, advanced and expert. I want to get the unchanged indicator if targeted and actual competencies levels match. If the actual competency level is below the targeted one, then the down indicator should appear. If the actual competency level is higher than the targeted one, then the up indicator should appear.

Kind of....

If targeted competency level = current competency level, then unchanged indicator

If targeted competency level = "Expert" and actual competency level IS NOT "Expert", then down indicator

If targeted competency level = "Advanced" and actual competency level = "Basic" or "Proficient", then down indicator

If targeted competency level = "Advanced" and actual competency level = "Expert", then up indicator

If targeted competency level = "Proficient" and actual competency level = "Basic", then down indicator

If targeted competency level = "Proficient" and actual competency level = "Advanced" or "Expert", then green button

If targeted competency level = "Basic" and current competency level IS NOT "Basic", then up indicator

Thanks a lot for helping me! 🤔

• ✭✭✭✭✭✭

Try this:

=IF([Targeted Competency]@row = [Actual Competency]@row, "Sideways", IF([Targeted Competency]@row = "Expert", IF([Actual Competency]@row <> [Targeted Competency]@row, "Down", "Sideways"), IF([Targeted Competency]@row = "Advanced", IF([Actual Competency]@row = "Expert", "Up", "Down"), IF([Targeted Competency]@row = "Proficient", IF([Actual Competency]@row = "Basic", "Down", "Up"), IF([Targeted Competency]@row = "Basic", "Up")))))

Hope this helps!

• ✭✭✭✭✭✭