Sheet average excluding certain sheets

[rirebel17]

I have a sheet for each business day on bottom of excel. I currently have formula '5.1.23:5.31.23' to calculate end of month details. How do I create the average omitting certain sheets. For example, I want the average from all 5.1.23:5.31.23. omitting sheet dated 5.22.23. This seems soo simple, but I'm not getting it, please help!

 

[Domenic]

Let's say that you want to average A2 from the desired worksheets, try the following...

=SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",SUMIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),"<>")))/SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",COUNTIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),">=-9.99999999999999E+307")))

Note, however, if one or more of the referenced sheets do not exist, the formula will return the error value #REF!. To allow one or more sheets to be non-existent, try the following instead...

=SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",IFERROR(SUMIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),"<>"),"")))/SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",IFERROR(COUNTIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),">=-9.99999999999999E+307"),"")))

Change the cell to be averaged accordingly.

Hope this helps!

 

[Domenic]

A much easier alternative, though, would be to create two new worksheets, and name them First and Last. Then place all of the worksheets to be averaged between these two sheets. Then you can simply use the following formula...

=AVERAGE(First:Last!A2)

Hope this helps!

 

[rirebel17]

Let's say that you want to average A2 from the desired worksheets, try the following...

=SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",SUMIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),"<>")))/SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",COUNTIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),">=-9.99999999999999E+307")))

Note, however, if one or more of the referenced sheets do not exist, the formula will return the error value #REF!. To allow one or more sheets to be non-existent, try the following instead...

=SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",IFERROR(SUMIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),"<>"),"")))/SUM(IF("5."&ROW(INDIRECT("1:31"))&".23"<>"5.22.23",IFERROR(COUNTIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),">=-9.99999999999999E+307"),"")))

Change the cell to be averaged accordingly.

Hope this helps!

Hello Domenic!! The 2nd worked, this is brilliant!! Sooo, last thing.... say, I want to omit 5.22.23 AND 5.24.23, how would that be formatted, using the 2nd formula you've provided? I'm not sure where to add this detail. Thank you in advance!

 

[Domenic]

In that case, try the following...

=SUM(IF(ISNA(MATCH("5."&ROW(INDIRECT("1:31"))&".23",{"5.22.23","5.24.23"},0)),IFERROR(SUMIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),"<>"),"")))/SUM(IF(ISNA(MATCH("5."&ROW(INDIRECT("1:31"))&".23",{"5.22.23","5.24.23"},0)),IFERROR(COUNTIF(INDIRECT("5."&ROW(INDIRECT("1:31"))&".23!A2"),">=-9.99999999999999E+307"),"")))

Note that you can add to the constant array {"5.22.23","5.24.23"} as desired.

Hope this helps!