Moving frequency rate formula

T

ThorThunder88

New Member
Joined
May 16, 2018
Messages
2
I have a data set of different sites and different events that occur on them, broken down by month

[TABLE="width: 1720"]
<tbody>[TR]
[TD]Entity
[/TD]
[TD]KPI
[/TD]
[TD="align: right"]Jun-16
[/TD]
[TD="align: right"]Jul-16
[/TD]
[TD="align: right"]Aug-16
[/TD]
[TD="align: right"]Sep-16
[/TD]
[TD="align: right"]Oct-16
[/TD]
[TD="align: right"]Nov-16
[/TD]
[TD="align: right"]Dec-16
[/TD]
[TD="align: right"]Jan-17
[/TD]
[TD="align: right"]Feb-17
[/TD]
[TD="align: right"]Mar-17
[/TD]
[TD="align: right"]Apr-17
[/TD]
[TD="align: right"]May-17
[/TD]
[TD="align: right"]Jun-17
[/TD]
[TD="align: right"]Jul-17
[/TD]
[TD="align: right"]Aug-17
[/TD]
[TD="align: right"]Sep-17
[/TD]
[TD="align: right"]Oct-17
[/TD]
[TD="align: right"]Nov-17
[/TD]
[TD="align: right"]Dec-17
[/TD]
[TD="align: right"]Jan-18
[/TD]
[TD="align: right"]Feb-18
[/TD]
[TD="align: right"]Mar-18
[/TD]
[TD="align: right"]Apr-18
[/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 2
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]58
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 5
[/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Event Type 6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Site 1
[/TD]
[TD]Total manhours
[/TD]
[TD="align: right"]12479.688
[/TD]
[TD="align: right"]9640.63
[/TD]
[TD="align: right"]11104.688
[/TD]
[TD="align: right"]10060.938
[/TD]
[TD="align: right"]9090.63
[/TD]
[TD="align: right"]11745.313
[/TD]
[TD="align: right"]6503.13
[/TD]
[TD="align: right"]5046.88
[/TD]
[TD="align: right"]7895
[/TD]
[TD="align: right"]10313
[/TD]
[TD="align: right"]7242.188
[/TD]
[TD="align: right"]11676.563
[/TD]
[TD="align: right"]10552.813
[/TD]
[TD="align: right"]12174.8438
[/TD]
[TD="align: right"]14460.938
[/TD]
[TD="align: right"]15785.938
[/TD]
[TD="align: right"]17412.3438
[/TD]
[TD="align: right"]19805
[/TD]
[TD="align: right"]16335.313
[/TD]
[TD="align: right"]18463.8
[/TD]
[TD="align: right"]28635.63
[/TD]
[TD="align: right"]27625.63
[/TD]
[TD="align: right"]24742.3438
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1720"]
<tbody>[TR]
[TD]Site 2
[/TD]
[TD]Event Type 1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Event Type 2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Event Type 3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Event Type 4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Event Type 5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Event Type 6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1
[/TD]
[/TR]
[TR]
[TD]Site 2
[/TD]
[TD]Total manhours
[/TD]
[TD="align: right"]9983.75
[/TD]
[TD="align: right"]7712.5
[/TD]
[TD="align: right"]8883.75
[/TD]
[TD="align: right"]8048.75
[/TD]
[TD="align: right"]7272.5
[/TD]
[TD="align: right"]9396.25
[/TD]
[TD="align: right"]5202.5
[/TD]
[TD="align: right"]4037.5
[/TD]
[TD="align: right"]6316
[/TD]
[TD="align: right"]8250
[/TD]
[TD="align: right"]5793.75
[/TD]
[TD="align: right"]9341.25
[/TD]
[TD="align: right"]8442.25
[/TD]
[TD="align: right"]9739.875
[/TD]
[TD="align: right"]11568.75
[/TD]
[TD="align: right"]12628.75
[/TD]
[TD="align: right"]13929.875
[/TD]
[TD="align: right"]15844
[/TD]
[TD="align: right"]13068.25
[/TD]
[TD="align: right"]14771
[/TD]
[TD="align: right"]22908.5
[/TD]
[TD="align: right"]22100.5
[/TD]
[TD="align: right"]19793.875
[/TD]
[/TR]
</tbody>[/TABLE]


Each site has a table that looks like this

[TABLE="width: 182"]
<tbody>[TR]
[TD]Site 1
[/TD]
[TD]Frequency Rates
[/TD]
[/TR]
[TR]
[TD]Event Type 1
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Type 2
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Type 3
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Type 4
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Type 5
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Event Type 6
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Duplicate row based on cell with text separated by semicolon
Replies
5
Views
99
JEC
JEC
B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
239
bh24524
B
P
  • Solved
Vlookup and match issue
Replies
7
Views
91
etaf
etaf
ExcelNewbie2020
  • Solved
filter multiple columns
Replies
7
Views
229
Cubist
Cubist
N
  • Solved
Averaging every Nth cell in a column
Replies
9
Views
112
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top