Can Averageifs work with multiple criteria in the same column range?

[The French Viking]

Hi,

I've gained a ton of help from searching the forums here, but I can't find one on this pesky problem that I've narrowed down in a large formula:

I'm using the syntax Averageifs to calculate the average months of service in 'Data"!Q:Q based on two or more criteria in column 'Data'!O:O. I want to average the months of service for those who left the company due to "Unknown" and "ReturntoSchool."

Here's the part of a large formula that is returning #DIV/0!:

=AVERAGEIFS('Data'!Q:Q,'Data'!O:O,"Unknown",'Data'!O:O,"ReturntoSchool")

Again, this is part of a large formula with other criteria, but if I use more than one criteria for the same range I get the #DIV/0! error message. Have I hit a road block?

Thanks for reading and helping!

 

[T. Valko]

Hi,

I've gained a ton of help from searching the forums here, but I can't find one on this pesky problem that I've narrowed down in a large formula:

I'm using the syntax Averageifs to calculate the average months of service in 'Data"!Q:Q based on two or more criteria in column 'Data'!O:O. I want to average the months of service for those who left the company due to "Unknown" and "ReturntoSchool."

Here's the part of a large formula that is returning #DIV/0!:

=AVERAGEIFS('Data'!Q:Q,'Data'!O:O,"Unknown",'Data'!O:O,"ReturntoSchool")

Again, this is part of a large formula with other criteria, but if I use more than one criteria for the same range I get the #DIV/0! error message. Have I hit a road block?

Thanks for reading and helping!

Try this array formula**.

Use cells to hold the criteria:

• S2 = Unknown
• T2 = ReturntoSchool

Then, this array formula**:

=AVERAGE(IF((O2:O20=S2)+(O2:O20=T2),Q2:Q20))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[Aladin Akyurek]

You need to switch to something like...

Control+shift+enter, not just enter:

=AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,{"Unknown","ReturntoSchool"},0)),
     Data!Q2:Q2000))

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
    AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,{"Unknown","ReturntoSchool"},0)),
      Data!Q2:Q2000))))

The latter avoids #DIV/0! when there is no match...

 

[The French Viking]

Awesome! It works! Thanks so much!

 

[T. Valko]

=LOOKUP(9.99999999999999E+307,CHOOSE({1,2},0,
AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,{"Unknown","ReturntoSchool"},0)),
Data!Q2:Q2000))))

The latter avoids #DIV/0! when there is no match...

Since they're using Excel 2007 or later...

S2 = Unknown
T2 = ReturntoSchool

=IFERROR(AVERAGE(IF(ISNUMBER(MATCH(Data!O2:O2000,S2:T2,0)),Data!Q2:Q2000)),0)

 

[Hanneske4]

I have a similar problem.
Wat needs to happen is that in the Columns C the program needs to calculate the average of all the values of F IF the number in E falls within the interval in Columns A and B.
e.g. in C2 it needs to calculate the average of F2-F6 since E2-E6 is equal to, or between 140 and 142 (Interval in cells A-B).
So I need a formula you can write in one cell we can just drag down and preferably also to the right so I can do the same thing for D. Since there isn't always the same amount of cells that need to be averaged since some numbers are missing in the dataset it needs to be something like AVERAGEIFS with as criteria that E2-E701 match the interval between cell A and B. I tried for a long time but something always went wrong. The formula should give the same results as what I did manually in Column C if all is well.
Thanks!

[/URL][/IMG]

 

[Aladin Akyurek]

I have a similar problem.
...[/IMG]

Could you post this sample in a form directly readable into Excel cells? A method that allows for doing that is: Borders-Copy-Paste.

 

[Hanneske4]

Could you post this sample in a form directly readable into Excel cells? A method that allows for doing that is: Borders-Copy-Paste.

Thank your or the quick reply. I made a small example sheet with some extra explanation and examples.
http://we.tl/PcYwYGvw4n

 

[Aladin Akyurek]

Thank your or the quick reply. I made a small example sheet with some extra explanation and examples.
http://we.tl/PcYwYGvw4n

The site doesn't give direct access to your file.

Select A1:G10.
Paste it here.
Click the Go Advanced button.
Choose the icon for Table Properties.
Choose for Full Grid.
Click Ok.

 

[Hanneske4]

