SOLVED

# formula help - calculating score needed to bring score past certain marks

Copper Contributor

# formula help - calculating score needed to bring score past certain marks

Hello

in the table below i need a formula or help in how to calculate how many 5 star reviews it will take to bring the current rating up to above 3.5 and 4 stars and also 5 stars (this column isn't added yet)

column D is based off another sheet which averages out the star score and inserts here

what's my best way to work this out

FYI column j and k are hardtyped and not based off a formula and i want to automate now

5 Replies
best response confirmed by cmckernan93 (Copper Contributor)
Solution

# Re: formula help - calculating score needed to bring score past certain marks

Put this formula in J2 and drag it down to fill the column

``=IF(3.5<\$D2,"",((\$C2*\$D2)-(3.5*\$C2))/(3.5-5))``

Put this formula in K2 and drag in down to fill the column

``=IF(4<\$D2,"",((\$C2*\$D2)-(4*\$C2))/(4-5))``

# Re: formula help - calculating score needed to bring score past certain marks

@cmckernan93 Same thought and result as @Jagodragon though formulated slightly different and noting that the scores you typed are substantially different from ours.

Formula in J2, copied down and across:

``=MAX(0,\$C2*(J\$1-\$D2)/(5-J\$1))``

Note that I took the liberty to use dynamic headers in J1 and K1 so that you don't have to hard-code the value of the target into the formula.

File attached.

# Re: formula help - calculating score needed to bring score past certain marks

thank you, that worked perfect

if i wanted to find the rating to 4.95 star average would just change the below "3.5" numbers to 4.95? or is it different again?

=IF(3.5<\$D2,"",((\$C2*\$D2)-(3.5*\$C2))/(3.5-5))

# Re: formula help - calculating score needed to bring score past certain marks

@cmckernan93 Personally I prefer solutions that allow me to set variables outside the formulas. With my formula, you would just change the 3.5 in the header to 4.95 once, rather than change it twice in the first formula and then copy the formula down. Up to you what you prefer.

# Re: formula help - calculating score needed to bring score past certain marks

@Riny_van_Eekelen: I agree. but i didn't want to suggest reformatting his entire sheet.

@cmckernan93: you are correct. if you added a cell where you could enter a desired review value by itself, you could change the "3.5" to a cell address.
1 best response

Accepted Solutions
best response confirmed by cmckernan93 (Copper Contributor)
Solution

# Re: formula help - calculating score needed to bring score past certain marks

Put this formula in J2 and drag it down to fill the column

``=IF(3.5<\$D2,"",((\$C2*\$D2)-(3.5*\$C2))/(3.5-5))``

Put this formula in K2 and drag in down to fill the column

``=IF(4<\$D2,"",((\$C2*\$D2)-(4*\$C2))/(4-5))``