Help with Averaging Based on Cell Conditions

[cionnaith]

Hello folks!

I am trying to calculate averages across an array of cells for one of six conditions in those cells.

I have five cells (i.e. G11:G15) where each cell can have one of six conditions (e.g. morning, afternoon, ... ) that are being read from another worksheet. In another column I have percentages associated with those five cells (i.e. I11:I15). In a third array of cells (i.e. I19:I24) I need to calculate the average for each of the conditions as they are present across those five cells. For example,

G I
11 morning 11 50%
12 morning 12 75%
13 afternoon 13 100%
14 afternoon 14 50%
15

I
19 average of all of the percents in I when G contains "morning"
20 average of all of the percents in I when G contains "afternoon"
21
22
23
24

etc..

The only other thing I need is to have the calculations in the I19:I24 cells return a blank cell if there are no entries in the others.

I hope this makes sense. Thanks so much in advance.

 

[lenze]

Why not just use a Pivot Table??
lenze

 

[cionnaith]

Why not just use a Pivot Table??
lenze

Thanks for the reply. Is there a way to do it using a formula instead of a pivot table?

 

[Aladin Akyurek]

Hello folks!

I am trying to calculate averages across an array of cells for one of six conditions in those cells.

I have five cells (i.e. G11:G15) where each cell can have one of six conditions (e.g. morning, afternoon, ... ) that are being read from another worksheet. In another column I have percentages associated with those five cells (i.e. I11:I15). In a third array of cells (i.e. I19:I24) I need to calculate the average for each of the conditions as they are present across those five cells. For example,

G I
11 morning 11 50%
12 morning 12 75%
13 afternoon 13 100%
14 afternoon 14 50%
15

I
19 average of all of the percents in I when G contains "morning"
20 average of all of the percents in I when G contains "afternoon"
21
22
23
24

etc..

The only other thing I need is to have the calculations in the I19:I24 cells return a blank cell if there are no entries in the others.

I hope this makes sense. Thanks so much in advance.

Are you wanting to average H11:H14? If so:

J19: morning
J20: afternoon

I19:

=IF(COUNTIF($G$11:$G$14,J19),
    SUMIF($G$11:$G$14,J19,$H$11:$H$14)/COUNTIF($G$11:$G$14,J19),
    "")

On Excel 2007 or later:

=IF(COUNTIF($G$11:$G$14,J19),
    AVERAGEIF($G$11:$G$14,J19,$H$11:$H$14),
    "")

 

[cionnaith]

Are you wanting to average H11:H14? If so:

J19: morning
J20: afternoon

I19:

=IF(COUNTIF($G$11:$G$14,J19),
    SUMIF($G$11:$G$14,J19,$H$11:$H$14)/COUNTIF($G$11:$G$14,J19),
    "")

On Excel 2007 or later:

=IF(COUNTIF($G$11:$G$14,J19),
    AVERAGEIF($G$11:$G$14,J19,$H$11:$H$14),
    "")

No, H is an empty column. Only need to average across I's based on the conditions in G's

 

[lenze]

In 2007, you can use

=AVERAGEIFS(I11:I15,G11:G15,"morning")

lenze

 

[Aladin Akyurek]

No, H is an empty column. Only need to average across I's based on the conditions in G's

Substitute $I$11:$I$14 for $H$11:$H$14...

 

[T. Valko]

Hello folks!

I am trying to calculate averages across an array of cells for one of six conditions in those cells.

I have five cells (i.e. G11:G15) where each cell can have one of six conditions (e.g. morning, afternoon, ... ) that are being read from another worksheet. In another column I have percentages associated with those five cells (i.e. I11:I15). In a third array of cells (i.e. I19:I24) I need to calculate the average for each of the conditions as they are present across those five cells. For example,

G I
11 morning 11 50%
12 morning 12 75%
13 afternoon 13 100%
14 afternoon 14 50%
15

I
19 average of all of the percents in I when G contains "morning"
20 average of all of the percents in I when G contains "afternoon"
21
22
23
24

etc..

The only other thing I need is to have the calculations in the I19:I24 cells return a blank cell if there are no entries in the others.

I hope this makes sense. Thanks so much in advance.

What version of Excel are you using?

 

[cionnaith]

In 2007, you can use

=AVERAGEIFS(I11:I15,G11:G15,"morning")

lenze

Thanks...that works great. The only thing I need to do is make it so that it returns a blank cell instead of the DIV0 thing if cells being averaged are empty (for instance, there are no percentages in I for "morning" because there are no "morning"s in that particular array. Do I just add ,"") to the end?

Thanks again!

 

[T. Valko]

Thanks...that works great. The only thing I need to do is make it so that it returns a blank cell instead of the DIV0 thing if cells being averaged are empty (for instance, there are no percentages in I for "morning" because there are no "morning"s in that particular array. Do I just add ,"") to the end?

Thanks again!

Try it like this...

=IFERROR(AVERAGEIFS(I11:I15,G11:G15,"morning"),"")