Formula for Distribution To Meet a Given Average

[mrblister]

Hello all!

I am in need an Excel formula, starting from a cell (e.g. N4) and copied down, where the formula considers:

• the topmost cell contains a given lowest value
• the bottom-most cell (given) contains the highest value
• the average value of all cells equals a given value.

So for example, if:

• lowest_value = 0.5
• highest_value = 1.5
• average_value = 0.95
• number_of_cells = 3

Then the formula outputs would be: N4 = 0.5, N5 = 0.85, N6 = 1.5.

This is as far as I can get. I need the formula to calculate the distribution values between lowest_value and highest_value , while maintaining average_value, over the number_of_cells (e.g. 4, 5, etc).

Can anyone assist?

 

[dermie_72]

does this do what you want. Which such a small data set, it's hard to tell if you want a running average or you want to capture the Min the max and the average.

=AVERAGEIFS(N$4:N6,N$4:N6,">="&MIN(N$4:N6),N$4:N6,"<="&MAX(N$4:N6))

 

[mrblister]

Hi mrbliste,

Does this one work for your version of excel? *I have the french version of Excel 365 so I might make some error while translating formulas*

- ENGLISH -

=LET(lowest_value,0.5,
highest_value,1.5,
average_value,0.95,
number_of_cells,5,
tempAverage,((average_value*number_of_cells)-highest_value-lowest_value)/(number_of_cells-2),
rdmTbl,RANDARRAY(number_of_cells-2,1,lowest_value+0.00001,highest_value-0.00001),
lstVal,(rdmTbl/SUM(rdmTbl))*tempAverage*(number_of_cells-2),
seq,SEQUENCE(number_of_cells),
IF(seq=1,lowest_value,IF(seq=number_of_cells,highest_value,INDEX(SORT(lstVal),seq-1))))

- FRENCH -

=LET(lowest_value;0,5;
highest_value;1,5;
average_value;0,95;
number_of_cells;5;
tempAverage;((average_value*number_of_cells)-highest_value-lowest_value)/(number_of_cells-2);
rdmTbl;TABLEAU.ALEA(number_of_cells-2;1;lowest_value+0,00001;highest_value-0,00001);
lstVal;(rdmTbl/SOMME(rdmTbl))*tempAverage*(number_of_cells-2);
seq;SEQUENCE(number_of_cells);
SI(seq=1;lowest_value;SI(seq=number_of_cells;highest_value;INDEX(TRIER(lstVal);seq-1))))

Bests regards,

Vincent

Hello Vincent,

"Je vous remercie de m'avoir aidé. Bonne année!"

Your help is greatly appreciated. The latest formula works on my computer and version of Excel, but not on the target computer. HOWEVER, I was able to modify your first formula with a substitute for TEXTPLIT:

=LET(lowest_value,0.5,
highest_value,1.5,
average_value,0.95,
number_of_cells,5,
temp_average_value,((average_value*number_of_cells)-highest_value-lowest_value)/(number_of_cells-2),
rdmTbl,MAKEARRAY(number_of_cells-2,1,LAMBDA(r,c,r*c+max(1,number_of_cells+PARAMETER))),
lst_val,(rdmTbl/SUM(rdmTbl))*temp_average_value*(number_of_cells-2),
txtjn,TEXTJOIN(";",TRUE,lowest_value,SORT(lst_val),highest_value),
VALUE(TRIM(MID(SUBSTITUTE(txtjn, ";", REPT(" ", 255)), (ROW(A1:INDEX(A:A,number_of_cells))-1)*255+1, 255))))

So I will mark your first solution as "the solution."

I also replaced RANDARRAY with a static array (because I don't want the output values to change), in the section "MAKEARRAY(number_of_cells-2,1,LAMBDA(r,c,r*c+max(1,number_of_cells+PARAMETER)))".

If you’re willing, I’d appreciate assistance with the MAKEARRAY section, specifically with the variable PARAMETER. I want the output array to have values distributed as linearly as possible (at the very least, the second value must not be less than lowest_value). Adjusting PARAMETER manually achieves this, but if lowest_value, highest_value, or average_value change, PARAMETER must be adjusted accordingly. Otherwise, the differences between values can become uneven, and some values may fall below lowest_value. I need a sub-function so that PARAMETER self-calculates so that there are no values less than lowest_value, and also (hopefully) the difference between the array values are distributed as linearly as possible.

 

[mrblister]

does this do what you want. Which such a small data set, it's hard to tell if you want a running average or you want to capture the Min the max and the average.

=AVERAGEIFS(N$4:N6,N$4:N6,">="&MIN(N$4:N6),N$4:N6,"<="&MAX(N$4:N6))

Thank you, @dermie_72, but this is not what I am looking for. The data set I gave is small, as it was just an example. I need the formula to output array values from specific inputs (see my other posts).

 

[coulombevin]

Hello Vincent,

"Je vous remercie de m'avoir aidé. Bonne année!"

Your help is greatly appreciated. The latest formula works on my computer and version of Excel, but not on the target computer. HOWEVER, I was able to modify your first formula with a substitute for TEXTPLIT:

=LET(lowest_value,0.5,
highest_value,1.5,
average_value,0.95,
number_of_cells,5,
temp_average_value,((average_value*number_of_cells)-highest_value-lowest_value)/(number_of_cells-2),
rdmTbl,MAKEARRAY(number_of_cells-2,1,LAMBDA(r,c,r*c+max(1,number_of_cells+PARAMETER))),
lst_val,(rdmTbl/SUM(rdmTbl))*temp_average_value*(number_of_cells-2),
txtjn,TEXTJOIN(";",TRUE,lowest_value,SORT(lst_val),highest_value),
VALUE(TRIM(MID(SUBSTITUTE(txtjn, ";", REPT(" ", 255)), (ROW(A1:INDEX(A:A,number_of_cells))-1)*255+1, 255))))

So I will mark your first solution as "the solution."

I also replaced RANDARRAY with a static array (because I don't want the output values to change), in the section "MAKEARRAY(number_of_cells-2,1,LAMBDA(r,c,r*c+max(1,number_of_cells+PARAMETER)))".

If you’re willing, I’d appreciate assistance with the MAKEARRAY section, specifically with the variable PARAMETER. I want the output array to have values distributed as linearly as possible (at the very least, the second value must not be less than lowest_value). Adjusting PARAMETER manually achieves this, but if lowest_value, highest_value, or average_value change, PARAMETER must be adjusted accordingly. Otherwise, the differences between values can become uneven, and some values may fall below lowest_value. I need a sub-function so that PARAMETER self-calculates so that there are no values less than lowest_value, and also (hopefully) the difference between the array values are distributed as linearly as possible.

Hi mrblister,

"Tout le plaisir est pour moi, Bonne année à vous aussi!"

I tried multiple change in the equation and I randomly found that 4 could replace parameter in your formula. The reason why is simple, *it's a mystery* .

I've tried to change average, lowest value and highest value with success, could you confirm if it fits your needs?

Bests regards,

Vincent