Average excluding blank cells

[ugurgulluev]

Dear all,

We are running an Employee Satisfaction Survey in our company but I am having hard time to find the average scores due to the blank cells in data cells, therefore looking for your valuable input.

I have a dataset like this:

Manager	Question 1	Question 2	Question 3	Question 4	Question 5	Question 6
Alex	4	5	2	2	6	6
Alex	6		3	4		8
Jake	5	6	7	4	1	8
Jake		7		6		4
Joe	6		5		7	8
Joe		7
Natalie	4	10	6	4	7	3
Natalie	1			6		6

Question 1 and Question 2, for example, belong to the "Leadership index" group. Therefore, in order to find the average score per manager for the "Leadership index", I created the following measure:

Leadership index = (AVERAGE(Sheet1[Question 1])+AVERAGE(Sheet1[Question 2])/2)

However, it results in incorrect scores as following, I believe because the measure does not exclude the blank cells while calculating the measure:

Manager	Leadership index
Alex	7.5
Jake	8.25
Joe	9.5
Natalie	7.5

Do you guys have any ideas on how to exclude the blank values while calcualating the measure so that I get the correct results?

Thanks for your support!

Ugur Gulluev

[Greg_Deckler · Accepted solution]

You just have a precedence issue (order of operations)

Leadership index Correct = (AVERAGE(Table10[Question 1])+AVERAGE(Table10[Question 2]))/2