VLookup If Date is equal to the Day

[dandiehl]

I have a list that contains a number for each day of the year. Column A
contains the date and column B has the corresponding number of widgets. I
would like wrtie a formula that will return the year-to-date average number
of widgets by day. It might appear like this:

Day Avg Widgets
Sun 45
Mon 215
Tue 246
Wed 302
Thu 285
Fri 177
Sat 96

 

[Roger Govier]

Hi

In C" try
=AVERAGE($B$2:B2)
and copy down as far as required.

 

[mike]

You can use the =SUMIF() function.

Convert your numbers into excel dates (type in the date that is represented
by "1", and drag down 365 cells, Excel will fill in the dates for you (I'm
assuming you were accurate when you said there is a number for EVERY day in
the year). You can do this in another column - we'll say column C.

=SUMIF(C:C,WEEKDAY(C1),B:B)

This will give you the result for whatever day of the week cell C1 is If
your day "1" is not Monday, have your "WEEKDAY()" function look to any day
that IS a Monday in your list and you will get the total for all Mondays.
Repeat this for each day of the week you want added to the total. This'll
work as long as you keep in mind the reference in the WEEKDAY() function
points to the day of the week to be totalled. In the end you'll have the
formula above in seven cells where the WEEKDAY() function will reference
cells for each day of the week (I'm assuming it will be C1, C2, C3, or C2,
C3, C4 etc.

Hope I didn't confuse you . . . .

 

[mike]

Sry, I forgot about the "average part" of you question. No time now I'll
check later if someone hasn't helped you further . .

 

[Barb Reinhardt]

1) Add a helper column next to the week day

Sunday 1
Monday 2
Tuesday 3

and so on.

I'm going to assume the helper column is G


=AVERAGE(IF(WEEKDAY(A2:A15)=G2,B2:B15))

The serial dates are in A2:A15 and the daily counts are in B2:B15. G2
contains the Weekday. Commit with CTRL SHIFT ENTER.

 

[Barb Reinhardt]

Don't forget the $ on the cell values to ensure that the row #'s don't change
as you copy down.

 

[dandiehl]

Mike, thanks for the response. I tried your method but I am still having
some trouble, so let me give some more detail regarding the problem.

My source data looks like this...
COLUMN A contains my dates (4/5/2008 - 1/1/2008)
COLUMN B contains my total number of widgets for the corresponding date

My constraint is that I cannot add a helper column that converts the date
into a text field that identifies the weekday such as... TEXT(A2,"ddd"). The
reason for this constraint has to do with the method in which the data is
populated in the list each week.

My desired result is to see the average number of widgets by weekday (for
all the dates in the source data list).

Any help would be appreciated. Thanks!

Dan

 

[Roger Govier]

Totally misread the question sorry.
Please ignore my post