Formulas and Functions
Formulas and Functions
Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.
Exclude Zeros from Average If formula when selecting a range from a different sheet
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!
Answers
-
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"))
-
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!
-
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"))
-
@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"))
-
Thank you, Paul!! You solved my problem :-)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.9K Get Help
- 441 Global Discussions
- 153 Industry Talk
- 500 Announcements
- 5.4K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 78 Community Job Board
- 511 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm curious for some inspiration please! I'm using a form to log usage of machinery. This collects data on the time used in minutes, which I convert to hours and total for each machine. The machines n…
- I am trying to count the number of times "N/A" is included in a single row. I know it can been done with a single range with the below equation, but in this sheet there are some columns that want to e…
- Hello! Long time listener, first time caller — We have a weekly update dashboard that is presented on Thursdays. We'd like to have the date displayed be either the previous weeks Thursday or the curre…