AVERAGEIFS Multiple Criteria: use an Array?

[GTblearch]

I have a table with several hundred records in the following format:

A B C D

11 FUND BFY PERIOD DOLLARS
2 3737 2006 1 $18,872,113
3 3737 2007 1 $19,609,443
4 3737 2008 1 $24,275,651
5 3750 2006 1 $4,310,784
6 3750 2007 1 $4,499,430
7 3750 2008 1 $5,431,438
8 3777 2006 2 $141,946
9 3777 2007 2 $248,308
10 3777 2008 3 $372,655
11 3737 2006 3 $614,784
12 3737 2007 3 $618,104
13 3737 2008 4 $747,215
14 3750 2006 4 $126,377
15 3750 2007 1 $126,344
16 3750 2008 1 $147,272
17 3777 2006 1 $4,053
18 3777 2007 2 $6,735
19 3777 2008 2 $9,773

I have been experimenting with various combinations of arrays and the
SUM/AVERAGE/IF/AND/OR functions (but mostly with the new AVERAGEIFS) as a
means of averaging the dollars based on multiple criteria: FUND, Period, and
Budget Fiscal Year (BFY). (The data in each column are in the named ranges
FUND, BFY, PERIOD, DOLLARS.)

I can use the formula below as a regular formula, or an Array, and acheive
the same results:

=AVERAGEIFS(Dollars, FUND,"3737", Period, "2",)

Using only the FUND and Period as criteria will average all records
regardless of the BFY. They way that I understand the formula, I either can
average the records for ALL of BFYs that meet the FUND and Period criteria,
or include a third criteria in the formula that specifies a single BFY.

Is there a way to include multiple parameters for a single criteria field?
in other words: how can I average records where FUND = "3737", Period = "2",
and BFY = "2006" OR "2008" without repeating the entire formula for each BFY?

Any feedback or ideas would be greatly appreciated. Thanx.
- Brian Learch

 

[Eduardo]

Hi,
you can achieve that refering the formula to a cell where you enter the BFY,
other with Period and other with the fund #

 

[GTblearch]

Thanx, but I'm trying to use multiple parameters as input to the functions
criteria forthe BFY (e.g. 2006 and 2008, or 2007 and 2008, or 2006 and 2007.)

 

[Peggy Shepard]

Hi GTblearch,

=AVERAGEIFS(DOLLARS,FUND,"3737",BFY,">2005",BFY,"<2009",BFY,"<>2007",PERIOD,"1")

The criteria was changed because period 2 had no matches for the rest of the
criteria - and produces a division error.

Thanks,
Peggy

 

[Bob Phillips]

=SUMPRODUCT(--(Fund=3737),--(Period=1),--(ISNUMBER(MATCH(BFY,{2006,2008},0))))

 

[Ron Rosenfeld]

I have a table with several hundred records in the following format:

A B C D

11 FUND BFY PERIOD DOLLARS
2 3737 2006 1 $18,872,113
3 3737 2007 1 $19,609,443
4 3737 2008 1 $24,275,651
5 3750 2006 1 $4,310,784
6 3750 2007 1 $4,499,430
7 3750 2008 1 $5,431,438
8 3777 2006 2 $141,946
9 3777 2007 2 $248,308
10 3777 2008 3 $372,655
11 3737 2006 3 $614,784
12 3737 2007 3 $618,104
13 3737 2008 4 $747,215
14 3750 2006 4 $126,377
15 3750 2007 1 $126,344
16 3750 2008 1 $147,272
17 3777 2006 1 $4,053
18 3777 2007 2 $6,735
19 3777 2008 2 $9,773

I have been experimenting with various combinations of arrays and the
SUM/AVERAGE/IF/AND/OR functions (but mostly with the new AVERAGEIFS) as a
means of averaging the dollars based on multiple criteria: FUND, Period, and
Budget Fiscal Year (BFY). (The data in each column are in the named ranges
FUND, BFY, PERIOD, DOLLARS.)

I can use the formula below as a regular formula, or an Array, and acheive
the same results:

=AVERAGEIFS(Dollars, FUND,"3737", Period, "2",)

Using only the FUND and Period as criteria will average all records
regardless of the BFY. They way that I understand the formula, I either can
average the records for ALL of BFYs that meet the FUND and Period criteria,
or include a third criteria in the formula that specifies a single BFY.

Is there a way to include multiple parameters for a single criteria field?
in other words: how can I average records where FUND = "3737", Period = "2",
and BFY = "2006" OR "2008" without repeating the entire formula for each BFY?

Any feedback or ideas would be greatly appreciated. Thanx.
- Brian Learch

1. I don't believe you can have an "OR" criteria within the AVERAGEIFS
function.

2. Using separate averageifs will not give the same result.
2006 10
2006 10
2007 30

The average of the above is 50/3

But the sum of the averages would be 40

If you want the sum of the averages, then multiple averageifs would be
appropriate.

If you want to look at all the entries that meet your multiple criteria, and
then average them, you could use either a combination of (sumifs(...) +
sumifs(...)) / (countifs(...) + countifs(...))

or, perhaps a bit more simply:

=SUMPRODUCT(DOLLARS*(FUND=3777)*(PERIOD=2)*(BFY={2006,2007}))
/SUMPRODUCT((FUND=3777)*(PERIOD=2)*(BFY={2006,2007}))

Note that the years are entered within braces {...} as an array constant
--ron

 

[Ron Rosenfeld]

=SUMPRODUCT(DOLLARS*(FUND=3777)*(PERIOD=2)*(BFY={2006,2007}))
/SUMPRODUCT((FUND=3777)*(PERIOD=2)*(BFY={2006,2007}))

For 2006 OR 2008:

=SUMPRODUCT(DOLLARS*(FUND=3777)*(PERIOD=2)*(BFY={2006,2008}))
/SUMPRODUCT((FUND=3777)*(PERIOD=2)*(BFY={2006,2008}))
--ron

 

[GTblearch]

Thanx to all for the suggestions, I'll give them a try once I'm able
"digest" them.

- BRL