Need to count consecutive days

[Ronal_bal]

I need to count consecutive days and output the no.of.days in separate column. I've attached a screenshot

[IraWatt]

Hey @Ronal_bal,

Think all you need is a multi row formula. Should the 3 row of your expected output not be 1 rather then 0?

[Ronal_bal]

So, whenever the value in column X-vales is zero. days should be counted as zero. its like carrying some stuff with you and counting the no of consecutive days it was with you. And then starting again from zero

[IraWatt]

@Ronal_bal Here is how I would achieve this:

Any questions or issues please ask :)
HTH!
Ira

[IraWatt · Accepted solution]

@Ronal_bal Just adjusted it so on X-value = 0 then put 0:

[Ronal_bal]

Hi Thanks for sharing the solution.

But one thing is that I need to calculate the no.of.days from X-value =0 until it becomes the column x-value becomes zero again

[IraWatt]

Hey @Ronal_bal, sorry I only realised that after posting. I've updated the workflow and I think it does this now. Let me know if not and I'll adjust it :)

[Ronal_bal]

I just found one block. what if the first record has a value instead of zero? How will you count it?

Current formula struck with NULL whenever a x-Column starts with a value

[IraWatt]

@Ronal_bal can you give an example data set which isn't working? The first X value in your example isn't 0 and it seems fine:

[Ronal_bal]

Sorry, my bad. Got it