finding average risk score

sophiaashepard

Hello,

I am trying to find the average risk score across multiple sheets given the criteria of being a risk and being open. I am trying to use an (AVG(COLLECT( but I am getting an invalid operation. Here is what I am trying:

=AVG((COLLECT({CA Risk Score}, {CA ROI Option}, "Risk", {CA Risk Status}, "Open") + COLLECT({CRV Risk Score}, {CRV ROI}, "Risk", {CRV Risk Status}, "Open") + COLLECT({HA Risk Score}, {HA ROI}, "Risk", {HA Risk Status}, "Open") + COLLECT({LEE Risk Score}, {LEE ROI}, "Risk", {LEE Risk Status}, "Open") + COLLECT({SSC Risk Score}, {SSC ROI}, "Risk", {SSC Risk Status}, "Open") + COLLECT({DTIM Risk Score}, {DTIM ROI}, "Risk", {DTIM Risk Status}, "Open")))

Paul Newcome

Try wrapping each of the individual AVG(COLLECT(....)) pieces in their own IFERROR function to output a zero in those instances where a sheet does not contain any matching rows.

Then you can wrap the whole thing in another IFERROR for when none of the sheets contain any rows to be included.

Paul Newcome

Try this instead:

AVG(AVG(COLLECT({CA Risk Score}, {CA ROI Option}, "Risk", {CA Risk Status}, "Open")), AVG(COLLECT({CRV Risk Score}, {CRV ROI}, "Risk", {CRV Risk Status}, "Open")), AVG(COLLECT({HA Risk Score}, {HA ROI}, "Risk", {HA Risk Status}, "Open")), AVG(COLLECT({LEE Risk Score}, {LEE ROI}, "Risk", {LEE Risk Status}, "Open")), AVG(COLLECT({SSC Risk Score}, {SSC ROI}, "Risk", {SSC Risk Status}, "Open")), AVG(COLLECT({DTIM Risk Score}, {DTIM ROI}, "Risk", {DTIM Risk Status}, "Open")))

sophiaashepard

Hey @Paul Newcome. Thank you! I get a #DIVIDE BY ZERO error I think because one of the sheets does not have anything that match the criteria. do you know how I could resolve this?

Paul Newcome

Try wrapping each of the individual AVG(COLLECT(....)) pieces in their own IFERROR function to output a zero in those instances where a sheet does not contain any matching rows.

Then you can wrap the whole thing in another IFERROR for when none of the sheets contain any rows to be included.

sophiaashepard

@Paul Newcome Thank you! That worked. Now I would like exclude zeros from being part of the average calculation... I tried doing this and it didnt work:

=IFERROR(AVERAGEIF(COLLECT({CA Risk Score}, {CA ROI Option}, "Risk", {CA Risk Status}, "Open")>0), 0)

Paul Newcome

You would still use an AVG/COLLECT combo (not the AVERAGEIF function), and you would include a range/criteria set where the range is the score range and the criteria is greater than zero.