Excel 2016 Add Difference Based on Binary "ON" "OFF" (Logic Text) in a Single Formula with Multiple Occurrences of said Logic

ultma · Feb 16, 2022

I just cant seem to get this to work. Basically I need to create an if array that has logic but also multiple occurrences. the easy way is in using the formula in cells C2:C6 and the SUM in E2, but this will be a very dynamic sheet with varying amounts of data and the data in A and B is from a plugin that has weird formulas in the cells and doesn't really work well with auto fill. An index match would work (maybe

) if only I could get it to produce an array (the failed attempt in D2), FILTER or JOINTEXT would work but this is for excel 2016.

In short I'm adding up the time the system is in the on state over a period of time, the period of time is controlled by the plugin so I don't need to worry about that. However I need it to be a single formula in one cell.

Example.xlsx

A

B

C

D

E

1

44608

ON

2

44608.01

OFF

0.004919

0.014756

0.014594

3

44608.01

ON

0

4

44608.02

OFF

0.005

5

44608.02

ON

0

6

44608.02

OFF

0.004676

Sheet1

Cell Formulas
Range	Formula
D2	D2	=SUM(IF(B1:B6="off",INDEX(A1:A6,MATCH("OFF",B1:B6,0),1)-INDEX(A1:A6,MATCH("OFF",B1:B6,0)-1,1),0))
E2	E2	=SUM(C2:C6)
C2:C6	C2	=IF(B2="off",A2-A1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.

ultma · Feb 17, 2022

this is what it looks like without the indexed cell ranges and references to other cells rather than nesting formula.

The indexed cell ranges do a lot of the heavy lifting thanks to C2 and C3

=IF(C5>C3,SUMIF(B1:B118,"Off",A1:A118)-SUMIF(B1:B118,"On",A1:A118)+(D1-C4),SUMIF(B1:B118,"Off",A1:A118)-SUMIF(B1:B118,"On",A1:A118))

ultma · Feb 17, 2022

ultma · Feb 17, 2022

not quite there missing logic for the bit at the start if it starts at off

mikerickson · Feb 17, 2022

No need for arrays,
The time duration that a switch is in the ON postion is the clock time it was turned Off minus the clock time it was turned On.

=SUMIF(B:B,"OFF",A:A) - SUMIF(B:B."On",A:A) is the total duration that the switch in the OP was in the On position.

ultma · Feb 18, 2022

mikerickson said:
No need for arrays,
The time duration that a switch is in the ON postion is the clock time it was turned Off minus the clock time it was turned On.

=SUMIF(B:B,"OFF",A:A) - SUMIF(B:B."On",A:A) is the total duration that the switch in the OP was in the On position.

Correct I was making it difficult for myself yesterday.

Got it sorted now just need one last IF and a tiny tweak to formula to fully make it hands free.

The probelm is at either end of the data, as the extract only records state change between two dates. The first record wont trigger until after the start time unless the start time is the same as the state change event.

I could of used helper cells to create the end and start events as well, which to be honest would be better. However I want to finish the challenge of doing it in one formula now.

ultma · Feb 20, 2022

this is the final formula
=IF(MATCH("OFF",B:B,0)<MATCH("ON",B:B,0),IF(MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)>MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(E1-LOOKUP(2,1/(B:B="ON"),A:A))+(INDEX(A:A,MATCH("OFF",B:B,0))-D1),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(INDEX(A:A,MATCH("OFF",B:B,0))-D1)),IF(MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)>MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(E1-LOOKUP(2,1/(B:B="ON"),A:A)),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))))

this is the simple version referring to other cells

=IF(D6<D2,IF(D5>D3,SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(E1-D4)+(D7-D1),SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(D7-D1)),IF(D5>D3,SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(E1-D4),SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))))

Example.xlsx

A

B

C

D

E

1

16-Feb-22 00:10:28

OFF

16/02/2022 12:10 AM

16/02/2022 12:41 AM

2

16-Feb-22 00:16:19

ON

0

2

Row of first ON

3

16-Feb-22 00:23:31

OFF

0.005

5

Row of last OFF

4

16-Feb-22 00:28:50

ON

0

16-Feb-22 00:41:39

Last ON

5

16-Feb-22 00:35:34

OFF

0.004676

6

Row of last ON

6

16-Feb-22 00:41:39

ON

0

1

Row of first OFF

7

0

16-Feb-22 00:10:28

First OFF

8

9

10

11

formula

12

0

0.00967559

Sheet1

