Exclude Zeros from Average If formula when selecting a range from a different sheet

Kate123

Hi there,

How can I exclude zeros from the below formula when referencing ranges in different sheets?

=AVERAGEIF({GOAL C8 Event Metrics Tracking - By Event Range 1}, "Speaker Series", {GOAL C8 Event Metrics Tracking - By Event Range 4})

Thanks!

Paul Newcome

You would need to switch over to an AVG/COLLECT like so:

=AVG(COLLECT({GOAL C8 Event Metrics Tracking - By Event Range 4}, {GOAL C8 Event Metrics Tracking - By Event Range 4}, @cell <> 0, {GOAL C8 Event Metrics Tracking - By Event Range 1}, @cell = "Speaker Series"))

Kate123

Thank you, Paul! This worked perfectly!

As I tried to use this same formula for other items such as "Conference" or "Panels" (which have no attendees yet), I am getting a #DIVIDE BY ZERO error. Is there anyway for this formula to show the result as 0 until people attend this events?

I hope this makes sense and thanks for your help!

Kate123

I figured that part out using the IFERROR at the start of the formula. However, I have encountered another issue:

One item I am trying to average is completion of different assessments (via personal development). In this instance, I want to include zeros in the average calculation.

I added a new column in so I could identify between personal development items that currently active and those that are pending (future based) - I want the calculation to only pick up the active items and average completion of those including zeros.

This is the formula I used, however the calculation seems to be excluding zeros as opposed to including them - any ideas on how to fix this?

=AVG(COLLECT({GOAL C8 Event Metrics Tracking - By Event Range 8}, {GOAL C8 Event Metrics Tracking - By Event Range 8}, @cell <> 0, {GOAL C8 Metrics Tracking - By Experience Range 2}, @cell = "Active", {GOAL C8 Event Metrics Tracking - By Event Range 1}, @cell = "Personal Development"))

Paul Newcome

@Kate123 The bold portion below is what excludes zeros. Try removing that to see if it helps.

=AVG(COLLECT({GOAL C8 Event Metrics Tracking - By Event Range 8}, {GOAL C8 Event Metrics Tracking - By Event Range 8}, @cell <> 0, {GOAL C8 Metrics Tracking - By Experience Range 2}, @cell = "Active", {GOAL C8 Event Metrics Tracking - By Event Range 1}, @cell = "Personal Development"))

Kate123

Thank you, Paul!! You solved my problem :-)

Paul Newcome

Happy to help. 👍️