Spreadsheet: 836190d1689162383-using-offset-formula-to-calculate-the-average-where-there-is-no-data-zeros-in-data-dummy-data-for-offset-pm
Sheet: Data_Leavers

Attrition rates
1. input data here - this format has to stay the same | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 22/23 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24
 | P11 | P11 | P11 | P11 | P12 | P12 | P12 | P12 | P13 | P13 | P13 | P13 | P1 | P1 | P1 | P1 | P2 | P2 | P2 | P2 | P3 | P3 | P3 | P3 | P4 | P4 | P4 | P4 | P5 | P6
 |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  |  | [NAME] |  |  |  | P4 W4
Depot | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Total No of employed drivers | Total No  of leavers | Period Attrition Rate % | Internal transfers | Period Attrition Rate % | Period Attrition Rate %
Barking | 340 | 1.5 | 0.004411764705882353 |  | 340 | 5.5 | 0.0161764705882353 |  | 335.5 | 5 | 0.01490312965722802 |  | 328 | 1.5 | 0.004573170731707317 |  | 321 | 6 | 0.01869158878504673 |  |  |  | 0.012 |  |  |  | 0.035
Bow | 358 | 5 | 0.01396648044692737 |  | 353 | 4.5 | 0.01274787535410765 |  | 355 | 3.5 | 0.009859154929577466 |  | 350.5 | 6 | 0.01711840228245364 |  | 344 | 3 | 0.00872093023255814 |  |  |  | 0.008
Leyton | 284.5 | 3.5 | 0.01230228471001757 |  | 280.5 | 4 | 0.0142602495543672 |  | 280 | 2.5 | 0.008928571428571428 |  | 276 | 3.5 | 0.01268115942028986 |  | 274 | 6.5 | 0.02372262773722628 |  |  |  | 0.019
Attrition trend | 1 | 13 | 14 | 15 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 | 24 | 25 | 26
2. formula to bring all the "period attrition rate" data from above together | 22/23 | 22/23 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24
 | P12 | P13 | P1 | P2 | P3 | P4 | P5 | P6
Depot | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate % | Period Attrition Rate %
Barking |  |  |  |  |  |  |  |  |  | what if I use if is number/search to bring in % where there are numbers above otherwise show 0?
Bow
Leyton
IF CELL IS BLANK
IF CELL IS BLANK THEN RETURN THE AVERAGE OF LAST 3M OF DATA
COMBINED ALL |  |  |  |  |  |  |  |  | AVERAGE(OFFSET(I20,0,0,1,-$B$30))
COMBINED ALL WITH IF ERROR
[NAME] VALUE IF BLANK
INDEX MATCH FOR ABOVE
3. the average to be used. I want this to be the average of the latest X months (X being B28) but if the data above = 0, I want it to ake the average there are numbers. E.g. in F,G,H and I32, it is showing blank because the cell it's looking up, F/G/H/I20 is blank so I want it to take the average of E,D and C 20 since there are numbers in here.
Moving average period >>> | 3
 | 22/23 | 22/23 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24 | 23/24
 | P1 | P13 | P1 | P2 | P3 | P4 | P5 | P6
Depot
Barking
Bow
Leyton
 |  |  |  |  | Currently the formula works here but it only works where there are values above but it won't work for cells on the right >>> | I want the average for the latest 3 months so if P4,P3,P2 from rows 31 to 33 above have a number > 0%, i want the average of P6,P5,P4 otherwise Where there are no numbers above in P4,P3,P2, I want it to give me the average of the 3 months where there are numbers - in this instance it's taking the average of p3,p2,p1 | I want the average for the latest 3 months so if P5,P4,P3 from rows 31 to 33 above have a number > 0%, i want the average of P6,P5,P4 otherwise Where there are no numbers above in P5,P4,P3, I want it to give me the average of the 3 months where there are numbers - in this instance it's taking the average of p3,p2,p1 | I want the average for the latest 3 months so if P6,P5,P4 from rows 31 to 33 above have a number > 0%, i want the average of P6,P5,P4 otherwise Where there are no numbers above in P6,P5,P4, I want it to give me the average of the 3 months where there are numbers - in this instance it's taking the average of p3,p2,p1