Rolling 12 months - Bradford Factor results

Hi. In DB4:
=IF($C4="","",SUMPRODUCT(($D$3:$CX$3="Monthly Absence (days)")*(COLUMN($D:$CX)=LARGE(($D$3:$CX$3="Monthly Absence (days)")*COLUMN($D:$CX),ROW($1:$12)))*$D4:$CX4))

and in DC4:
=IF($C4="","",SUMPRODUCT(($D$3:$CX$3="Monthly Occurences")*(COLUMN($D:$CX)=LARGE(($D$3:$CX$3="Monthly Occurences")*COLUMN($D:$CX),ROW($1:$12)))*$D4:$CX4))

You can delete column DA, as it is redundant.

Wait a minute. I got confused by your VERY complex data layout. Back shortly with V2...

If I were you, I would NOT present my data like that!! I would insert FOUR columns between C&D and have 1) the most recent month's days/occurrences and the rolling 12 month days/occurrences and the calculated BF. Yo would then need only 2 columns per month, not 7 (including the "blank" month ID column). The other advantage would be that (with a bit of formula tweaking) you would NOT need to change formula ranges or copy paste every month.

All the info you need would be beside the person's name, too, rather than half a mile away!!

Views??

So, I can see you've been back here, since my last post, but haven't commented...

However, I had a few spare minutes and implemented my thoughts. This is what I would suggest for your problem.

the only "problem" is that you need to add zeros if there are no absences. becasue you might forget, I have amended the headings in D & E to show the most recent month added (dynamically) to provide you with a visual cross-check