Averaging non-contigious ranges in Excel (excluding zeros)

[MaverickJC]

Hello. I hope this post finds you well.

I work for a hospitality company that want to find their full year average occupancy % as the year progresses.

The columns of the excel sheet are currently:
Jan 8th | Jan 15th | Jan 22nd | Jan 29th | JAN MTD AVERAGE | JAN TRAILING 3-MTH AVERAGE | Feb 5th | Feb 12th ... and so on.

I need to include the week data (%), but not the MTD AVERAGE or TRAILING 3 MONTH AVERAGEs columns, into a single FY average at the end of the year (which does not include zeros for the weeks which haven't happened yet)

I thought the correct formula would be (assuming starting cell A1):

=AVERAGEIFS(A1:D1,A1:D1,">0",G1:J1,G1:J1,">0",M1:P1,M1:P1,">0"))), etc. However this is returning a #ERROR!.

Am I simply missing a bracket somewhere? Can't really change the format of the spreadsheet now, it's a legacy one I've picked up and the site teams are used to the format.

Any guidance gratefully received. Thank you.

[MaverickJC]

Also, wanted to introduced myself more politely on the correct page, but was afraid to lose my thread. My name is James and I work in Finance at a UK-based hospitality company. Great to meet you all

[TMS]

Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[MaverickJC]

MaverickJC - AVERAGEIFS proforma.xlsx

I need CB2 to averageIF all the green cells (excluding any zeros), and also help with the MTD averages where they have zero data to calculate and return #DIV/0!

Thank you so much.

[josephteh]

Welcome to the forum, MaverickJC!

Try, =SUMPRODUCT(A1:P1*NOT(ISFORMULA(A1:P1)))/SUM(NOT(ISFORMULA(A1:P1))*1)

[CheeseSandwich]

For CB2:

Please Login or Register  to view this content.

The first six characters of the corresponding cell in the range C1:BZ1 must be "Sunday".
The value in the cell must be greater than zero.

Here's a breakdown of how the formula works:

The LET function allows you to define a named variable ("f") that represents the result of a FILTER function.
The FILTER function returns an array of values that meet the specified conditions. In this case, it returns a filtered array of values from C2 to BZ2 where the first six characters of the corresponding cell in C1 to BZ1 are "Sunday".
The named variable "f" is used as the input array for the AVERAGE function, which returns the average value of the filtered array of values, but only for values that are greater than zero.
So in summary, this formula calculates the average value of cells in the range C2:BZ2 where the first six characters of the corresponding cell in the range C1:BZ1 are "Sunday" and the cell value is greater than zero.

[CheeseSandwich]

For your MTD:

Please Login or Register  to view this content.

Or:

Please Login or Register  to view this content.

[josephteh]

Try, =AVERAGEIF(C$1:BZ$1,"Sunday*",C2:BZ2)

[CARACALLA]

CB2=AVERAGE(FILTER(FILTER(C2:BZ2,(ISNUMBER(SEARCH("2023",C1:BZ1))*(ISNUMBER(C2:BZ2)))),FILTER(C2:BZ2,(ISNUMBER(SEARCH("SUNDAY",C1:BZ1))*(ISNUMBER(C2:BZ2))))>0))

or

CB2=AVERAGE(IF(ISNUMBER(SEARCH("2023",C1:BZ1)),IF(C2:BZ2>0,C2:BZ2))) for office365 20121 only enter for other control+shift+enter

[josephteh]

Or, =AVERAGEIFS(C2:BZ2,C2:BZ2,">0",C$1:BZ$1,"Sunday*"), to exclude 0%

[TMS]

Wow, I turn my back and it's like a feeding frenzy!

[MaverickJC]

Thank you so much everyone! Very grateful for your input.

[MaverickJC]

Hi CARACALLA,

Please may I confirm if this formula would work for numbers rather than percentages?
CB2=AVERAGE(FILTER(FILTER(C2:BZ2,(ISNUMBER(SEARCH("2023",C1:BZ1))*(ISNUMBER(C2:BZ2)))),FILTER(C2:BZ2,(ISNUMBER(SEARCH("SUNDAY",C1:BZ1))*(ISNUMBER(C2:BZ2))))>0))

If works perfectly on the rows with percentages (thank you!) but not on those with numbers.

[JeteMc]

It may help us understand the issue if we could see a sample row that is not yielding the correct result.
Let us know if you have any questions.