Average of multiple worksheets including #N/A values

[Jerry in Amsterdam]

Hi everyone.
I'm trying to calculate an average of values that are in cell D20 on 21 separate worksheets. Normally the formula =AVERAGE(Worksheet1:Worksheet21!D20) would do, however, there sometimes are #N/A! values in cell D20. This was done on purpose because the vaues are also used for a chart.
What would be a working formula for this? The SUM.IF.../(COUNTA-COUNT.IF) does not seem to work when the cells are on multiple worksheets.


As a bonus I should add that the 21 worksheets are actually 23 worksheets, but two worksheets (no. 7 and no. 12) are not to be included in the average.

 

[Anthony47]

Hi Jerry,
I would do the following way:
1) open the vba editor (Alt-F11) and insert this code in one of your Modlues (if missing, just add one via Menu ->Insert ->Module)
Function Soff(Ref, offset)
' Returns cell contents at Ref, in sheet offset
Application.Volatile
With Application.Caller.Parent
Soff = .Parent.Sheets(.Index + offset) _
.Range(Ref.Address).Value
End With
End Function

Credit for this Udf is to Mrexcel's or another forum, I don't remember
This is a "sheet offset" function, that will return the value in the Referenced cell in the offsetted sheet.


2) in a free area of your spreadsheet (for example from Z1 to the right) create a serie with 1, 2, 3, . . . . 23 (set Z1 to 1, put in AA1 the formula =Z1+1 then copy to the remaining cells); this index will be used as the "sheet offset"
3) in the lower row (thus in Z2) put the formula =SOFF(D20,Z1)
4) copy this formula rightward until the last index
5) you can use the ISERR function with the above formula to clear those #N/A values
5) calculate your average on this new table rather than on the several sheets.

If you wish to remove any sheet from the calculation, just skip its index in the serie (step 1).

Does this helps?

Bye,

 

[Domenic]

Download and install the free add-in Morefunc.xll, then...

1) Create two new sheets and name them First and Last.

2) Place all relevant sheets between these two new sheets.

3) Try the following formula, which needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER...

=AVERAGE(IF(ISNUMBER(THREED('First:Last'!D20)),THREED('First:Last'!D20)))

Alternatively, enter a list of sheet names in a range of cells, let's say A2:A22, then try...

=SUMPRODUCT(SUMIF(INDIRECT("'"&$A$2:$A$22&"'!D20"),"<>#N/A"))/SUMPRODUCT(COUNTIF(INDIRECT("'"&$A$2:$A$22&"'!D20"),{"<0",">=0"}))

Hope this helps!

 

[Jerry in Amsterdam]

Thanks guys, for your help! Although the vba stuff seems promising I don't feel that confident about my Excel skills. Recording a macro is as far as I will go...
And for downloading the add on, the computers at my work all have the "Download handy things from the internet" priviledge disabled.
So i tried the last formula with the indirect function, but I couldn't get it to work properly.
All this led me to new insights however, and I decided to simply create a list of the sheetnames on the "Average" worksheet in cells A44 to A65 and copied all the D20 values to that sheet, creating a range right there. From there on it was simple to get the average with =SUM(SUMIF(D44:D65;{"<0";">0"}))/SUM(COUNTIF(D44:D65;{"<0";">0"}))

So, an "indirect" thanks to ya!

 

[Domenic]

So i tried the last formula with the indirect function, but I couldn't get it to work properly.

Did the formula return an error value, or did it return an inccorrect amount?

...From there on it was simple to get the average with =SUM(SUMIF(D44:D65;{"<0";">0"}))/SUM(COUNTIF(D44:D65;{"<0";">0"}))

This formula, as is, excludes zero values from the average. Does the data contain negative values? If so, my understanding is that excluding zero values from the calculation does not represent a 'true' average. Is this what you want?