# Average of multiple worksheets including #N/A values

#### Jerry in Amsterdam

##### New Member
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

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

#### Anthony47

##### Well-known Member
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) _
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

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

##### MrExcel MVP
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?

Replies
4
Views
306
Replies
19
Views
609
Replies
10
Views
1K
Replies
2
Views
354
Replies
1
Views
307

1,172,023
Messages
5,878,769
Members
433,370
Latest member
mcleven

### 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.

### Which adblocker are you using?

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

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