Productivity calculation

Hi All, I've been working on this for a while, but can't figure out the best way to approach it.

Basically I Have a sheet with 3 pages, "Section" 'Raw" and "states"

On the section page, columns from B1 onwards is a name, Rows from a2-a50 are time intervals in 15 minutes intervals.

on the Raw page it is a break down of instances the people enter certain "States" and at what time. with the state they are in.

on the "States" Page, this lists the possible states the employees can be, and if they are Productive or unproductive.


The idea of the sheet is on the "section" sheet, there will be a calculation for each interval, for example on B2, it would calculate the amount of time in Productive states for the advisor a. and if it is above 85% of the time is productive, return a percentage value of what that employee achieved. Target wise it should be between 85%-100%

The problem I encountered is their is technically no limit of amount of states within a 15 minute period, so i cannot get excel to figure out how to work this out and to return the percentage value.

Finally, in Column L, it would do the same, but for all the team as a total (as in if first employee got 0% then next few would need higher % to make the average higher!

Thanks for any input

added an example

Edit- On the Raw sheet, it has two columns for times, when they started and ended that state, followed by the next states start time and end time. Thanks!