how to calculate what score would increase average by 1

[XrayboX]

I have spreadsheet that I'm using to track bowling scores.
One of the formulas needed,is to calculate what the lowest total score for the night
(total score(Series)=Game1 + Game2 + Game3)is needed to increase your average by 1 point.
what I was doing, using these 3 named Ranges

Season_avg = O8:O43 calculated as
ROUND(Total_Pins/Total_Games;0)

Total_Games = I8:I43 calculated as
Week_Games+I(Prior row) (Week_games is 0 to 3 )

Total_Pins = N8:N43 calculated as
Series+N(Prior Row) Series (is between 0 to 900)

Avg_Plus = R8:R43 calculated as
(Season_Avg+1)*(Total_Games+3)-Total_Pins


Test Data
First row of data 110 182 140
series = 432
Season_Avg = 144
Avg_Plus = 438

second row of data 435 0 0
(3 less than formula still increases Average)
Series = 435
Season_Avg = 145
Avg_Plus = 447

third row of data 443 0 0
(4 less than formula still increases Average)
Series= 443
Season_Avg = 146
Avg_Plus = 454

the problem is, while the number returned will increase the average by 1 .
a smaller number would also increase the average
The drift appears to 3 4 6 7 9


Can Any of you folks
figure out how to tweak the formula so it reports the minimum necessary to increase the average by 1?

Thanks In Advance
Ray Bernache


When Linux is Free and Open, who needs Windows or Gates?

 

[davidd31415]

I can not commit the time to review your code or clean up the formula here, but perhaps this will help you:

known values:
x = sum of scores
y = number of scores

unknown value:
z = score needed to increase average by 1

x/y = current average

x+z / y+1 = average when including z

solve the following equation for z:
x+z / y+1 = (x/y) + 1

x+z = (x/y) + 1) * (y+1)

z = ((x/y) + 1) * (y+1)) - x

HTH,

Dave