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?


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]Store[/TD]
[TD="align: center"]1Star[/TD]
[TD="align: center"]2Star[/TD]
[TD="align: center"]3Star[/TD]
[TD="align: center"]4Star[/TD]
[TD="align: center"]5Star[/TD]
[TD="align: center"]Avg.[/TD]
[TD="align: center"]Target Avg.[/TD]
[TD="align: center"]# of Stars Needed.[/TD]
[/TR]
[TR]
[TD="align: center"]ABC[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1.3[/TD]
[TD="align: center"]2.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2.47[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]DEF[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]2.6[/TD]
[TD="align: center"]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]MNO[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2.7[/TD]
[TD="align: center"]3.5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
















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

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[/TR]
[TR]
[TD="align: center"]Store[/TD]
[TD="align: center"]1Star[/TD]
[TD="align: center"]2Star[/TD]
[TD="align: center"]3Star[/TD]
[TD="align: center"]4Star[/TD]
[TD="align: center"]5Star[/TD]
[TD="align: center"]Avg.[/TD]
[TD="align: center"]Target Avg.[/TD]
[TD="align: center"]# of Stars Needed.[/TD]
[/TR]
[TR]
[TD="align: center"]ABC[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2.56[/TD]
[TD="align: center"]2.5[/TD]
[TD="align: center"]12[/TD]
[/TR]
[TR]
[TD="align: center"]XYZ[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]3.05[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]DEF[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3.00[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]1[/TD]
[/TR]
[TR]
[TD="align: center"]MNO[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2.76[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]















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