counting consecutive days of loss while ignoring zero values

[leslieann]

Hello,

I am looking for a formula that will count consecutive cells of negative numbers in one column while ignoring zero values....please help!

Thank you!

[AliGW]

Welcome to the forum!

You need to use the COUNTIF function and set the criterion to less than 0.

[Zer0Cool]

Can you post a sample of your data.

COUNTIF will do it for you only if you want to count all of the cells that meet your criteria in a range. Your question specifically stats "consecutive" which complicates matters beyond a simple COUNTIF. So do you actually mean counting consecutive according to some criteria within the column or where you just stating that your data set as a whole is consecutive?

[leslieann]

Hello,

I am trying to count the consecutive days of loss but the complicated part are the zero values for weekends where data is not entered. In the example below, I have five consecutive days of loss, but all formulas I try return a value of 4.

14,677
(12,872)
(40)
(29)
(18)
-
-
(55)
26
37
48

[FlameRetired]

Try array entering this formula in C1 as below. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

A little comment might be in order here. In order to make it work this formula "re-purposes" the Holidays argument of NETWORKDAYS to be the days corresponding with amounts < 0.

	A	B	C	D
1	1/8/2018	14,677	5	In C1: {=SUM(IF(NETWORKDAYS(WORKDAY(A1-1,ROW(1:11)),WORKDAY(A1-1,ROW(1:11)),N(IF(1,IF(B1:B11<0,A1:A11))))=0,1))}
2	1/9/2018	-12,872
3	1/10/2018	-40
4	1/11/2018	-29
5	1/12/2018	-18
6	1/13/2018	0
7	1/14/2018	0
8	1/15/2018	-55
9	1/16/2018	26
10	1/17/2018	37
11	1/18/2018	48

[leslieann]

