Function/Formula for Staggered Injury Rate

Hi All,

I have a scenario where I am trying to generate a table to show what the Injury rate of a player is in a particular month based on how I manually enter the rate.

I have attached an example of how I have done (I have used a lot of IF Statements, however if I expand the table out/list this is a big tedious task).

In the sheet attached:

ROW 2 (Column B - H) = Months
ROW 3 (Column B - H) = How many players are going to be playing per month (however in Month 1 I have 8 players in month 2 I have 16 players so 8 new players joined in month 2)
ROW 4 (Column B - H) =Injury rate per month (So a new player joining's injury rate in Month 1 is 0.1, in Month 2 0.2 etc etc)

ROWs 10-14 = this is the table using the above to show what the injury rate of the players is month on month

E.g 8 Players joined Month 1 = Injury Rate Month 1 is 0.1, Month 2 is 0.2 etc etc

Another 8 players Joined Month 2 = So their Injury Rate in Month 2 is 0.1, and Month 3 is 0.2 etc etc

In Columns J to R is the Injury Rate of each player month on Month (I have used IF Statements to show this)

The idea is to be able to expand the table if I increase the number of months and number of player.

Any help will be appreciated.

Thanks

Hi,

Thanks for the reply and the solution.

This solution works really well if the Injury rate is linear i.e 0.1, 0.2,0.3,0.4 etc month on month

However if there is a big change i.e. from 0.5 to 1.0 in one of the months the players further down do not start with an injury rate of 0.1.