Cell Formulas
Range	Formula
D2	D2	=MATCH("ON",B:B,0)
D3	D3	=MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)
D4	D4	=LOOKUP(2,1/(B:B="ON"),A:A)
D5	D5	=MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)
D6	D6	=MATCH("OFF",B:B,0)
D7	D7	=INDEX(A:A,MATCH("OFF",B:B,0))
C2:C12	C2	=IF(B2="OFF",A2-A1,0)
D11	D11	=IF(MATCH("OFF",B:B,0)<MATCH("ON",B:B,0),IF(MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)>MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(E1-LOOKUP(2,1/(B:B="ON"),A:A))+(INDEX(A:A,MATCH("OFF",B:B,0))-D1),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(INDEX(A:A,MATCH("OFF",B:B,0))-D1)),IF(MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)>MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))+(E1-LOOKUP(2,1/(B:B="ON"),A:A)),SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"Off",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))-SUMIF(INDEX(B:B,MATCH("ON",B:B,0)):INDEX(B:B,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)),"On",INDEX(A:A,MATCH("ON",B:B,0)):INDEX(A:A,MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)))))
D12	D12	=IF(D6<D2,IF(D5>D3,SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(E1-D4)+(D7-D1),SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(D7-D1)),IF(D5>D3,SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))+(E1-D4),SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"Off",INDEX(A:A,D2):INDEX(A:A,D3))-SUMIF(INDEX(B:B,D2):INDEX(B:B,D3),"On",INDEX(A:A,D2):INDEX(A:A,D3))))

ultma · Feb 22, 2022

I have run into a problem

I get Consecutive ON's and OFF's sometimes
and I also get BAD sometimes which can create consecutive ON's and OFF's

I cant seem to think of a way to deal with this without manual intervention, i need a way to find these subtract the time stamps from the sums unless there is another way

ultma · Feb 22, 2022

A bit more detail and sort of an idea to deal with it.

I get Consecutive ON's and OFF's sometimes (almost entirely OFF's that I have seen in a quick looks evenly spaced I would rather deal with this issue altogether than try and take advantage of this incase its not just scenario).

and I also get BAD sometimes which can create consecutive ON's and OFF's i.e. ON, BAD, ON.

I cant seem to think of a way to deal with this without manual intervention, I need a way to find these and subtract the time stamps from the On and OFF sums unless there is another way.

I know I wont get this in a single formula, I have a technique that uses SUMPRODUCT as a helper cell and a nested array formula with IF, INDEX, ROW and SMALL to create a list from a larger data source but I cant think of a criteria to find the these problematic situations.

examples of the issue's.

14-Dec-21 04:53:20	ON
14-Dec-21 05:12:17	OFF
14-Dec-21 06:27:45	Bad
14-Dec-21 06:34:09	OFF
14-Dec-21 06:50:37	ON

04-Dec-21 04:18:10	ON
04-Dec-21 04:51:44	OFF
04-Dec-21 12:51:44	OFF
04-Dec-21 20:51:44	OFF
05-Dec-21 04:51:44	OFF
05-Dec-21 12:51:44	OFF
05-Dec-21 20:51:44	OFF
06-Dec-21 04:51:44	OFF
06-Dec-21 10:27:16	ON

bebo021999 · Feb 23, 2022

What is your expected result?
I suggest 4 scenarios as per below sheet:
Value in column A
ON is negative
OFF is positive

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

1

VALUE

ON

OFF

ON

OFF

ON

OFF

ON

OFF

2

1

ON

-1

3

2

ON

4

3

ON

-3

5

4

OFF

4

6

5

OFF

5

7

6

ON

-6

8

7

OFF

7

9

8

OFF

10

9

BAD

11

10

OFF

10

12

11

ON

-11

13

12

BAD

14

13

BAD

15

14

OFF

14

16

17

18

19

scenarior 1:

scenarior 2:

scenarior 3:

scenarior 4:

20

1st ON vs 1st OFF

Last ON vs 1st OFF

1st ON vs last OFF

last ON vs last OFF

Sheet5

Cell Formulas
Range	Formula
I2,I12,I7	I2	=-A2
F4,F12,F7	F4	=-A4
J6,J15,J11	J6	=A6
A3:A15	A3	=A2+1
D17,M17,J17,G17	D17	=SUM(C2:D15)

ultma · Feb 23, 2022

Excel 2016 Add Difference Based on Binary "ON" "OFF" (Logic Text) in a Single Formula with Multiple Occurrences of said Logic

ultma

New Member

ultma

New Member

Excel Facts

ultma

New Member

ultma

New Member

mikerickson

MrExcel MVP

ultma

New Member

ultma

New Member

ultma

New Member

ultma

New Member

bebo021999

Well-known Member

ultma

New Member

Similar threads

Forum statistics

Share this page

Excel 2016 Add Difference Based on Binary "ON" "OFF" (Logic Text) in a Single Formula with Multiple Occurrences of said Logic

New Member

New Member

Excel Facts

New Member

New Member

MrExcel MVP

New Member

New Member

New Member

New Member

Well-known Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock