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

ultma

New Member
Joined
Dec 12, 2013
Messages
35
Office Version
  1. 2016
Platform
  1. Windows
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 :unsure:) 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
ABCDE
144608ON
244608.01OFF0.0049190.0147560.014594
344608.01ON0
444608.02OFF0.005
544608.02ON0
644608.02OFF0.004676
Sheet1
Cell Formulas
RangeFormula
D2D2=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))
E2E2=SUM(C2:C6)
C2:C6C2=IF(B2="off",A2-A1,0)
Press CTRL+SHIFT+ENTER to enter array formulas.
 
What is your expected result?
I suggest 4 scenarios as per below sheet:
Value in column A
ON is negative
OFF is positive
Book1
ABCDEFGHIJKLM
1VALUEONOFFONOFFONOFFONOFF
21ON-1-1
32ON
43ON-3-3
54OFF44
65OFF55
76ON-6-6-6-6
87OFF77
98OFF
109BAD
1110OFF1010
1211ON-11-11-11-11
1312BAD
1413BAD
1514OFF14141414
16
1775119
18
19scenarior 1:scenarior 2:scenarior 3:scenarior 4:
201st ON vs 1st OFFLast ON vs 1st OFF1st ON vs last OFFlast ON vs last OFF
Sheet5
Cell Formulas
RangeFormula
I2,I12,I7I2=-A2
F4,F12,F7F4=-A4
J6,J15,J11J6=A6
A3:A15A3=A2+1
D17,M17,J17,G17D17=SUM(C2:D15)
in that scenario it would be 7 assuming start and stop is 0
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Helper column(s) would help much.
Is it accepted?
Nested single formula would be a monter :)
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,337
Members
448,568
Latest member
Honeymonster123

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