How to Calculate number required to achieve target percentage

[chieh]

HI Everyone:

I need to figure out Number of 5 Star Review required in order to increase Average rating (Column G) to Target 1 Average Rating (Column H)

Currently I have the formula in column G, and when entering a value in Column I, the average in column changes. See 2nd table for example.
I increase the # of 5 Star Needed on column I to change the average on column G.
The formula inside column G5 is: =((B2 * 1) + (C2 * 2) + (D2 *3) + (E2 * 4) + (F2 * 5) + (I2 *5)) / (B2 + C2 + D2 + E2 + F2 +I2)

However, this formula is not dynamic, and the best formula will be to Change the Target Avg. and in return it will automatically figure out # of 5 Star needed, Any idea on how to achieve this result?

A	B	C	D	E	F	G	H	I
Store	1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of Stars Needed.
ABC	21	1	0	1	1	1.3	2.5
XYZ	16	2	1	4	7	2.47	3
DEF	3				2	2.6	3
MNO	17	2	3	2	12	2.7	3.5

<tbody>
</tbody>

Example of manually adjusting the # of star needed in Column I to increase the average rating on Column G. Same spreadsheet as above.

A	B	C	D	E	F	G	H	I
Store	1Star	2Star	3Star	4Star	5Star	Avg.	Target Avg.	# of Stars Needed.
ABC	21	1	0	1	1	2.56	2.5	12
XYZ	16	2	1	4	7	3.05	3	9
DEF	3				2	3.00	3	1
MNO	17	2	3	2	12	2.76	3.5	6

<tbody>
</tbody>

Thank you in advance.
Cheers!

 

[gsbelbin]

This should do the job

=ROUNDUP((H2*SUM($B2:$F2)-(B2*1+C2*2+D2*3+E2*4+F2*5))/(5-H2),0)

 

[chieh]

Hi Gsbelbin:

Thank you very much. Your suggestion works perfectly.

Kind regards
Chieh