How can I collect a prior score based on 2 criteria?

lindseycarlisle

We do 5S Audits of our building and I need to be able to pull the prior two scores of the same area. It worked for a while but then all of a sudden it started showing the Divide by Zero Error even though there are prior scores. Prior Score Formula:

We got the prior 5S Date to pull but for some reason, we can't pull the score. Prior Date Formula:

We have so many date columns due to indexes on other sheets/dashboards.

Can anyone see where I went wrong?

If anyone has any ideas for the "Last, Last" score that would super helpful as well!

Paul Newcome

That particular error is most likely pulling through from a referenced range. Double check all of the ranges being referenced by the formula and ensure there are no cells containing an error.

lindseycarlisle

@Paul Newcome Thank you for the suggestion! The top row should show an error since it was the first audit done for that area... All other cells that should be referenced don't have an error. The second row should be pulling the score from the top row.

Genevieve P.

Hi @lindseycarlisle

What formula are you using in the 5S Score column? Is it possible that one cell there has an error?

I would suggest wrapping that 5S Score formula in an IFERROR to see if it helps:

=IFERROR(formula, "")

Cheers,

Genevieve