Identifying data by time and day of week

[jamesy5]

Hi,

If someone could help with the following query it would be greatly appreciated.

In the attached file you will see data for each half hourly period of a month – the date on the left hand side, and the time period at the top.

You will notice in the file that I have highlighted both the highest and lowest figures in the data (highlighted in orange and blue respectively).

All the data needs to be split into 3 categories:
1. Core (highlighted in green)
2. Unoccupied (highlighted in dark blue)
3. Shoulder (highlighted in grey)

Underneath the data I have created formulas that work out the total data in these time frames.

However, the key aspect I need help with is that weekend days (Saturdays and Sundays) also need to be classed as unoccupied data.

If someone could help it would be greatly appreciated.

Many thanks,

James

[BMV]

Please Login or Register  to view this content.

[Kaper]

By the way, for D42:

Please Login or Register  to view this content.

would be enough.

(But honestly, I first thought about even more complicated one myself : =SUMPRODUCT(C6:P35*(WEEKDAY(B6:B35,2)<6))+SUMPRODUCT(AU6:AX35*(WEEKDAY(B6:B35,2)<6))+SUMPRODUCT(C6:AX35*(WEEKDAY(B6:B35,2)>=6))
)


And final note - lower right corner cells are not empty but contain single space. If such spaces appear in other cells you could expect !ARG# errors with SUMPRODUCT (SUM just skips these cells)

[jamesy5]

Hi there,

Thanks very much for your help. I have a few further queries I hope you could help with.

I noticed that if the formula range is expanded to include empty cells, we receive an error. Ideally, the formulas would cover the entire data range of a month with 31 days. It could be copied to months that only have, either 28, 29 or 30 days and wouldn’t receive an error.

Separately, I have attached a further file and highlighted in orange two figures that it would be amazing if these could be worked out using a formula each month.

Many thanks,

James

[BMV]

Then it could be array formulas and IFERROR inside

[Kaper]

Hi James,
What is the point to put space in empty cells?
Unless there is important reason, empty cells shall be empty.

If they are empty, then formulas for pink cells (note that the formulas are universal for 28...31 days long months, that's why there is row 36 taken into account) are as easy as:

Please Login or Register  to view this content.