Forum Discussion
List Generate Average as record;
I'm not sure what you are asking here.
Clearly in the code z and y are always the same.
But what is this? It makes no sense. Where are you getting this from?
z / 2 <> z = y/2
You can't say a thing (z/2) is not equal (or equal) to another thing (z=y/2).
You can't have an assignment on one side of a logical test like this
Again not sure how esle to explain this other than how I have.
z / 2 <> z = y/2 makes no sense mathematically and I don't know what you are trying to do there.
Phil
Hi Dicken
This is not the correct way to calculate the average
avg = ( [avg ] + alist {x -1} ) / x
[avg] already contains the average of the previous x-1 values, not the sum of those values.
You can't just add the next value to [avg] then divide by x
The correct average calculation is either
avg = ([asum] + alist{x-1}) / xor
avg = asum / x
Regards
Phil
i know it does not work the quesiton was why;
if ;each [x] < List.Count( alist) , each [ x = [x] + 1, asum = [asum] + alist {x-1} , avg = [avg] + alist {x-1} ] )here asum and avg = same result and x = same result why
avg = ( [avg] + alist {x-1} ) / x , avg2 = asum /x ] )does avg not work and avg2 work, as before divtins asum = avg ?
Maybe this table helps illustrate what is happening here.
Avg1 is [avg] + alist{x-1}
Avg2 is ([avg] + alist{x-1)/x)
let Query2 = let alist = List.Numbers( 1, 20 ,1 ) in List.Generate( ()=> [ x = 1 , asum = alist {0} ,avg1 = alist {0}, avg2 = alist {0}] , each [x] < List.Count( alist) , each [ x = [x] + 1, previousAvg1 = [avg1], previousAvg2 = [avg2], asum = [asum] + alist {x-1} , avg1 = ( [avg1] + alist {x -1} ), avg2 = ( ([avg2] + alist{x-1})/x ) ] ), #"Converted to Table" = Table.FromList(Query2, Splitter.SplitByNothing(), null, null, ExtraValues.Error), #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"x", "previousAvg1", "previousAvg2", "asum", "avg1", "avg2"}, {"x", "previousAvg1", "previousAvg2", "asum", "avg1", "avg2"}) in #"Expanded Column1"I did explain why it doesn't work 🙂
Let's say you have a list of 3 numbers {2 , 3, 4}
Start with x = 1
Sum = 2
Correct Avg = 2/1 = 2
Your avg = 2/1 = 2
x = 2
Sum = 2 + 3 = 5
Correct Avg = (2 + 3) / 2 = 2.5
Your avg = ([Prev Avg] 2 + 3 ) / 2 = 2.5
All good so far.
x = 3
Sum = 5 + 4 = 9
Correct Avg = (2 + 3 + 4) / 3 = 3
Your avg = ([Prev Avg] 2 .5 + 3 ) / 3 = 1.83333
The way your code was calculating avg is taking the avg from the previous step and adding the next value then dividing by x, which is incorrect
Regards
Phil
ok but you state ; Your avg = ([Prev Avg] 2 .5 + 3 ) / 3 = 1.83333 ,
but if you don't do the divsion it's the correct result example
