CODEWITHSUNDEEP
vbaexcelvb6
Hrqls
Hrqls

Reputation: 2951

Average and sum of data of all mondays, tuesdays, etc in a month

I have an excel document with a main sheet and 1 sheet for every day of the month. Every month this excel document will be filled with data for each day.

The main sheet has to show the average and sum of the data of each weekday.

My problem is that every month the weekdays are on other days in the month, so I can't simply use the 1st day of the month for a monday.

I start with an empty Excel document with only the main sheet. My VB6 program then adds the sheet for each monthday with the data.

I know how to fill the monthday sheets with data, but I don't know how to fill the cells in the main sheet with a formula.

The average and sum formula's in the main sheet have to be dynamic to chose which sheets are mondays, tuesday, etc.

Here are some simplified examples of the sheets of january and february:

januaryfebruary

The real monthday sheets contain much more data, and the main sheet more analyses. See below for a part of it:

part of the real document

I see various way to go for solving this:

How can I make sure the cells in the main sheet always use the right weekday, or how can I from my VB6 program write a formula in the cells in the main sheet?

Upvotes: 0

Views: 1206

Answers (1)

Dan
Dan

Reputation: 45752

First, if you can, I suggest you change your workbook structure to use a tidy data layout, i.e. don't have separate sheets for each day but rather concatenate all your data and add a column for day of month. Then you can just use a single pivot table.

Failing that, make a column of numbers 1 to 31, make a cell with the year and another with the month. Use =DATE() to make a cell of the dates for the month and then use =WEEKDAY() to get the week day for each month. In another column average and sum your data per day of month. then either use a pivot table or just SUMIF to aggregate this up to a day of week level.

enter image description here

Upvotes: 2

Related Questions