• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of contagious runs

B

bluetaurean

Member
I need to count the number of runs of each product in a range. Each run could span over 1 to 4 days. How can this be done


Proudct A

01-Jan 5000 First Run

02-Jan 15000 First Run

03-Jan

04-Jan

05-Jan 3000 Second Run

06-Jan 12000 Second Run

07-Jan

08-Jan 15000 Third run

09-Jan 15000 Third run

10-Jan 3000 Third run


No of days 7

No of runs 3
 
can you explain your data table? I don't know what I am looking at and couldn't understand how your intended answers are created from the data table.
 
No of days:

=COUNTA(B:B)


For runs, can you use a helper column with this formula:

=IF(AND(B2<>"",B3=""),"X","")


Then, you count of runs is:

=COUNTIF(C:C,"X")
 
HI Luke,


Thanks for your response. the above formula is not working. Let me try to explain my question.


The first column is a series of dates, with no gaps. i.e 1st Jan to 31st Jan (31 rows with no missing dates)


The second column contains the quantity of a particular product produced on a particular date.


The issue : A production could commence on the 1st day and be completed on the 2nd or 3rd or 4th day. i.e one run could last for 1 or 2 or more days. Then there is a gap of a few days before it is run again.


I need to count the number of days the product has been produed which is straightforward and also the number of runs of the product for which i am not able to find a solution. So it could be that the product was produced for 10 days in the month but only 3 or 4 run each lasting 2 to 3 days.
 
Hi ,


Try the following :


Assume your data range is from K4 through K34 ; in your helper column , say column L , from L4 through L34 , have the following formula :


=IF(AND(ISBLANK(K4),NOT(ISBLANK(K3))),1,0)


For counting the number of runs , have the following formula in any unused cell :


=SUM(L4:L34)+NOT(ISBLANK(K34))


Narayan
 
Thanks Luke, the formulas did work fine. With the same data set i need to generate more statistics like the maximum and minimum gaps between two runs ("x"). Please help
 
You must log in or register to reply here.
Back
Top