# 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!

• ✭✭✭✭✭
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"))))

• ✭✭
Options

@Genevieve P. are you able to assist please?

• ✭✭✭✭✭
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"))))

• ✭✭
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?

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!