Frequency Formula

[benntw]

I have a date range in Row 4 that looks back dynamically 21 days. The formula works off of today's date -21 then gives me each day after that. Currently the date range shows from 9/30 to 10/21 from left to right. The right side is closer to the current date. What I am trying to do is have a formula to tell me how many days in a row an employee has worked. I have tried the following formula " {=MAX(FREQUENCY(IF(D5:Y5>0,COLUMN(D5:Y5)),IF(D5:Y5 = 0, COLUMN(D5:Y5))))} . This works if the max number is after the days off. But if someone worked 10 days in a row and then took 2 days off and came back for 7 days it shows the results for 10. What I am looking for is a formula to show me how many cells have values greater than 0. If there is no value in a cell I need it to count the next set of values until there is no value in a cell. That should give me how many days in a row after someone has had a day off. I hope this makes sense and someone can help me out. Thank you.

 

[pgc01]

But if someone worked 10 days in a row and then took 2 days off and came back for 7 days it shows the results for 10. What I am looking for is a formula to show me how many cells have values greater than 0. If there is no value in a cell I need it to count the next set of values until there is no value in a cell.

Not clear to me what you want.

You did not post the result you expect so I'm going to guess.
Do you mean you want how many days in a row since today going backwards?

For ex., with "w" for a working day and "o" for an off day

wwwoowwwwww (result 6 because 6 days since off ? )
wwwwwoowww (result 3 because 3 days since off ? )
wwwwowwooo (result ???. Now off, what should be counted? )

Please post some examples with the result you expect and the logic you used.

 

[jasonb75]

I'm not entirely clear on what is needed either, see if this helps

{=IFERROR(LOOKUP(2,1/(1/FREQUENCY(IF(D5:Y5>0,COLUMN(D5:Y5)),IF(D5:Y5 = 0, COLUMN(D5:Y5))))),0)}

Remember, MAX will find the highest value, not the last value.

 

[benntw]

Thank you. Appreciate the help.