Forum Discussion
cmckernan93
Aug 08, 2022Copper 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...
- Aug 08, 2022
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))
cmckernan93
Aug 09, 2022Copper Contributor
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))
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))
Riny_van_Eekelen
Aug 09, 2022Platinum Contributor
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.
- JagodragonAug 09, 2022Iron ContributorRiny_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.