Solved Is AVERAGEIFS the correct formula to use?

June 28, 2016 at 10:47:22
Specs: Windows 64
Hi, Back in March DerbyDad03 answered a very similar question - I tried to plug in my data with the formula provided and although the formula worked for the person who posted the ? unfortunately, the formula did not work for me.

I need to get an average of the Quality Score per profile based on a few different conditions. All of the individual profile scores are in column AV. The conditions are follows:

1. To get the average of the Quality Score per profile (AV)
ONLY for
2.A specific Analyst (ie A4 Mary Sue)
3. Error Type is Regulatory (Z:Z)
AND
4. Exclude (<>) business segment BB Mass, CRE and GIB (all segments are in column G:G)


The average will be determined by dividing the (aggregated total ) of the Quality Score per profile (AV) (will be determined by the critera above 2-4) - by the number of profiles - the # of profiles will be determined by the critera above (2-4).

Is this even possible?



See More: Is AVERAGEIFS the correct formula to use?

Report •

✔ Best Answer
June 28, 2016 at 13:10:57
Look at the syntax of the formula:

AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Now look at the syntax of your formula:

=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")

What column are you trying Average?
I don't think it's Column D.

MIKE

http://www.skeptic.com/



#1
June 28, 2016 at 11:32:12
From what I can understand, it does not appear that =AVERATEIFS() is
what you need.

The syntax of the function is similar to SUMIFS
where you have one range to average and multiple criteria.

AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Post a small sample of your data using Column Letters and Row numbers
and what your expected answer should be.

MIKE

http://www.skeptic.com/


Report •

#2
June 28, 2016 at 11:50:52
     A                B                 C         

1 Susan              500	       CRE            
2 Mary               400	       GIB            

3 Mary               100	       BBMass         

4 Mary               300	       Lg Corp        

5 Mary               700	       Lg Corp        

6 Mary               800	       IBC            

I need a formula that will give me the average for A:A (Mary only) and the sum of B:B and C:C for all Buiness Segments except CRE, GIB and BBMass (C:C)

A - Analyst
B - Quality score per profle
C - Business segment



Report •

#3
June 28, 2016 at 12:13:20
OK, the =AVERAGEIFS() will work, try this:

=AVERAGEIFS(B1:B6,A1:A6,"Mary",C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")


MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

Related Solutions

#4
June 28, 2016 at 12:16:41
If you do not want to "hard code" the name into the formula,
you can reference a cell that contains the name, so the formula:

=AVERAGEIFS(B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")

will also work.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#5
June 28, 2016 at 12:40:37
Don't kill me lol - I forgot to add the column D - which I refer to in my initial question as Error Type is Regulatory (Z:Z)

That would be the last condition. I only want Regulatory

A            B         C                D        


1 Mary       400      GIB        Regulatory      

2 Mary       100      BBMass     Regulatory      

3 Mary       500      Lg Corp    Regulatory      

4 Susan      500      IBC        Regulatory      

5 Susan      500      CRE        Admi            

6 Susan      500      CRE        Regulatory      

7 Mary       200      BBMass     No Exception    

The data maybe different but the columns are the same (except the addition of D)


Report •

#6
June 28, 2016 at 12:44:57
Ok,

You see how the formula works,
you add in the final criteria and post your formula.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#7
June 28, 2016 at 12:59:47
Fair enough!

=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")

For some reason I get an error message stating that the formula contains an error?


Report •

#8
June 28, 2016 at 13:10:57
✔ Best Answer
Look at the syntax of the formula:

AVERAGEIFS( average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ... )

Now look at the syntax of your formula:

=AVERAGEIFS(D1:D6,"=Regulatory",B1:B6,A1:A6,A2,C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass")

What column are you trying Average?
I don't think it's Column D.

MIKE

http://www.skeptic.com/


Report •

#9
June 28, 2016 at 13:42:55
And you are correct! I am trying to average the data in column B.

=AVERAGEIFS(B1:B6,A1:A6,A2,D1:D6,"=Regulatory",C1:C6,"<>GIB",C1:C6,"<>CRE",C1:C6,"<>BBMass)

I am pretty certain I finally got it right!


Report •

#10
June 28, 2016 at 14:32:11
You now have mastered two formulas

=SUMIFS() and =AVERAGEIFS()

Excellent.

MIKE

http://www.skeptic.com/


Report •

#11
June 28, 2016 at 16:04:27
Yessssss!! I appreciate your help & Thank you!!!!!

Report •

Ask Question