Average of multiple worksheets including #N/A values

Jerry in Amsterdam

New Member
Joined
May 2, 2006
Messages
47
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.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
2,148
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

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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

New Member
Joined
May 2, 2006
Messages
47
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! :LOL:
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,697
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?
 

Forum statistics

Threads
1,136,623
Messages
5,676,857
Members
419,656
Latest member
lironprofit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top