Solved

Average if to ignore 0 zero in data set

Posted on 2016-07-29
1
64 Views
Last Modified: 2016-07-29
Hi Experts using excel 2013

i have the following formula which i need to amend to ignore and not count the cells with zeros in them to reduce the average
=IFERROR(AVERAGEIFS(INDIRECT("Data!$Bm$2:$Bm$2599"),INDIRECT("Data!$AQ$2:$AQ$2599"),$D$3,INDIRECT("Data!$bp$2:$bp$2599"),O$5,INDIRECT("Data!$bn$2:$bn$2599"),$G$5),"-")
0
Comment
Question by:route217
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 26

Accepted Solution

by:
ProfessorJimJam earned 500 total points
ID: 41734517
=IFERROR(AVERAGEIFS(INDIRECT("Data!$Bm$2:$Bm$2599"),INDIRECT("Data!$AQ$2:$AQ$2599"),$D$3,INDIRECT("Data!$bp$2:$bp$2599"),O$5,INDIRECT("Data!$bn$2:$bn$2599"),$G$5,INDIRECT("Data!$Bm$2:$Bm$2599"),"<>0"),"-")
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question