Time remaining

[dado6612]

Hi to all

I'm trying to figure out how to accomplish this, but have no idea/knowledge
What I need is calculation how much time in theory takes till score of one side get 1800 or more, with some rules.
Rules are: In first 30 minutes, winner side get 10 points for each holding minute (have bigger score than opposite time in current minute) - 300 points
In next 30 minutes winner side get 20 points for same reason - 600 points
in next 30 minutes winner side get 30 points - 900
In last 30 minutes winner side get 60 points - 1800
Overall winner is who first get 1800 points or more(possible after 90 minutes).

Ex.
One side have 50 points
Other side have 200 points
Sum is 250, current minute is 25
So, other side need 1600 more points, they have 5 more minutes where they get 10 points, so after first 30 minutes they might have 250 points
in next 30 minutes they can get 600, and have 850
After another 30 minutes they might have 1750, and they need one more minute to get 1810, so after 66 minutes we can have winner, in theory.

Any help how to calculate that with some formula with those rules and 3 inputs?

Thanks

 

[p45cal]

In your scenario, the side which has 200 points must win unless there is some other way of getting points. This is because the side with 50 points can never get more points. Perhaps there is another score somewhere, completely independent of these points you're talking of? If so can the team with that leading score swap from minute to minute from team 1 to team 2 and back?

 

[dado6612]

It can, but in my scenario I'm looking in theory when will any of side, mostly current winning one, get 1800 points.
It can be 50.01 in one minute, and 49.9 in other for same side, just once he will get points, otherwise wont. That's irrelevant here, as i think, cause I'm looking for shortest time one side will get 1800 points.

 

[dado6612]

Does anyone have any idea?

 

[dado6612]

bump

 

[dado6612]

Anyone

 

[p45cal]

No time for a slick built-in worksheet formula solution, but a very basic user-defined function, used in a sheet as follows:
=FewestMinutesToWin(200,25)
where the 200 is the current score and the 25 is the curent minute, and returns your 66 minutes.
Backed up by the code:

Function FewestMinutesToWin(CurrentScore, CurrentMinute)
FewestMinutesToWin = 0
For i = CurrentMinute To 120
  Select Case i
    Case Is <= 30
      Increment = 10
    Case Is <= 60
      Increment = 20
    Case Is <= 90
      Increment = 30
    Case Else
      Increment = 60
  End Select
  CurrentScore = CurrentScore + Increment
  If CurrentScore >= 1800 Then Exit For
  FewestMinutesToWin = FewestMinutesToWin + 1
Next i
If CurrentScore < 1800 Then FewestMinutesToWin = "Not enough time left"
End Function

 

[dado6612]

LOL
Why I didnt check it earlier. I've made this up
=ROUNDUP((IF((B1+B2)<299;(((300-(B1+B2))/10)+60);IF(AND((B1+B2)<899;(B1+B2)>299);((900-(B1+B2))/20)+30+((900-((((900-(B1+B2))/20)*20)+B1))/60);IF(AND((B1+B2)<1800;(B1+B2)>899);((1800-(B1+B2))/30)+((1800-((((1800-(B1+B2))/30)*30)+B1))/60);((1801-B1)/60)))));0)

Gonna test your code
Thanks

 

[dado6612]

Ofcourse, your code works perfectly
Thanks a lot again