Disregarding N/A in Formula

Ann11

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

Dan W

You might be able to find a solution in this thread

https://community.smartsheet.com/discussion/69045/ignoring-n-a-in-formula

Paul Newcome

I would suggest splitting this across two separate formulas in two separate columns. One that outputs the number itself and then another to output the color.

Basically what you are going to need to do is wrap each of those portions in an IFERROR to output a zero.

=IF(SUM(IFERROR(VALUE(RIGHT([Overall Summary Cost]@row, 1)), 0), VALUE(RIGHT([Overall Summary Schedule]@row, 1)), VALUE(RIGHT([Overall Summary Risk]@row, 1))) < 5, "Blue", IF(SUM(IFERROR(VALUE(RIGHT([Overall Summary Cost]@row, 1)), 0), VAL............................................

Ann11

@Paul Newcome how would I create the first formula? The one that outputs the number itself first.

I'm also assuming that your formula above is for the second formula?

Paul Newcome

@Ann11 My apologies. I wasn't very clear.

I actually provided two pieces.

First: "I would suggest splitting this across two separate formulas in two separate columns. One that outputs the number itself and then another to output the color."

In the first column is where you would put your SUM function that adds up each value. In the second column would be your nested IF that says if the [first column] is less than # then output "color".

The reason for splitting is simply management/personal preference as opposed to something that MUST be done. Yes. You would have an extra column and an extra formula, but both formulas combined are going to be much shorter and easier to adjust than if you were to try to combine them into a single formula.

Second: Referencing IFERROR

Each of your VALUE(RIGHT(.........)) sets should be wrapped in an IFERROR to output a zero no matter if you split it out across two columns or just update your existing formula. The root cause of the issue is that the VALUE function is throwing an error when it gets to "N/A" because "A" cannot be directly converted into a numerical value.

IFERROR(VALUE(RIGHT(.........)), 0)

Ann11

@Paul Newcome thanks for the clarification, that makes sense. But can you show me an example?

Paul Newcome

@Ann11 An example of which? There is an example of the IFERROR bit just above.