Determine frequency over time

Hi all,

I need to show increasing absence during a set time, but with everything I've tried, I get a count. What I want to show is if within a specific timeframe a person has increasing absences (which is a trend). The attendance schedule is weekly and shows "A" for absent and "P" for present. I have converted this to "0" for absent and "1" for present.

Ideally, my end-result would be an "up" arrow if the frequency of zeroes increases over the time span.

For example

1111111111111111111 would not trigger anything, because this person is always present
1010101010101010101 would also not trigger anything, because this person has a regular attendance pattern
1010010100001000000 would trigger something because the absence intervals get longer and longer over time

I've tried several formulas, but I'm not good at statistics, so I'm not getting the results I want.

Anyone have a solution for this problem? Thanks in advance for your help!!

Hi Gertrud and welcome.

Not sure how your data is arranged. It's often good to attach a file to help us understand how your data is structured.

I've made some assumptions and think this might get you heading in the right direction.

The main part of this problem is to understand what logic you will use to determine if the incidents of sick leave are increasing or decreasing because I imagine they real life examples aren't as neat as your examples above. I thought the best way to determine this would be to determine an array of absence periods first. For your 3rd example above the absence array would be 1, 2,1,4,6 because the person took one day then 2 days then 1 day then 4 days then 6 days. If you plotted this the trend line of best fit would be positive showing an increase over time. As such the formula below uses the line of best fit and uses the slope to determine an increase in absence or a decrease. A positive answer will mean an increase in absences, a negative will indicate a decrease

Please Login or Register  to view this content.

Its an array formula so you'll need to enter it with control + shift + enter. You'll know if you get it right because it will be surrounded in squiggly lines.

I have assumed for this purpose that your array of ones and zeros are in cell A1 and are a single text field. If this isn't the case then we'll have to work out another way of resolving this

I keep trying to reply, but it's not showing up in the thread, so I'm trying again... Thank you so much for responding! I didn't realize that I could attach a file to my post. I've now added one to the original inquiry. If you have the time to take a look, that would be very much appreciated.