Find Specific Days for Referance to Formula

doodlealien · #1 07-01-2009, 07:16 PM

Hello,

I have a column with dates for a month in descending order. I have another column where I manually enter values of work received on those days. Now, I want to create a formula that locates each Monday, then refers to the column immediatly to the right of the date in order to return a daily average of work received.

The reason I want to do this is because I create a new worksheet each month and it would save me a lot of time of manually changing my formula and picking out each Monday by hand.

Example :

Monday 46
Tuesday 77
Wednesday 12
Monday 36

I need the formula to automatically find the 46, 36, then return an average. Thank you!

**daddylonglegs** · #2 07-01-2009, 07:25 PM

If A2:A32 contains dates and B2:B32 contains amounts you can get an average for monday by using the formula

=AVERAGE(IF(WEEKDAY(A2:A32)=2,B2:B32))

This is an array formula that needs to be confirmed with CTRL+SHIFT+ENTER so that curly braces appear around the formula in the formula bar

Change the 2 to a 3 for Tuesday etc.

If A2:A32 actually contains the day, e.g. "Saturday", "Monday" etc.you can use this formula

=SUMIF(A2:A32,"Monday",B2:B32)/COUNTIF(A2:A32,"Monday")

doodlealien · #3 07-01-2009, 07:52 PM

Amazing! This worked like a charm, thanks a bunch! The CNTL-SHIFt-ENTER thing is a little strange but I can get used to it.

Also, one more quick question if you don't mind. Sometimes in the case of holidays, I remove a row of data, which removes the date and tally. All of the formulas based on this recover from this deletion except for my date formula which is used to remove Sundays and fill series.

=IF(WEEKDAY(C4)=7,C4+2,C4+1)

I enter 1/1/2009 in the first cell and it automatically fills out the month skipping sundays. When I remove a line, obviously the remaining days freak out. Any help in keeping the series in tact, skipping sundays and the lines I remove? Thank you!

**daddylonglegs** · #4 07-01-2009, 08:06 PM

Rather than deleting rows why don't you list all your holidays somewhere, e.g. H1:H10 then make your formula to generate the dates this in C5 copied down

=MIN(IF(WEEKDAY(C4)+{1,2,3,4,5}<>1,IF(ISNA(MATCH(C4+{1,2,3,4,5},H$1:H$10,0)),C4+{1,2,3,4,5})))

This will ignore all Sundays and those dates listed in H1:H10

Assumes that you never have 5 consecutive holidays/Sundays

doodlealien · #5 07-11-2009, 07:29 PM

Sorry for the long reply.

The above formula you created for me works great for removing holidays so thank you for solving that problem for me.

You mentioned that it would remove sundays as well, did you mean that I should manually enter the sundays in with the holidays or should the formula automatically remove them for me? Currently, the formula won't automatically remove them, was wondering if there is a way for it to do so. Thanks again!

**daddylonglegs** · #6 07-12-2009, 12:35 PM

Apologies, I misplaced a parenthesis. Try this version

=MIN(IF(WEEKDAY(C4+{1,2,3,4,5})<>1,IF(ISNA(MATCH(C4+{1,2,3,4,5},H$1:H$10,0)),C4+{1,2,3,4,5})))

You only have to list holidays in the holiday range. This version should correctly list all dates except Sundays and holidays.......

Welcome to the Excel Forum

Forum Statistics