Formula to creating a score table + label status dots

Ann11

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!

James Keuning

=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

@Genevieve P. are you able to assist please?

James Keuning

=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

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.

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