Search code examples
excelexcel-formulaexcel-2013excel-2016

Excel: how to calculate percentage within 24h when have dynamic data (over 24h)

I cant figure out how to calculate percentage within one day (24h) with a set of data that is dynamically changing, you have one day and different time as you can see on the SS. But thing is, I can calculate general difference between time using =MOD (B3-B2,1) and then just convert that into percentage (and I am getting just small % portion within one day). But problem persists when the time is after 23:59...

That "System" Column is actually two processes that are alternately working, so one is OFF other is ON and both got that date/time (working time).

How to calculate % one system within one day - so its working time in percentage per one day (so, for 10.01, 11.01..)

enter image description here

Something like this, and then lets say SUM of percentages withing one day for one system. There gives me over 100% for many examples (dates). enter image description here

enter image description here

I got a different data set (tried on other examples) and didnt work good:

 SUMIFS($B$2:$B$1020;$A$2:$A$1020;$F3;$C$2:$C$1020;"<>"&G$1)-SUMIFS($B$2:$B$1020;$A$2:$A$1020;$F3;$C$2:$C$1020;G$1)+(INDEX($C$2:$C$1020;MATCH($F3;$A$2:$A$1020))=G$1)

Is it maybe cuz I edited my data from PowerQuery and then inputed to the sheet? or anything else?

enter image description here

Solution

  • Bearing in mind that a day starts from t=0 and ends in t=1 where t is time in days, for two processes A and B we can consider four scenarios:

    Start with process A, end with process B

|___A1---B1___A2---B2___|

B1-A1+B2-A2

∑B - ∑A

Start with process A, end with process A

|___A1---B1___A2---B2___A3---|

B1-A1+B2-A2+1-A3

∑B - ∑A +1

Start with process B, end with process A

|---B1___A1---B2___A2---|

B1-0+B2-A1+1-A2

∑B - ∑A +1

Start with process B, finish with process B

|---B1___A1---B2___A2---B3___|

B1-0+B2-A1+B3-A2

∑B - ∑A

    Where A1 is the first on time for process A, A2 the second etc. and --- means process A is on and ___ means process A is off.

    So we can just subtract the sum of the start times for process A from the sum of the start times for process B and add 1 if the last process in the day is process A.

    Formula for a full day (like 11/1/19)

    =SUMIFS($B$2:$B$23,$A$2:$A$23,$F3,$C$2:$C$23,"<>"&G$1)-SUMIFS($B$2:$B$23,$A$2:$A$23,$F3,$C$2:$C$23,G$1)+(INDEX($C$2:$C$23,MATCH($F3,$A$2:$A$23))=G$1)

    enter image description here

    What about the first day, where there is nothing before half past one? Now it is wrong to assume that anything before process A is occupied by process B and vice versa, so have to subtract the first time if the first process is not process A and add another term to the calculation:

    =SUMIFS($B$2:$B$23,$A$2:$A$23,$F2,$C$2:$C$23,"<>"&G$1)-SUMIFS($B$2:$B$23,$A$2:$A$23,$F2,$C$2:$C$23,G$1)+(INDEX($C$2:$C$23,MATCH($F2,$A$2:$A$23))=G$1)-(INDEX($C$2:$C$23,MATCH($F2,$A$2:$A$23,0))<>G$1)*$B2

    So the total time on the first day is not 24 hours but about 10.5 hours or 44% of a day.