Hi everyone,
I have attached my workbook but there are some wrong averages being calculated.
There is Erkenning_final_wrong where in the circles the average of proces_1_1, proces_1_2, proces_1_3, proces_1_4, proces_1_5, proces_1_6, proces_1_7, proces_1_8 should be placed.
The number in there atm is not correct, the correct number is in tab Erkenning_average_correct.
How can I fix this?
Thank you!!!!
Eveline,
This is indeed a very complex data set.
I think the reason for the discrepancy was in the CASE statement for Response.
They strings were not correctly matching in spelling or in order.
(Of course, I make a typo when talking about mismatched spelling!
I can commiserate, it is easy to do. Let me try again...)
The strings were not correctly matching in spelling or in order.
Because they were not matching, many of the Response values were coming back as null.
I changed it to:
case [Pivot Field Values]
when "Helemaal niet mee eens" then 1
when "Niet mee eens" then 2
when "Enigszins mee oneens" then 3
when "Noch eens noch oneens" then 4
when "Enigszins mee eens" then 5
when "Mee eens" then 6
when "Helemaal mee eens" then 7
end
I would recommend when writing the case the statement to
put the desired Pivot Field Values on a worksheet,
right click and select "Edit Alias"
then copy that string into the case statement.
Please see workbook v18.1 attached in the Forum Thread.
