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

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
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

B
  • Question Question
Event code to populate message upon workbook open at specified dates
Replies
2
Views
204
bh24524
B
ExcelNewbie2020
  • Solved
filter multiple columns
Replies
7
Views
216
Cubist
Cubist
D
  • Solved
List of items within a specific month
Replies
4
Views
218
Fluff
Fluff
S
  • Question Question
Formula to identify the time & frequency of max value.
Replies
5
Views
272
Cubist
Cubist
S
  • Solved
Web Query in VBA Loop through tables on page to choose get tags from table instead of text
2
Replies
13
Views
607
Domenic
D

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

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