Hi All,
I have an averageif formula that basically sort of looks like this . =averageif(a1,f1,z1). this formula is copied down but I just noticed that it calcualtes incorrectly if anyone of those cells contain a 0 value. It artificially drives the average down because it considers the 0 in the formula. Is there a way to write the average if formula to consider 3 cells for the avreage, as seen above, but only use the average if the value is greater than 0.
Thank you.
Not sure what this is doing as it compares F1 with A1 and averages Z1
=AVERAGEIF(A1,F1,Z1).
Better post a file as per instructions in yellow banner at top of page.
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
AVERAGEIF(range, criteria, [average_range])
So you are not averaging 3 cells there , you are only using the Value in Z1 to average ?
perhaps it would be better if you attached a spreadsheet sample with the data and also your expecred results
you have a 2010 and 365 what version are you actually using
you can add a criteria to only average if >0
But I dont know what your range actually is
Wayne
if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here
If you have a solution to your thread - Please mark your thread solved do the following: >
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Here is a sample document that outlines my current formula. Sorry I screwed this up and made it more difficult. Lets start again.
I have a huge file that runs quite a bit and there is currently an average formula, like the one attached. That uses the average function and takes in 3 cells. EG. =average(cell1, cell2, cell3). The problem with this formula is
if any of those cells have a 0 value it drages the average down significantly. I would like to use the same formula but somehow tell it that if there is a 0 value in any of those cells just use the cells that have an a numerical
value greater than one.
Is this a better explanation for you guys.
Again I apologize for the poor post.
Maybe try
=AVERAGEIF(A6:I6,">0")
or
=AVERAGEIFS(A6:I6,A6:I6,">0",$A$5:$I$5,"Num*")
or
=SUM(A6,E6,I6)/COUNT(1/A6,1/E6,1/I6)
or
=AVERAGE(CHOOSE({1,2,3},IF(A6,A6),IF(E6,E6),IF(I6,I6)))
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks