Excel AverageIF issue... PLEASE HELP!

M

martinezr4

I am looking to do an AverageIf across multiple sheets but only if the
value is greater than 0. Now when I do a standard average it works
fine except it counts the cells with zero's against the overall
average which would be resolved if the averageif command worked.
Here is the command:
=AVERAGEIF(Start:End!H3,">0")
I have created a start and end sheet in the workbook so that it can
capture all sheets in-between but I cannot get this to work. Any
assistance would be appreciated!
 
G

Gary''s Student

You can use an averageIf array if you have an array. So lets cheat and make
one:

In I1 thru I3 in any sheet enter:
=Sheet1!H3
=Sheet2!H3
=Sheet3!H3
and so on downward and then:

=AVERAGE(IF(I1:I3>0,I1:I3))

enter as an array formula with CNTRL-SHFT-ENTER rather than just ENTER
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

AVERAGEIF 14
AverageIF 6
averageif function and pivot tables 0
AVERAGEIF FORMULA EXCEL 2007 1
Help with another formula, please 3
Follow-up AVERAGEIF question 2
AverageIF 1
Averageif help 7

Top