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.

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.

=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.

Sorry for the late reply, as I was away for a few days.
Thank you both for trying to help.

Daddylonglegs's formula didn't work. While Bob's formula worked (generated a
number), it was incorrect average. I have noticed when I expand the range for
the % field the answer changes, which telling me it is not really applying
the period conditions (=1, <=3), because regardless of the range length, it
should only average P1-3. Here's the formula:
AVERAGE(IF((B2=1)*(B2<=3)*($F$2:$F$2050=U$3),$J$2 :$J$2050))
Note: my period number formatted as text (01, 02, 03, ..) but I did test the
formula on a number formatted numbers as well.

I also found out that may be a weighted average will work better than my
current calculated field (averaging the averages). Basically I'd like to
average out the stoppage hours for any given location for each quarter. If a
location ran 35 hours, and stopped for 5 hours, then the % stoppage time is
5/40 = 12.5%. Because the run time varies for each week, the weighted average
will give us a better overall average.
Any suggestion or help is highly appreciated.
--
when u change the way u look @ things, the things u look at change.


"Bob Phillips" wrote:

=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.