Frequency Formula

benntw

Board Regular
Joined
Feb 17, 2014
Messages
222
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top