Moving frequency rate formula

[ThorThunder88]

I have a data set of different sites and different events that occur on them, broken down by month

Entity	KPI	Jun-16	Jul-16	Aug-16	Sep-16	Oct-16	Nov-16	Dec-16	Jan-17	Feb-17	Mar-17	Apr-17	May-17	Jun-17	Jul-17	Aug-17	Sep-17	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18
Site 1	Event Type 1											3
Site 1	Event Type 2	2						5											1			58
Site 1	Event Type 3				3					2			2						2					3
Site 1	Event Type 4			1		1							2			2					2
Site 1	Event Type 5	2						2					6
Site 1	Event Type 6																							2
Site 1	Total manhours	12479.688	9640.63	11104.688	10060.938	9090.63	11745.313	6503.13	5046.88	7895	10313	7242.188	11676.563	10552.813	12174.8438	14460.938	15785.938	17412.3438	19805	16335.313	18463.8	28635.63	27625.63	24742.3438

<tbody>
</tbody>

Site 2	Event Type 1
Site 2	Event Type 2					5		2									5						2	2
Site 2	Event Type 3		2						2					2						2
Site 2	Event Type 4		5				2			3	3				1			1					5
Site 2	Event Type 5		2										2								1	3	3
Site 2	Event Type 6					3					3													1
Site 2	Total manhours	9983.75	7712.5	8883.75	8048.75	7272.5	9396.25	5202.5	4037.5	6316	8250	5793.75	9341.25	8442.25	9739.875	11568.75	12628.75	13929.875	15844	13068.25	14771	22908.5	22100.5	19793.875

<tbody>
</tbody>

Each site has a table that looks like this

Site 1	Frequency Rates
Event Type 1
Event Type 2
Event Type 3
Event Type 4
Event Type 5
Event Type 6

<tbody>
</tbody>

above all of these tables is a Month selector.

I need a formula that reads what I enter in the month selector (e.g. Apr-18), can find the site in the data set (because when I extract the data from the system it orders sites differently each time), then for each event type applies the formula i need to calculate the frequency rate (12 months of events upto and including selected month x 1000000/12 months of manhours upto and including selected month)

It has to be able to move so that if i change that month selector to Feb-18 for example, it will present the frequency rate for that month using the above formula.

Please help

 

[jacobrcotton]

Can you attached a (de-identified) spreadsheet for reference? I think it would be easier if we would play with a real (but de-ID'd) data set.

 

[ThorThunder88]

There was no option to attach a file