Search code examples
google-sheetsmathrandom

Assigning integer weights in Google Sheets

Given ’n’ random integer "values", how do we assign integer "weights" to each of these random "values" such that the weighted mean is also an integer.

Generating random integer "values" is not an issue. E.g. n= 4, yields {17, 9, 13, 8}.

As of now the "weights" are generated randbetween(1,10) each, for example {10,8,4,4} resulting in a non-integer mean of 12 7/13.

How do we generate the "weights" so that the mean is also an integer?

Solution

  • A brute-force search like this?

    =ArrayFormula(let(values,A2:A5,weights,lambda(self,v,let(w,int(randarray(rows(values))*10)+1,if(mod(sumproduct(w,v)/sum(w),1)=0,w,self(self,v)))),weights(weights,values)))

    enter image description here

    or excluding duplicate weights, then:

    =ArrayFormula(let(values,A2:A5,weights,lambda(self,v,let(w,array_constrain(sort(sequence(10),randarray(10),1),rows(values),1),if(mod(sumproduct(w,v)/sum(w),1)=0,w,self(self,v)))),weights(weights,values)))

    If the weights didn't have to be random, then trying the smallest first would be a good strategy e.g. {1,1,1,2} if duplicates are allowed with an average of 11.