Hi,
I am unable to average multiple cells on different sheets and exclude zero. i understand that averageif does not recognize different sheets. however my sum formula does not seem to work.
In Sheet1, cell W3, i need to type in a formula that would average items on sheet2!E5:E7 and sheet3!E5:E7 and exclude zeros on the calculations "<>0"
i tried =SUM('Sheet2'!E5:E7, 'Sheet3'!E5:E7)/INDEX(FREQUENCY('Sheet2'!E5:E7, 'Sheet3'!E5:E7),2) but its not accepting it.
thanks in advance!
You might try SUM / COUNTIF
Hope that helps.
-Z
Thanks but how will my formula be?
tia!
Syntax like this:
Maybe you could do it with 2x AVERAGEIF /2, but then you'd have to have at least one value in both ranges, so Zoodeek's solution is more robust that that.
Please Login or Register to view this content.
=sum(Sheet2!E5:E7,Sheet3!E5:E7)/(COUNTIF(Sheet2!E5:E7,"<>0")+COUNTIF(Sheet3!E5:E7,"<>0"))
This should get you where you want to go.
-Z
You need to tweak that syntax a little for the correct 3d references, and the INDEX function needs a 2nd argument of zero....like thisOriginally Posted by jayp_
=SUM(Sheet2:Sheet3!E5:E7)/INDEX(FREQUENCY(Sheet2:Sheet3!E5:E7,0),2)
That formula is easily "extensible" to any number of worksheets
Audere est facere
Perhaps it's worth adding the caveat that this depends on their being no negative numbers within those ranges.
Presume that the OP knows this in any case since they're using this set-up.
Regards
Absolutely, yes. Thanks XOR LX
My feeling is that if negative numbers are valid then you'd expect zero to be a valid value too - it's only where all values are positive that zeroes can be regarded as "no info" type values.
Having said that, if you do want the average to possibly include negative values you can define a sheetlist and use a SUMIF/COUNTIF construction to get the required result, e.g.
=SUMIF(INDIRECT("'"&sheetlist&"!E5:E7"),"<>0")/COUNTIF(INDIRECT("'"&sheetlist&"!E5:E7"),"<>0")
Maybe this will work for you:
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
You can still use the FREQUENCY set-up for negative numbers as well.
If the values in question are all integers:
=SUM(Sheet2:Sheet3!E5:E7)/SUM(INDEX(FREQUENCY(Sheet2:Sheet3!E5:E7,{-1,0}),N(IF(1,{1,3}))))
If not:
=SUM(Sheet2:Sheet3!E5:E7)/SUM(INDEX(FREQUENCY(Sheet2:Sheet3!E5:E7,0-{0.0000001,0}),N(IF(1,{1,3}))))
or some such suitably small value in place of 0.0000001.
Regards
Ha! I thought you might go that route.
I'm always a little dissatisfied with any solution that needs to use a VSV (very small value) like 0.000001, but it does the job
Completely agree with the VSV bit - I cringed a bit myself when posting that!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks