Excel incorrectly calculating the average of numbers when using division

Question

I am trying to work out the average of scores for the male artists. When I tried using the AVERAGEIFs function it returned a #VALUE error and therefore I have created a table to work out the average manually. The expected score is 3.9 however when I use my table which adds up all the scores and then the number of scores which are not blank and divides the total score by number of scores the answer is 3.4.

Could you please advise on why this would be and if there is a way to calculate the average of male artists?

Here is an image of my spreadsheet:

It is a lot easier for us if you post data rather than a screenshot. I'm not waiting for jobs like copying your data from a screenshot into a spreadsheet. — jkpieterse
– jkpieterse, Commented Feb 23, 2018 at 11:06
If you make a normal Average and you make it per column, thus it would be AVG(Q1:Q5), and then make it AVERAGEIFS() with that column and the Artist and Male as conditions, would it be what you expect? — Vityata
– Vityata, Commented Feb 23, 2018 at 11:06
Really @jkpieterse..? Personally I live for the opportunity to waste my time retyping someone's crappy data just so I have a chance at helping them. It's esspecially exciting when I get to guess at the formula that 'didn't work' 'cause they didn't bother to include it. — user4039065
– user4039065, Commented Feb 23, 2018 at 11:17

Vityata · Accepted Answer · 2018-02-23 11:15:20Z

0

To my knowledge, AVERAGEIFS() works on a single column / single row. Thus, you need a helping row, showing you the AVERAGE per person, and then taking the average, with conditions such as Gender and Job.

I have 6.5 with this formula:

=AVERAGEIFS(F2:F5 F2:F5,B2:B5,B2,A2:A5,A2)

It simply gives the average of 2 and 11, as they are the only 2 male artists in my sample.

MSDN AVERAGEIFS

If you do not like the idea of additional column, then making a PIVOT table would be a good solution.

answered Feb 23, 2018 at 11:15

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel incorrectly calculating the average of numbers when using division

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related