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!
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!