Formula to creating a score table + label status dots

Options

Hi,

I've looking to create a score table (see picture below). Basically I want each drop-down "Value" in a cell to be given a weight and based on that weight, I want it to add up to a score which will then auto populate a label status dot colour.


E.g

Under budget (1) + Ahead of schedule (1) + Low (1) will give me a score of 3. A score of 3 will auto populate blue in the overall score.

Thanks!

Best Answer

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    =IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 5, "Blue", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 8, "Green", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 11, "Yellow", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 13, "Red", ">12"))))

Answers

  • Ann11
    Ann11 ✭✭
    Options

    @Genevieve P. are you able to assist please?

  • James Keuning
    James Keuning ✭✭✭✭✭
    Answer ✓
    Options

    =IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 5, "Blue", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 8, "Green", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 11, "Yellow", IF(SUM(VALUE(RIGHT([Overall Summary Cost]@row, 1)), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 13, "Red", ">12"))))

  • Ann11
    Ann11 ✭✭
    Options

    Thank you @James Keuning that worked! I'm just wondering, is there a way I can still show this without having the actual numbers next to each other?


    But it will still give me the same result?

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @Ann11

    I actually agree that having the number after your words would be the easiest way to do this. You're looking to SUM text to find a numerical weight, but without a number associated with the text, the SUM function won't know what those weights are.

    Another option may be to have 3 helper columns in your sheet, automatically showing the numbers. So you'd have "Overall Summary Cost" with text, then "Summary Cost Value" with only the associated number.

    Then your IF statement would simply SUM together your 3 helper column cells, instead of stripping the value from the right. Does that make sense?

    Cheers,

    Genevieve

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!