Average formula to ignore zeros over several tabs of data (in 1 spreadsheet)

michellepike

New Member
Joined
Aug 13, 2013
Messages
9
I have 12 tabs (1 for each month or period) in a spreadsheet and am trying to create a formula to give me an average from tab to tab but to ignore the 0.00%. (So the Period 2 tab needs average Pd1 & 2; the Period 3 tab needs average Pd1,2 & 3; and so and and so forth until Period 12 which needs to average the data from 12 tabs).

This is what I have so far (I am on the Period 3 tab so this is for Pd1, Pd2 and Pd3):

=SUM(('Pd 1-2015'!E78:K78,'Pd 2-2015'!E78:M78,G78:M78)/INDEX(FREQUENCY(('Pd 1-2015'!E78:K78,'Pd 2-2015'!E78:M78,G78:M78),0),2))

This is giving me #VALUE! error.

I've also tried

=AVERAGEIF(('Pd 1-2015'!E78:K78,'Pd 2-2015'!E78:M78,G78:M78),"<>0")

and get the same #VALUE! error.

Thank you so much in advance!
 

Some videos you may like

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

Watch MrExcel Video

Forum statistics

Threads
1,099,115
Messages
5,466,769
Members
406,497
Latest member
Bryanlim

This Week's Hot Topics

Top