Formula...don't know where to start


I have 3 columns. The cells in each column can have a value of Low, Medium, or High. In a fourth column I want to return a value based the combination of Low, Medium, or High selected in the other three cells. So:

Two or more highs = 4

One high and two mediums = 4

One high, one med, one low = 2

Three mediums = 2

Two mediums and one low = 1

One medium and two lows = 0.5

Three lows = 0.25

This formula far exceeds my abilities. Any takers?

Best Answer


  • Mike Gilner
    edited 02/07/20

    Paul, you are a marvelous human being! I expected this to go unanswered. Not only did you provide the solution, you provided step-by-step guidance on how to get there. I am flabbergasted! Thank you so much. You put so much time and effort helping a complete stranger. Thank you. Thank you.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! 👍️

    The breakdown actually serves a few purposes...

    1. It helps you understand exactly how it works and why.
    2. It helps build out the more complicated final solution.
    3. It makes trouble shooting much easier.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!