Reverse Calculating an Average

[ONEIL290]

I was hoping to figure out how if I have an average number, to have excel automatically calculate the numbers in a range required to have that output.
For example if I want the average to be 50% and I have a range of 30 inputs, what would those 30 numbers need to be? Also if I manually input numbers in the range I want it to automatically adjust the calculated numbers to still satisfy the average. EX: If I want the average to be 50% and I have numbers 55%, 60%, 58%, 52%, what will the remaining 26 numbers need to be to average 50%?

 

[Cubist]

You'd only need 1 free input to achieve the desired average.

 

[ONEIL290]

You'd only need 1 free input to achieve the desired average.

What equation do I need in the cells to calculate the rest of the range?

 

[Cubist]

It doesn't matter what the first 29 inputs are so you can use random generators like RANDARRAY() for the first 29.

Book1
	A	B
1	Desired Average	50%
2	n	30
3
4	#	Example
5	1	98.3%
6	2	16.8%
7	3	90.1%
8	4	93.1%
9	5	74.4%
10	6	96.4%
11	7	50.5%
12	8	96.3%
13	9	65.7%
14	10	36.1%
15	11	33.7%
16	12	29.5%
17	13	18.0%
18	14	21.9%
19	15	88.5%
20	16	6.4%
21	17	90.6%
22	18	7.4%
23	19	36.6%
24	20	54.2%
25	21	35.5%
26	22	51.5%
27	23	20.8%
28	24	28.2%
29	25	46.5%
30	26	94.0%
31	27	98.9%
32	28	73.7%
33	29	19.7%
34	30	-73.5%
Sheet2

Cell Formulas
Range	Formula
A5:A34	A5	=SEQUENCE(B2)
B5:B33	B5	=RANDARRAY(B2-1)
B34	B34	=$B$1*B2-SUM(B5#)
Dynamic array formulas.

 

[Joe4]

Since you have not really defined any set of rules or guidelines for this, there are many different ways to do it (there is literally no limit to the number of possibilities that would work, it is infinite).

One simple way would be to calculate what the next record would have to be to get the average to where you want, and then set every single record after that equal to the average, i.e.

So cell B1 is your desired average and cells A1:A4 are your initial values.
You could use this formula in cell A5 to get the next record so that the average will be exactly your desired value:

=(B1*(COUNT(A1:A4)+1))-SUM(A1:A4)

and then just use your average for the rest of the entries, i.e. put in cell A6 and copy down to A30:

=$B$1

 

[ONEIL290]

Since you have not really defined any set of rules or guidelines for this, there are many different ways to do it (there is literally no limit to the number of possibilities that would work, it is infinite).

One simple way would be to calculate what the next record would have to be to get the average to where you want, and then set every single record after that equal to the average, i.e.

View attachment 119440

So cell B1 is your desired average and cells A1:A4 are your initial values.
You could use this formula in cell A5 to get the next record so that the average will be exactly your desired value:

=(B1*(COUNT(A1:A4)+1))-SUM(A1:A4)

and then just use your average for the rest of the entries, i.e. put in cell A6 and copy down to A30:

=$B$1

Your solution is very close to what I'm looking for. This does solve my question but a little more context is I want to know what continuous number needs to be filled in, which yours will show the calculated then will repeat the 50. What I'm trying to solve with full context is what number will be needed to sustain (minimum required) over the rest of the range. Below is a screen shot of what I'm trying to fill out. I manually figured out the numbers in the highlighted cells, but is there a way for that to auto fill the calculated number? I will input a new number every day next to the respective date and I want the rest of the days to automatically to fill in the calculated number (highlighted cell) to equal the target number.

 

[Joe4]

Do you mean that you want to average it out, so you populate all the missing values with the same number, and get to your desired average, something like this?

If so, here is the formula I put in cell A5 and copied down to A30:

=((30*$B$1)-SUM(A$1:A$4))/(30-COUNT($A$1:$A$4))

 

[ONEIL290]

Do you mean that you want to average it out, so you populate all the missing values with the same number, and get to your desired average, something like this?
View attachment 119451

If so, here is the formula I put in cell A5 and copied down to A30:

=((30*$B$1)-SUM(A$1:A$4))/(30-COUNT($A$1:$A$4))

Yes that's it. this will work as I can adjust the number (30) to the desired amount I need for the full range. Is there anyway to make it auto adjust so that when I manually fill in cell A5 with a new number. say 60, it will auto adjust cells A6-A30 to the new number needed to have the average of 50? Essentially to have the sum range automatically expanding so I wont have to change it in the formula.

 

[Joe4]

You can replace any of the hard-coded references in the formula with a cell reference (where you store the value you want to use).
Just remember that if you are going to be copying the formula down a number of rows that you will need to use absolute range referencing (i.e. $A$5 instead of A5).

 

[ONEIL290]

You can replace any of the hard-coded references in the formula with a cell reference (where you store the value you want to use).
Just remember that if you are going to be copying the formula down a number of rows that you will need to use absolute range referencing (i.e. $A$5 instead of A5).

I'm able to do it just by moving the calculated to be displayed in only one cell which satisfies my needs. I can add data to the range and it auto updates. Thank you very much for your help!