Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

How do I obtain the Average Values between 2 dates?

0 views
Skip to first unread message

gimiv

unread,
Jun 29, 2006, 6:34:46 PM6/29/06
to

I have 2 worksheets. One named Daily, one named weekly. The A columns of
both sheets have dates. Daily has every business day (including
holidays) and weekly has every Friday(including holidays). The B
columns contain volume that coorelates with the dates. What I would
like to do is take the Average values between 2 Fridays and put them
into the weekly worksheet. I had success with the first week because I
was only searching on one criteria, but when I tried to search between
dates, no luck. This is the formula that worked for the first cell:
{=AVERAGE(IF(Daily!$A$4:$A$604<=Weekly!A4,Daily!$B$4:$B$604))}


Here is the formula I attempted the DID NOT work in calculating the
daily average of one weeks voume.
{=AVERAGE(IF(AND(Daily!$A$4:$A$604<=Weekly!A5,Daily!$A$4:$A$604>Weekly!A4),Daily!$B$4:$B$604))}

Any ideas? FYI, I want to skip the blank cells(holidays) in my
calculations.

Thanks,

Gimi


--
gimiv
------------------------------------------------------------------------
gimiv's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=35726
View this thread: http://www.excelforum.com/showthread.php?threadid=557138

Domenic

unread,
Jun 29, 2006, 7:03:04 PM6/29/06
to
Try...

=AVERAGE(IF(Daily!$A$4:$A$604>Weekly!A4,IF(Daily!$A$4:$A$604
<=Weekly!A5,Daily!$B$4:$B$604)))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article <gimiv.2a6qgb_1...@excelforum-nospam.com>,

gimiv

unread,
Jun 30, 2006, 10:06:43 AM6/30/06
to

That worked, excellent. Thanks Domenic. One last thing, how do I get it
to ignore blank cells (holidays)

Domenic

unread,
Jun 30, 2006, 10:27:18 AM6/30/06
to
Try...

=AVERAGE(IF(Daily!$A$4:$A$604>Weekly!A4,IF(Daily!$A$4:$A$604
<=Weekly!A5,IF(Daily!$B$4:$B$604<>"",Daily!$B$4:$B$604))))

...confirmed with CONTROL+SHIFT+ENTER.

In article <gimiv.2a7xqn_1...@excelforum-nospam.com>,

gimiv

unread,
Jun 30, 2006, 12:15:55 PM6/30/06
to

It worked. Thanks again. One more thing. Is there a way to set it so
that if there are noe values to calculate, you don't get the #DIV/0!
error?

Domenic

unread,
Jun 30, 2006, 12:50:26 PM6/30/06
to
Sure, try the following...

Insert > Name > Define

Name: BigNum

Refers to: =9.99999999999999E+307

Click Ok

Then try...

=LOOKUP(BigNum,CHOOSE({1,2},0,AVERAGE(IF(Daily!$A$4:$A$604>Weekly!A4,IF(D
aily!$A$4:$A$604<=Weekly!A5,IF(Daily!$B$4:$B$604<>"",Daily!$B$4:$B$604)))
)))

...confirmed with CONTROL+SHIFT+ENTER. The formula will return 0
instead of #DIV/0!. If you'd like the cell to be blank, you can custom
format the cell...

Format > Cell > Number > Custom > Type: [=0]""

Hope this helps!

In article <gimiv.2a83rb_1...@excelforum-nospam.com>,

0 new messages