Hi,
I've been using this formula:
=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"))))
To auto populate the "Overall Score" (last) column in this spreadsheet below:
However some of cells in the "Overall Summary Cost" column (first column) contain N/A and this affects my overall score:
Is there a formula that when N/A appears in the first column, I can disregard it and the overall score column will just use the second and third column? I still want the last column to populate a colour.
Cheers,
Ann