13 Replies Latest reply on Oct 10, 2018 2:00 AM by Eveline Vlassenroot

# Wrong averages are being calculated, what could be the problem?

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!!!!

• ###### 1. Re: Wrong averages are being calculated, what could be the problem?

Hi Eveline,

Not exactly sure, but you mean this?

Thanks,

Shin

• ###### 2. Re: Wrong averages are being calculated, what could be the problem?

Following up.

If the problem is solved, please mark my answer as Correct / Helped to close the thread, not from inbox but from original post.

Thanks,

Shin

• ###### 3. Re: Wrong averages are being calculated, what could be the problem?

Hi Shinichiro,

This is not what I meant.

I mean that this table with average calculations is wrong: (the numbers in the grey circles should be replaces by the numbers shown in the table below)

The right numbers are here: (so no percentages for the calculated average on the axis on top but numbers

• ###### 5. Re: Wrong averages are being calculated, what could be the problem?

Hi Eveline.

Unfortunately, I don't understand how these numbers are related to Gantt view from data structure perspective..

Could you attach original excel file? (Multiple files?)

Thanks,

Shin

• ###### 6. Re: Wrong averages are being calculated, what could be the problem?

Actually the text you see in the graphs is the alias for the following codes:

Dient afgestemd te worden met een breed en voldoende divers publiek (proces_1_1)

Er dient een consensus te zijn / we dienen gezamenlijk tot een vocabularium te komen (proces_1_2)

Er moeten de nodige kwaliteitschecks worden uitgevoerd op de onderliggende specificaties (proces_1_3)

Er dienen voldoende werkende toepassingen aanwezig te zijn (proces_1_4)

Elke standaard moet hetzelfde erkenningsproces doorlopen hebben (proces_1_5)

Sommige standaarden kunnen as such bekrachtigd worden omdat ze al dermate gebruikt worden (proces_1_6)

Standaarden waarvan tijdens de publieke review periode blijkt dat ze niet goed werken ook al zijn er reeds implementatie, moeten opnieuw de procedure doorlopen (proces_1_7)

Het is belangrijk dat er een overzicht is van de status van de verschillende kandidaat-standaarden om overlap in de werkgroepen te vermijden (proces_1_8)

Hope this makes a bit more sense!

• ###### 7. Re: Wrong averages are being calculated, what could be the problem?

Nop,

I ma asking original data, because you used Pivot at least, but structure is not aligned with pivot name/fields.

Without seeing Very original data, I cannot investigate the approach.

Shin

• ###### 8. Re: Wrong averages are being calculated, what could be the problem?

Okay!

• ###### 9. Re: Wrong averages are being calculated, what could be the problem?

Shinichiro Murakami Do you have any idea of these wrong numbers?

• ###### 10. Re: Wrong averages are being calculated, what could be the problem?

I tried, but gave up.

I mean your data is structured pretty much complicated way because of the nature of survey method.

I recommend that you clean the data more deeply first, Single Pivot is not enough I think.

I don't know anything about data meaning so, it is impossible to analyze everything and re-build from the scratch.

Anyway just speculating the better way to use Union than Pivot as a directions.

The key is to mange 1~8 as common dimensions with using "Table name"..

Several Formula example here.

I could not replicate same value on Avg, not the Gantt because it's too much different data structure and also your data did not have sd_2, sd_3, V1~V10 for example.

Anyways as conclusion, as the forum activity,it is way beyond my threshold and I stop to investigate any more.

Thanks,

Shin

• ###### 11. Re: Wrong averages are being calculated, what could be the problem?

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.

1 of 1 people found this helpful
• ###### 12. Re: Wrong averages are being calculated, what could be the problem?

What a thorough efforts or good eye ...........

Surprising.

Shin

• ###### 13. Re: Wrong averages are being calculated, what could be the problem?

swaroop.gantela

Thank you so much!!!!

It works now