SUMPRODUCT with conditions
=AVERAGE(IF((period=1)*(period<=3)*(location="som eplace"),percentage))
which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
"daddylonglegs" wrote in message
...
Try
=AVERAGE(IF(period_range0,IF(period_range<4,IF(lo cation_range="xxx",percent
_range))))
confirmed with CTRL+SHIFT+ENTER
this calculates average % for location "xxx" in first 3 periods (quarter
1)
Note ranges should all be the same size and not whole columns
"sahafi" wrote:
My data is like this:
Columns: (Year), (Period), (Week), (Location),(...),...,(Calculated
field %).
Each period has 4 weeks. I have about 30 locations and each location has
one
occurance of %number for each week. I need to calculate an average per
location per quarter. Quarter (Q) = 3 periods (but my data doesn't have
any
listing by quarter). I have tried sumproduct but it will calculate all
the %s
for a given location throughout the year. I only need to show the Avg %
for
each location between p1 wk1 through p3 wk4, then from p4 wk1 through p6
wk4,
and so forth.
Any direction on this is greatly appreciated.
Thanks.
--
when u change the way u look @ things, the things u look at change.
|