Equation got to complicated for me

[dmheller]

All,
Here is where I started.
=1440-SUM(IF(ISNUMBER('Line 3'!$D$4:$D$44903),IF(INT('Line 3'!$D$4:$D$44903)=INT('Line 3 calc Sheet'!B18),--(('Line 3 data copy'!$E$4:$E$44703>220)*ISNUMBER('Line 3 data copy'!$E$4:$E$44703)+('Line 3 data copy'!$F$4:$F$44703>220)*ISNUMBER('Line 3 data copy'!$F$4:$F$44703)+ ('Line 3 data copy'!$G$4:$G$44703>220)*ISNUMBER('Line 3 data copy'!$G$4:$G$44703)+('Line 3 data copy'!$H$4:$H$44703>220)*ISNUMBER('Line 3 data copy'!$H$4:$H$44703)>0))))
Now this isn't to bad as it looks up a date then finds the date and sums it in the date. So if any of E, F, G, or H are above 220 in that date, it sums that total and that's what I wont this equation to do and it works. Now I want to find how many times in that day the or state me changes but only changes from >220 to <220. I do not care if it goes from <220 to over 220. I don't know if that makes any since but I might have to throw in a dummy column to make this work. Let me know if you have any ideas.
thanks

 

[oldbrewer]

can we see some raw (pretend) data please ?

 

[dmheller]

Here would be an example, at 8:26 it goes from being over 220 to less than 220.

10/16/17 8:17:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:18:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:19:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:20:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:21:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:22:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:23:00 AM	233.9905	238.012	243.9955	249.9958
10/16/17 8:24:00 AM	233.9905	237.9968	243.9955	249.9958
10/16/17 8:25:00 AM	233.9905	237.9968	243.9955	249.9958
10/16/17 8:26:00 AM	182.8891	184.1762	200.7086	194.502
10/16/17 8:27:00 AM	142.2131	155.6269	167.3712	164.9063
10/16/17 8:28:00 AM	125.5814	137.6054	150.6807	148.9837
10/16/17 8:29:00 AM	112.5205	124.2124	139.1186	136.6748
10/16/17 8:30:00 AM	102.2394	113.7578	129.8345	126.7011
10/16/17 8:31:00 AM	94.2112	104.719	121.7752	118.3883
10/16/17 8:32:00 AM	87.4529	97.67177	114.8455	110.6334
10/16/17 8:33:00 AM	81.58927	91.53004	108.5879	103.9354
10/16/17 8:34:00 AM	76.72224	86.50076	102.956	97.81688
10/16/17 8:35:00 AM	72.4254	81.88866	97.53431	92.57902

<tbody>
</tbody><colgroup><col><col span="4"></colgroup>

 

[oldbrewer]

so if any one of the 4 columns goes from > 220 to < 220 OR < 220 to > 220 then you want to add all 4 numbers and do the same for any date_time where this happens ?

 

[dmheller]

No,
So currently, the way the equation works all 4 columns have to fall below 220 and this is also one min data. So with that said, I want any time all columns fall below 220 after the previous min were one was above 220. This I want to sum the number of times that happens in a given day.

 

[oldbrewer]

I think I understand now. Detect when all 4 columns fall below 220 for the first time. Then detect when all 4 columns rise above 220........and so on. Count the number of occurrencies per day.

 

[oldbrewer]

date_time

param1

param2

param3

param4

01/10/2017 01:00

9

11

9

9

01/10/2017 03:00

9

11

9

9

N

N

N

N

NNNN

01/10/2017 05:00

11

11

9

11

U

N

N

U

UNNU

01/10/2017 07:00

11

11

11

11

N

N

U

N

NNUN

01/10/2017 09:00

9

9

9

9

D

D

D

D

DDDD

DOWN

01/10/2017 11:00

9

11

11

9

N

U

U

N

NUUN

01/10/2017 13:00

11

11

9

11

U

N

D

U

UNDU

01/10/2017 15:00

11

9

9

11

N

D

N

N

NDNN

01/10/2017 17:00

11

9

11

11

N

N

U

N

NNUN

01/10/2017 19:00

9

9

9

9

D

N

D

D

DNDD

01/10/2017 21:00

11

11

11

11

U

U

U

U

UUUU

UP

01/10/2017 23:00

9

9

9

11

D

D

D

N

DDDN

02/10/2017 01:00

11

11

11

11

U

U

U

N

UUUN

02/10/2017 03:00

9

9

9

9

D

D

D

D

DDDD

DOWN

02/10/2017 05:00

11

9

11

9

U

N

U

N

UNUN

02/10/2017 07:00

9

9

9

9

D

N

D

N

DNDN

02/10/2017 09:00

11

11

11

11

U

U

U

U

UUUU

UP

02/10/2017 11:00

9

9

9

9

D

D

D

D

DDDD

DOWN

02/10/2017 13:00

11

11

9

11

U

U

N

U

UUNU

02/10/2017 15:00

11

9

9

11

N

D

N

N

NDNN

02/10/2017 17:00

11

11

11

11

N

U

U

N

NUUN

02/10/2017 19:00

9

9

9

9

D

D

D

D

DDDD

DOWN

02/10/2017 21:00

11

11

9

9

U

U

N

N

UUNN

02/10/2017 23:00

9

9

9

9

D

D

N

N

DDNN

03/10/2017 01:00

11

11

11

11

U

U

U

U

UUUU

UP

03/10/2017 03:00

9

9

9

11

D

D

D

N

DDDN

03/10/2017 05:00

9

9

11

9

N

N

U

D

NNUD

03/10/2017 07:00

9

9

9

9

N

N

D

N

NNDN

03/10/2017 09:00

11

11

11

11

U

U

U

U

UUUU

UP

03/10/2017 11:00

9

11

9

11

D

N

D

N

DNDN

03/10/2017 13:00

11

11

11

11

U

N

U

N

UNUN

03/10/2017 15:00

9

9

9

9

D

D

D

D

DDDD

DOWN

03/10/2017 17:00

11

11

11

11

U

U

U

U

UUUU

UP

03/10/2017 19:00

9

9

9

9

D

D

D

D

DDDD

DOWN

03/10/2017 21:00

9

11

11

11

N

U

U

U

NUUU

03/10/2017 23:00

9

11

11

11

N

N

N

N

NNNN

UP

DOWN

01/10/2017

1

1

I think the best way is to use some helper columns

02/10/2017

1

3

to find out whether each column is up or down

03/10/2017

3

2

then concatenate the outcome

04/10/2017

and detect instances of UUUU and DDDD

05/10/2017

06/10/2017

an easy sumproduct table

does the daily analysis

<colgroup><col><col span="4"><col><col span="10"></colgroup><tbody>
</tbody>

 

[dmheller]

Thank you, that is the way I went, I had to add helper columns. Then just wroth the logic into the column then I did a sumif for the dates based on the helper columns. Thanks for your help and glad to see I wasn't going insane over something I couldn't do.