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

[ultma]

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.

 

[bebo021999]

Does D2 work?
If not, what is your expected result in D2 (and D2 downward, maybe?)

 

[jtakw]

Hi,

If I understand correctly, And if your data is Always ON/OFF respectively, may be something like one of these (C1 or C2) single cell formula might work, depending on how your actual data setup is:

Book3.xlsx
	A	B	C
1	44608	ON	0.014594
2	44608.01	OFF	0.014594
3	44608.01	ON
4	44608.02	OFF
5	44608.02	ON
6	44608.02	OFF
Sheet1006

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((B1:B6="Off")*(A1:A6)-(B1:B6="On")*(A1:A6))
C2	C2	=SUMIF(B1:B6,"Off",A1:A6)-SUMIF(B1:B6,"On",A1:A6)

 

[ultma]

Hi,

If I understand correctly, And if your data is Always ON/OFF respectively, may be something like one of these (C1 or C2) single cell formula might work, depending on how your actual data setup is:

Book3.xlsx
	A	B	C
1	44608	ON	0.014594
2	44608.01	OFF	0.014594
3	44608.01	ON
4	44608.02	OFF
5	44608.02	ON
6	44608.02	OFF
Sheet1006

Cell Formulas
Range	Formula
C1	C1	=SUMPRODUCT((B1:B6="Off")*(A1:A6)-(B1:B6="On")*(A1:A6))
C2	C2	=SUMIF(B1:B6,"Off",A1:A6)-SUMIF(B1:B6,"On",A1:A6)

Thank you
I don't know why I didn't consider sumproduct I use it all the time, must of been a bad day.

One issue if I was being picky is if the initial value is "off" I need to ignore it hence my formula in D2 which didn't work subtracted the ones above "OFF" from the "OFF" times

I also thought sumproduct would still work like this =SUMPRODUCT((B:B="Off")*(A:A)-(B:B="On")*(A:A)) as long as the arrays are the same, I guess I was wrong.

 

[ultma]

"I also thought sumproduct would still work like this =SUMPRODUCT((B:B="Off")*(A:A)-(B:B="On")*(A:A)) as long as the arrays are the same, I guess I was wrong."

ignore this there is some junk at the bottom of the data from the data link Iferror should fix that

 

[ultma]

"I also thought sumproduct would still work like this =SUMPRODUCT((B:B="Off")*(A:A)-(B:B="On")*(A:A)) as long as the arrays are the same, I guess I was wrong."

ignore this there is some junk at the bottom of the data from the data link Iferror should fix that

the sumif formula works with the junk in the bottom rows, iferror's didn't work with sumproduct even as an array the A columns throw it off with the junk at the end

 

[ultma]

the sumif formula works with the junk in the bottom rows, iferror's didn't work with sumproduct even as an array the A columns throw it off with the junk at the end

so I just have the issue if the first cell in B is "OFF"

 

[jtakw]

so I just have the issue if the first cell in B is "OFF"

May be you can just Offset the Range by 1 row as shown below.
You will Not be able to use Entire Column references using this method, but you can make the range very large, more than you think you'll ever need, like B2:B100000 or whatever you want:

Book3.xlsx
	A	B	C
1	44607.94	OFF
2	44608	ON	0.014594
3	44608.01	OFF	0.014594
4	44608.01	ON
5	44608.02	OFF
6	44608.02	ON
7	44608.02	OFF
Sheet1006

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT((B2:B7="Off")*(A2:A7)-(B1:B6="On")*(A1:A6))
C3	C3	=SUMIF(B2:B7,"Off",A2:A7)-SUMIF(B1:B6,"On",A1:A6)

 

[ultma]

May be you can just Offset the Range by 1 row as shown below.
You will Not be able to use Entire Column references using this method, but you can make the range very large, more than you think you'll ever need, like B2:B100000 or whatever you want:

Book3.xlsx
	A	B	C
1	44607.94	OFF
2	44608	ON	0.014594
3	44608.01	OFF	0.014594
4	44608.01	ON
5	44608.02	OFF
6	44608.02	ON
7	44608.02	OFF
Sheet1006

Cell Formulas
Range	Formula
C2	C2	=SUMPRODUCT((B2:B7="Off")*(A2:A7)-(B1:B6="On")*(A1:A6))
C3	C3	=SUMIF(B2:B7,"Off",A2:A7)-SUMIF(B1:B6,"On",A1:A6)

I almost had it

=IF(B1="off",SUMIF(B:B,"On",A:A)-SUMIF(B:B,"Off",A:A),SUMIF(B:B,"Off",A:A)-SUMIF(B:B,"On",A:A))

however the trick is that the data extract comes form two dates so I need to account for that, however the first field and last field in A does not finish on the exact start and end dates

so I needed to find the last numerical value in the date column A

={INDEX(A:A,MATCH(9.99999999999999E+307,A:A))}
this is now the helper cell J1

and the start date - cell A1

and the difference between the two extract dates K1 and L1

and the difference between the end date L1 and the helper cell J1 the last numerical output in column A

=IF(B1="off",(L1-K1)-(L1-J1)-(A1-K1)-(SUMIF(B:B,"On",A:A)-SUMIF(B:B,"Off",A:A)),SUMIF(B:B,"Off",A:A)-SUMIF(B:B,"On",A:A))

however the spanner in the works is if the last cell in B is also "OFF" as well as B1 and vice versa for B1 and last value also being "ON"

I think I now need a formula to find the last ON and the last OFF and return the row numbers

then use A1:INDEX(A:A,"last row number") to fix this

I would also need a formula to return the corresponding date/time of the last "On" and "OFF"

then I can construct some logic for the different scenarios with some careful thought as to not add error from the first and the final sampled data and the end date.

 

[ultma]

I got it to work for all scenarios (on off, on on, off off, off on) it was not as complex as I thought it would need to be (just looks that way). The basis was the simple SUMIF formula inside an IF with INDEXED cell references e.g. INDEX(A:A,"row number"):INDEX(A:A,"row number") and some simple MATCH, LOOKUP and nested MATCH/LOOKUP formulas for the row of first ON, Row of last OFF, Row of last ON, and to get the date of the last ON

I nested it all together which I probably shouldn't of done until after I posted this.

Example.xlsx
	A	B	C	D
1	16-Feb-22 00:03:23	ON	16-Feb-22 00:00:00	17-Feb-22 00:00:00
2	16-Feb-22 00:10:28	OFF	1	Row of first ON
3	16-Feb-22 00:16:19	ON	118	Row of last OFF
4	16-Feb-22 00:23:31	OFF	16-Feb-22 23:34:02	Date of last ON
5	16-Feb-22 00:28:50	ON	117	Row of last ON
6	16-Feb-22 00:35:34	OFF	0.617933773	formula
Sheet1

Cell Formulas
Range	Formula
D1	D1	=C1+1
C2	C2	=MATCH("ON",B:B,0)
C3	C3	=MATCH(LOOKUP(2,1/(B:B="OFF"),A:A),A:A,0)
C4	C4	=LOOKUP(2,1/(B:B="ON"),A:A)
C5	C5	=MATCH(LOOKUP(2,1/(B:B="ON"),A:A),A:A,0)
C6	C6	=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)))+(D1-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))))