[TABLE="width: 528"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Mean Longitude[/TD]
[TD]Mean Latitude[/TD]
[TD][/TD]
[TD]Longitude[/TD]
[TD]Latitude[/TD]
[/TR]
[TR]
[TD]140[/TD]
[TD]142[/TD]
[TD="align: right"]16,10030737[/TD]
[TD="align: right"]54,82012341[/TD]
[TD="align: right"]140[/TD]
[TD="align: right"]17,13466296[/TD]
[TD="align: right"]55,17142108[/TD]
[/TR]
[TR]
[TD]143[/TD]
[TD]145[/TD]
[TD="align: right"]16,5365466[/TD]
[TD="align: right"]55,29002006[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]16,89191226[/TD]
[TD="align: right"]55,10606039[/TD]
[/TR]
[TR]
[TD]146[/TD]
[TD]148[/TD]
[TD="align: right"]16,2789456[/TD]
[TD="align: right"]55,27645318[/TD]
[TD="align: right"]141[/TD]
[TD="align: right"]16,14974548[/TD]
[TD="align: right"]54,66997415[/TD]
[/TR]
[TR]
[TD]149[/TD]
[TD]151[/TD]
[TD="align: right"]15,12252352[/TD]
[TD="align: right"]56,23009391[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]15,65812714[/TD]
[TD="align: right"]54,85587244[/TD]
[/TR]
[TR]
[TD]152[/TD]
[TD]154[/TD]
[TD="align: right"]15,19057957[/TD]
[TD="align: right"]55,58707761[/TD]
[TD="align: right"]142[/TD]
[TD="align: right"]14,66708901[/TD]
[TD="align: right"]54,29728899[/TD]
[/TR]
[TR]
[TD]155[/TD]
[TD]157[/TD]
[TD="align: right"]16,56289113[/TD]
[TD="align: right"]55,87980574[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]13,67660385[/TD]
[TD="align: right"]54,61272845[/TD]
[/TR]
[TR]
[TD]158[/TD]
[TD]160[/TD]
[TD="align: right"]15,95730094[/TD]
[TD="align: right"]56,67518008[/TD]
[TD="align: right"]143[/TD]
[TD="align: right"]15,18652462[/TD]
[TD="align: right"]55,28060606[/TD]
[/TR]
[TR]
[TD]161[/TD]
[TD]163[/TD]
[TD="align: right"]14,83969386[/TD]
[TD="align: right"]54,97488493[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]14,19712386[/TD]
[TD="align: right"]55,563944[/TD]
[/TR]
[TR]
[TD]164[/TD]
[TD]166[/TD]
[TD="align: right"]16,11058315[/TD]
[TD="align: right"]55,14740841[/TD]
[TD="align: right"]144[/TD]
[TD="align: right"]16,95802542[/TD]
[TD="align: right"]56,5602613[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]19,96942169[/TD]
[TD="align: right"]55,11727977[/TD]
[/TR]
[TR]
[TD="align: left"][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]145[/TD]
[TD="align: right"]19,23158014[/TD]
[TD="align: right"]54,60530077[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]18,24427685[/TD]
[TD="align: right"]55,0149688[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]146[/TD]
[TD="align: right"]16,50754506[/TD]
[TD="align: right"]54,0293024[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]14,02138499[/TD]
[TD="align: right"]55,03098529[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]147[/TD]
[TD="align: right"]16,78531648[/TD]
[TD="align: right"]56,14393496[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]16,7999514[/TD]
[TD="align: right"]56,09749369[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]148[/TD]
[TD="align: right"]15,3151988[/TD]
[TD="align: right"]55,34203395[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]13,8309314[/TD]
[TD="align: right"]55,90734834[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]149[/TD]
[TD="align: right"]15,09689313[/TD]
[TD="align: right"]56,25215541[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]15,61338108[/TD]
[TD="align: right"]55,9234319[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]150[/TD]
[TD="align: right"]16,38029808[/TD]
[TD="align: right"]56,07915109[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]14,64790435[/TD]
[TD="align: right"]56,58475237[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]151[/TD]
[TD="align: right"]15,16573305[/TD]
[TD="align: right"]56,63372436[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]16,43391375[/TD]
[TD="align: right"]56,0599321[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]152[/TD]
[TD="align: right"]15,95269644[/TD]
[TD="align: right"]55,84764075[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]17,72165454[/TD]
[TD="align: right"]55,12795349[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]153[/TD]
[TD="align: right"]15,241478[/TD]
[TD="align: right"]54,05951939[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]11,26187798[/TD]
[TD="align: right"]55,65715182[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]154[/TD]
[TD="align: right"]14,53185671[/TD]
[TD="align: right"]56,77026812[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]16,8023176[/TD]
[TD="align: right"]55,97514216[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]155[/TD]
[TD="align: right"]16,32346466[/TD]
[TD="align: right"]55,78446933[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]158[/TD]
[TD="align: right"]15,95730094[/TD]
[TD="align: right"]56,67518008[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]14,82731592[/TD]
[TD="align: right"]55,00634705[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]161[/TD]
[TD="align: right"]14,8520718[/TD]
[TD="align: right"]54,94342282[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]166[/TD]
[TD="align: right"]16,11058315[/TD]
[TD="align: right"]55,14740841[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]18,88689337[/TD]
[TD="align: right"]54,00337413[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]167[/TD]
[TD="align: right"]18,16383071[/TD]
[TD="align: right"]53,67407628[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]14,69127675[/TD]
[TD="align: right"]54,90779029[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]168[/TD]
[TD="align: right"]16,71798309[/TD]
[TD="align: right"]55,65738794[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]17,49494731[/TD]
[TD="align: right"]55,39443055[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]169[/TD]
[TD="align: right"]17,27212113[/TD]
[TD="align: right"]55,3803495[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]15,29960629[/TD]
[TD="align: right"]55,93358903[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]170[/TD]
[TD="align: right"]13,82705111[/TD]
[TD="align: right"]55,44222619[/TD]
[/TR]
</tbody>[/TABLE]

So IF the value in column E matches the values in Column A and B or everything in between (140,141,142 for the first example.) THAN: Include the value in the Cell in Column F in the average calculation in column C.
C2=Average(F2:F6)

The reason I don't just always calculate 6 values is because some data is missing (see the one marked in bold) In that case it can just take an average of as many data points as are still there from the interval shown in columns A and B.