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?

 

[hagia_sofia]

Hello, if you do not mind using a volatile function with random values maybe you could try something like:

=LET(
n,O1,
l,N1,
u,N2,
a,N3,
x,RANDARRAY(n-1,,l,u),
y,(a*(n+2)-(l+u+SUM(x))),
VSTACK(x,y))

where O1 is the number of values to be generated, N1 is the lowest value, N2 the highest, and N3 the average. The formula includes the lowest and highest values into consideration.

 

[hagia_sofia]

Apologies, I see that there is a mistake in this attempt as the last value might be out of the bounds, please ignore post #2.

 

[mrblister]

Thank you for the attempt @hagia_sofia, but regardless I don't have access to VSTACK anyway (that function is not included in Excel 2021).

 

[hagia_sofia]

You are right, my bad. I tried another one but failed again.

 

[coulombevin]

Hi mrblistter,

Her's what I would try:

=LET(lowest_value,0.5,
highest_value,1.5,
average_value,0.95,
number_of_cells,3,
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),
VALUE(TEXTSPLIT(TEXTJOIN (";",TRUE,lowest_value,SORT(lstVal),highest_value),,";")))

Bests regards,

Vincent

 

[Dave Patton]

It would help if you provided an example.
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
Goal Seek change the value in D4 to achieve 0.95 in D5

T202501a.xlsm
	C	D
2	lowest_value = 0.5	0.5
3	highest_value = 1.5	1.5
4	average_value = 0.95	0.85
5	number_of_cells = 3	0.95
6
3c

Cell Formulas
Range	Formula
D5	D5	=AVERAGE(D2:D4)

 

[coulombevin]

It would help if you provided an example.
N.B. You can post an extract of your sheet with the forum's tool named XL2BB.
Goal Seek change the value in D4 to achieve 0.95 in D5

T202501a.xlsm
	C	D
2	lowest_value = 0.5	0.5
3	highest_value = 1.5	1.5
4	average_value = 0.95	0.85
5	number_of_cells = 3	0.95
6
3c

Cell Formulas
Range	Formula
D5	D5	=AVERAGE(D2:D4)

Hi Dave,

I'm not quite sure i see what you mean. The question, if I understood it the right way, requested a formula that could be placed anywhere without any cell reference. If I was in the wrong, only replace value (eg. 0.5) with a cell reference (eg. $A$1).

Bests regards,

Vincent

 

[mrblister]

Hi mrblistter,

Her's what I would try:

=LET(lowest_value,0.5,
highest_value,1.5,
average_value,0.95,
number_of_cells,3,
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),
VALUE(TEXTSPLIT(TEXTJOIN (";",TRUE,lowest_value,SORT(lstVal),highest_value),,";")))

Bests regards,

Vincent

Thanks Vincent, but I don't have access to the TEXTSPLIT function. (And it's possible the system where the formula will be used may not have access to RANDARRAY, although I think RANDARRAY will be available)

 

[coulombevin]

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