Conditional AVERAGEIFS
 

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

Hi Chris,

One way would be to use an array formula.

F G H I J K
2 Year xa xb xc xd
3 1 1.23 1.55
4 2 1.75
5 3 1.90 2.00
6 4 2.20 1.40 2.10

=IF(ISERROR(AVERAGE(IF(Year=$G3,IF(Site=H$2,Value) ))),"",AVERAGE(IF(Year=$G3,IF(Site=H$2,Value))))

In each cell enter the formula above, then finalize it using the CTRL SHIFT
ENTER key combination to make it an array formula.

The If part just checks for divsion by zero errors which occur when there is
no data for that combination of criteria.

Mike

"Chris" wrote:

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

The data looks something more like what is below. I only want to average
values in year 1 at sites XH, XI, XJ, and XK (so data 2.1, 2.2, 4.1, and 1.5).

A B C
Year Site Data
1 1 XA 1.7
2 1 XB 1.6
3 1 XH 2.1
4 1 XI 2.2
5 1 XJ 4.1
6 1 XK 1.5
7 2 XA 4.5
8 2 XB 2.3
9 2 XH 2.2
10 2 XI 1.1
11 2 XJ 1.3
12 2 XK 1.5


"mikebres" wrote:

Hi Chris,

One way would be to use an array formula.

F G H I J K
2 Year xa xb xc xd
3 1 1.23 1.55
4 2 1.75
5 3 1.90 2.00
6 4 2.20 1.40 2.10

=IF(ISERROR(AVERAGE(IF(Year=$G3,IF(Site=H$2,Value) ))),"",AVERAGE(IF(Year=$G3,IF(Site=H$2,Value))))

In each cell enter the formula above, then finalize it using the CTRL SHIFT
ENTER key combination to make it an array formula.

The If part just checks for divsion by zero errors which occur when there is
no data for that combination of criteria.

Mike

"Chris" wrote:

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

Hi,

Try this array formula:

=AVERAGE(IF((A2:A13=1)*(B2:B13={"XB","XJ"})0,(A2: A13=1)*(B2:B13={"XB","XJ"})*C2:C13))

By array I mean you need to press Shift+Ctrl+Enter to enter it not Enter.
Adjust as needed. If you do not enter it as an array you will get #DIV/0.



--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Chris" wrote:

The data looks something more like what is below. I only want to average
values in year 1 at sites XH, XI, XJ, and XK (so data 2.1, 2.2, 4.1, and 1.5).

A B C
Year Site Data
1 1 XA 1.7
2 1 XB 1.6
3 1 XH 2.1
4 1 XI 2.2
5 1 XJ 4.1
6 1 XK 1.5
7 2 XA 4.5
8 2 XB 2.3
9 2 XH 2.2
10 2 XI 1.1
11 2 XJ 1.3
12 2 XK 1.5


"mikebres" wrote:

Hi Chris,

One way would be to use an array formula.

F G H I J K
2 Year xa xb xc xd
3 1 1.23 1.55
4 2 1.75
5 3 1.90 2.00
6 4 2.20 1.40 2.10

=IF(ISERROR(AVERAGE(IF(Year=$G3,IF(Site=H$2,Value) ))),"",AVERAGE(IF(Year=$G3,IF(Site=H$2,Value))))

In each cell enter the formula above, then finalize it using the CTRL SHIFT
ENTER key combination to make it an array formula.

The If part just checks for divsion by zero errors which occur when there is
no data for that combination of criteria.

Mike

"Chris" wrote:

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

Okay, then try this array formula

=AVERAGE(IF(Year=1,IF((Site={"XH","XI","XJ","XK"}) ,Data)))

Mike

"Chris" wrote:

The data looks something more like what is below. I only want to average
values in year 1 at sites XH, XI, XJ, and XK (so data 2.1, 2.2, 4.1, and 1.5).

