Formula to calculate average and not include blanks or zeros in the calculation

[SM mechanic]

I have a workbook I use to track production with my techs. It has a sheet for each week. At the beginning of the workbook there is a sheet labeled First, and one at the end labeled last. I use the formula =IFERROR(AVERAGE(FIRST:LAST!A1)""), as an example, on a sheet labeled averages. Each week I add a sheet between First, and Last and input their actual production. The formulas are on the AVERAGES sheet. I track their average daily production, as well as average weekly production, and total production. A1 changes in the formula based on what cells I am looking to get an average for. But otherwise that is the formula I use on most of that sheet. Where it goes wrong is when it calculates an average weekly production. I realized the averages aren't right when they take a weeks vacation. I have tried adding <> or <>0 to my equation and neither changes the result.

I also tried taking the the average from the calculated averages on the "Averages" sheet. But averaging the averages for each day, doesn't give a true average weekly number.

I hope this makes sense, I realize it is kind of convoluted.

 

[etaf]

are you still using 2007
I think you may need to use indirect() and then a column of sheetnames - which can be named

is that something you could do ?

sumif - will not do 3d references

 

[etaf]

need to lookup , when frequency () says its in 2007 version

=SUM(FIRST:LAST!A1)/(INDEX(FREQUENCY(FIRST:LAST!A1,0),2))

will not work for negative numbers , copied from another forum

Book3
	A	B	C	D	E	F
1		I have 4 sheets , with 0,10,0,10 in cell a1		expected mock up results - on 1 sheet excluding zero
2				0
3				10		5
4	5	=AVERAGE(FIRST:LAST!A1)		0
5	10	=SUM(FIRST:LAST!A1)/(INDEX(FREQUENCY(FIRST:LAST!A1,0),2))		10		10
Sheet1

Cell Formulas
Range	Formula
F3	F3	=AVERAGE(D2:D5)
A4	A4	=AVERAGE(FIRST:LAST!A1)
A5	A5	=SUM(FIRST:LAST!A1)/(INDEX(FREQUENCY(FIRST:LAST!A1,0),2))
F5	F5	=AVERAGEIF(D2:D5,">0")

will be on share - only for a few days

Dropbox

www.dropbox.com

other forum found solution

averageif across worksheets [SOLVED]

Hi, I have several worksheets that are all formatted exactly the same and then one worksheet at the back with various kinds of summary and total data. An example spreadsheet is attached. Each worksheet, except the summary one, represents the sales and costs of an individual store from...

www.excelforum.com

 

[SM mechanic]

The formula you wrote with frequency in it worked. To be honest I have no idea what it is doing. But I copied it into my worksheet. Then hand calculated the numbers to check it and it works.

Thank you very much!!

 

[etaf]

you are welcome
the link i posted from the forum i copied - also have an explanation of the formula - i just used/modified the positive values part - maybe worth a read