Thank you, I tried this and it came back with an error in the formula, could it be this part...,ROW(1:11)),N(IF(1, (I'm not sure what the "N" is for?)

[pjwhitfield]

Did you enter it as an array formula? ie as Flame said by holding down Ctrl+Shift then pressing enter.

So you type in your formula then hold Ctrl+Shift and press enter, you should see the formula surrounded by { and }

[leslieann]

Yes, I did, it shows an error in the formula.

[AliGW]

What error message do you get?

[leslieann]

It simply states there is an error in the formula....I am thinking it is related to the "N" as I believe there is something missing there

[FlameRetired]

The "N" is an Excel function. With "IF(1" nested inside it coerces the array "IF(B1:B11<0,A1:A11)". Occasionally this is a necessary step in some array formulas. Range and array coercion are very common issues among the dating functions and WORKDAY is one of those functions.

Are you committing the formula from edit mode with Ctrl + Shift + Enter ... instead of with just Enter?

See the attached. With C1 the active cell note the {} curly braces around the formula in the formula bar. You don't type those in yourself. I you do you will get an error. Excel puts those there when you commit with Ctrl + Shift + Enter.

[FlameRetired]

By-the-way upon further review this formula returns 5 and is simpler in this case.

Please Login or Register  to view this content.

[FlameRetired]

Upon more further review it occurs to me neither of those formulas will do what you want. For example if -55 were on a Tuesday I am guessing you would expect the return to be 4. These both still return 5.

This formula will return the count of contiguous non-weekend days of < 0 in column B. It is also array entered, but is limited to returning the count of only one contiguous "run". Somehow I suspect there is more to the problem than this. Is that correct?

Array entered.

Please Login or Register  to view this content.

[Zer0Cool]

I may be able to adjust the formula from this post to accomplish what you want. Ill give it a shot and if all goes well post back here.

Is it possible for there to be multiple consecutive sets of negatives/zero's?

ie:
2
4
-5
-2
-3
0
-1
6
7
-3
-3
2

If the above is possible, what do you want to return? Longest consecutive set?

Presuming the above is possible (should still work regardless, but if its only 1 consecutive range it can be done much easier) this should do it. I added to the sample posted by FlameRetired to have multiple consecutive ranges of varying frequency. Seems to get it correct (array entered)

Please Login or Register  to view this content.

See attached

[leslieann]

For some reason I am not able to open the attachments?!

There can be multiple, consecutive sets of negative numbers, the return I am looking for is, if there are 5 consecutive days of loss in column H (Inventory Variance), I simply need it to return a "yes" see below :




Closing Inventory
Day Opening Purchases Cardlock Total Book Physical Variance
Inventory Sales Sales Inventory Inventory (+/-)
1-Jan-18 12 50 1,918 1,918 (1,856) 12,821 14,677
2-Jan-18 12,821 51 (1) (1) 12,873 1 (12,872)
3-Jan-18 1 52 11 11 42 2 (40)
4-Jan-18 2 53 23 23 32 3 (29)
5-Jan-18 3 54 35 35 22 4 (18)
6-Jan-18 4 - - - 4 5 -
7-Jan-18 5 - - - 5 6 -
8-Jan-18 6 57 1 1 62 7 (55)
9-Jan-18 7 58 83 83 (18) 8 26
10-Jan-18 8 59 95 95 (28) 9 37

[FlameRetired]

leslieann they upload and open fine at my end. What message(s) pop up when you try?

I don't understand your latest data layout. It is not the same as post #4. There are multiple negative figures each day. Please post an example of expected results. Better yet please upload a workbook with a BEFORE section of data source and layout and an AFTER section of results including layout. The mission is becoming less clear to me.

If you are not familiar with how to do this (save yourself some grief ... the "paperclip" icon has not worked for some time):


To attach a file to your post,

• be sure to desensitize the data
• click “Go Advanced” (next to Post Quick Reply – bottom right),
• scroll down until you see “Manage Attachments”, click that,
• click “Browse”.
• select your file(s)
• click “Upload”
• click “Close window”
• click “Submit Reply”

The file name will appear at the bottom of your reply.

[FlameRetired]

In the meantime I took a guess at what you wanted.

Array entered in B1 and filled across.

Please Login or Register  to view this content.

	A	B	C	D	E	F	G	H	I
1		0	0	1	1	2	0	5
2	1-Jan-18	12	50	1,918	1,918	(1,856)	12,821	14,677	Workday
3	2-Jan-18	12,821	51	(1)	(1)	12,873	1	(12,872)	Workday
4	3-Jan-18	1	52	11	11	42	2	(40)	Workday
5	4-Jan-18	2	53	23	23	32	3	(29)	Workday
6	5-Jan-18	3	54	35	35	22	4	(18)	Workday
7	6-Jan-18	4	-	-	-	4	5	-	Weekend
8	7-Jan-18	5	-	-	-	5	6	-	Weekend
9	8-Jan-18	6	57	1	1	62	7	(55)	Workday
10	9-Jan-18	7	58	83	83	(18)	8	26	Workday
11	10-Jan-18	8	59	95	95	(28)	9	37	Workday

[daddylonglegs]

=MAX(FREQUENCY(IF($B$1:$B$24<=0,ROW($B$1:$B$24)),IF($B$1:$B$24>0,ROW($B$1:$B$24))))

This get's a result of 7 for the example given because it also counts the two zeroes. My understanding is that the zeroes should be ignored, i.e. not counted but also not treated as breaks in the runs of negative numbers. Easy fix to change that, just change <= 0 to < 0, i.e. this version adjusted to reference column H and with added IF function

=IF(MAX(FREQUENCY(IF($H$2:$H$10<0,ROW($H$2:$H$10)),IF($H$2:$H$10>0,ROW($H$2:$H$10))))>=5,"Yes")

confirm with CTRL+SHIFT+ENTER

[Zer0Cool]

Ah @daddylonglegs seems to have it. I was under the impression we wanted to count 0's, but reading it seems only considering 0's as not breaking the consecutive string of negatives while only counting negatives. Good catch.

[leslieann]

Daddylonglegs....Thank you so much!! that one did the trick!! And, thank you to everyone who responded to this thread.