Average ifs formula

Hollie Green

I have tried using the AVG(Collect formula and can't get it to do what I need. Anyone have any ideas on how to get the average of the cells in column1 if the matching cell in column 2 is ="Inbound" and the matching cell in Column 3 is equal to either "Developing", "Contributing", or "Experienced"

Kelly Moore

Hey @Hollie Green

=AVG(COLLECT([Column 1]:[Column 1], [Column 2]:[Column 2], "Inbound", [Column 3]:[Column 3] OR(@cell="Experienced", @cell="Developing", @cell="Contributing")))

This formula is for data that is all within the same sheet. If you are bringing it into a different sheet then we will need to change the formula syntax. Let me know.

cheers,

Kelly

Kelly Moore

Hey @Hollie Green

=AVG(COLLECT([Column 1]:[Column 1], [Column 2]:[Column 2], "Inbound", [Column 3]:[Column 3] OR(@cell="Experienced", @cell="Developing", @cell="Contributing")))

This formula is for data that is all within the same sheet. If you are bringing it into a different sheet then we will need to change the formula syntax. Let me know.

cheers,

Kelly

Amber Jackson

@Kelly Moore

Could you include the syntax changes for the formula including multiple sheets? That's exactly what I need at the moment.

Thank you!

Kelly Moore

Hey Amber

Remember with cross sheet references you must physically create these in your sheet, you cannot simply copy paste this formula.

=AVG(COLLECT({source sheet column 1}, {source sheet column 2}, "Inbound", {source sheet column 3}, OR(@cell="Experienced", @cell="Developing", @cell="Contributing")))

If you need more information on cross sheet references you can find it here

Does this get you want you need?

Kelly