A B C
Year Site Data
1 1 XA 1.7
2 1 XB 1.6
3 1 XH 2.1
4 1 XI 2.2
5 1 XJ 4.1
6 1 XK 1.5
7 2 XA 4.5
8 2 XB 2.3
9 2 XH 2.2
10 2 XI 1.1
11 2 XJ 1.3
12 2 XK 1.5


"mikebres" wrote:

Hi Chris,

One way would be to use an array formula.

F G H I J K
2 Year xa xb xc xd
3 1 1.23 1.55
4 2 1.75
5 3 1.90 2.00
6 4 2.20 1.40 2.10

=IF(ISERROR(AVERAGE(IF(Year=$G3,IF(Site=H$2,Value) ))),"",AVERAGE(IF(Year=$G3,IF(Site=H$2,Value))))

In each cell enter the formula above, then finalize it using the CTRL SHIFT
ENTER key combination to make it an array formula.

The If part just checks for divsion by zero errors which occur when there is
no data for that combination of criteria.

Mike

"Chris" wrote:

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

I just realized I have been assuming you wanted a general solution. Is it
possible you just want to grab some values and see the average of them? If
so you can use the information on the status bar at the bottom. Hightlight
the cells you want to average, RMB click on the SUM in the status bar and
change it to Average.

"mikebres" wrote:

Okay, then try this array formula

=AVERAGE(IF(Year=1,IF((Site={"XH","XI","XJ","XK"}) ,Data)))

Mike

"Chris" wrote:

The data looks something more like what is below. I only want to average
values in year 1 at sites XH, XI, XJ, and XK (so data 2.1, 2.2, 4.1, and 1.5).

A B C
Year Site Data
1 1 XA 1.7
2 1 XB 1.6
3 1 XH 2.1
4 1 XI 2.2
5 1 XJ 4.1
6 1 XK 1.5
7 2 XA 4.5
8 2 XB 2.3
9 2 XH 2.2
10 2 XI 1.1
11 2 XJ 1.3
12 2 XK 1.5


"mikebres" wrote:

Hi Chris,

One way would be to use an array formula.

F G H I J K
2 Year xa xb xc xd
3 1 1.23 1.55
4 2 1.75
5 3 1.90 2.00
6 4 2.20 1.40 2.10

=IF(ISERROR(AVERAGE(IF(Year=$G3,IF(Site=H$2,Value) ))),"",AVERAGE(IF(Year=$G3,IF(Site=H$2,Value))))

In each cell enter the formula above, then finalize it using the CTRL SHIFT
ENTER key combination to make it an array formula.

The If part just checks for divsion by zero errors which occur when there is
no data for that combination of criteria.

Mike

"Chris" wrote:

I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI, XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.

Conditional AVERAGEIFS
 

Try this array formula** :

List the sites in a range of cells:

H1 = XH
H2 = XI
H3 = XJ
H4 = XK

=AVERAGE(IF((A2:A100=1)*(ISNUMBER(MATCH(B2:B100,H1 :H4,0))),C2:C100))

You must be using Excel 2007. I would not use entire columns as range
references unless you absolutely must.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"Chris" wrote in message
...
I have three columns of data, one is years (e.g., 1, 2, 3, 4, etc), one is
sites (e.g., XA, XB, XC, XD, etc), and the other is the values I would
like
to average (e.g., 1.2, 1.6, 1.5, 2.1, etc). I have attempted to use
criteria
with AVERAGEIFS to average only those values in year 1 at sites XH, XI,
XJ,
XK. Here is the function I am using:
=AVERAGEIFS(Sheet3!H:H,Sheet3!C:C,"=1",Sheet3!D:D, "XH",Sheet3!D:D,"XI",Sheet3!D:D,"XJ",Sheet3!D:D,"X K")

However, it returns the #DIV/0! error. What is the appropriate function
for
average only the data in year 1 at sites XH, XI, XJ, XK?

Thank you.