Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
I have a worksheet that lists all the members of our church. Across the top
are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
Easily achieved with VBA. Assuming that the dates start in column C and the
absence count is in column B Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long Dim nCount As Long Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column 2 Then With Target For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column If Cells(.Row, i).Value = "a" Then nCount = nCount + 1 Else If nCount nMax Then nMax = nCount End If nCount = 0 End If Next i Me.Cells(.Row, "B").Value = nMax End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jerry37917" wrote in message ... I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
Hi Jerry
You could do this with Conditional Formatting Assuming your data starts in B2, then the earliest value that could have three consecutive "A"'s is D2. Also assuming you have 100 rows with data Highlight D2:BB101 FormatConditional Formattinguse dropdown to select Formual is and in the whit epane type =SUMPRODUCT(--(B2:D2="A"))=3 Choose Format and select either a coloured Font or Red or Background of Red Click the Add button and repeat the above but set the last part of formula =2, and a format of Yellow Click Add button, change last part of formula to =1 and set Format to Green -- Regards Roger Govier jerry37917 wrote: I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
Thank you very much. Your code wasn't exactly what I was looking for, but it
gave me what I needed. I was able to modify it slighly and achieve the count that I was wanting. I am relatively new to Excel and didn't even know this was possible. Wow! I didn't realize just how powerful Excel really is. Thanks again. Jerry "Bob Phillips" wrote: Easily achieved with VBA. Assuming that the dates start in column C and the absence count is in column B Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long Dim nCount As Long Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column 2 Then With Target For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column If Cells(.Row, i).Value = "a" Then nCount = nCount + 1 Else If nCount nMax Then nMax = nCount End If nCount = 0 End If Next i Me.Cells(.Row, "B").Value = nMax End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jerry37917" wrote in message ... I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
That is just scratching the surface <bg
-- HTH Bob Phillips (remove nothere from email address if mailing direct) "jerry37917" wrote in message ... Thank you very much. Your code wasn't exactly what I was looking for, but it gave me what I needed. I was able to modify it slighly and achieve the count that I was wanting. I am relatively new to Excel and didn't even know this was possible. Wow! I didn't realize just how powerful Excel really is. Thanks again. Jerry "Bob Phillips" wrote: Easily achieved with VBA. Assuming that the dates start in column C and the absence count is in column B Private Sub Worksheet_Change(ByVal Target As Range) Dim i As Long Dim nCount As Long Dim nMax As Long On Error GoTo ws_exit: Application.EnableEvents = False If Target.Column 2 Then With Target For i = 3 To Me.Cells(1, Columns.Count).End(xlToLeft).Column If Cells(.Row, i).Value = "a" Then nCount = nCount + 1 Else If nCount nMax Then nMax = nCount End If nCount = 0 End If Next i Me.Cells(.Row, "B").Value = nMax End With End If ws_exit: Application.EnableEvents = True End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jerry37917" wrote in message ... I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
Maybe...
=IF(COUNTIF(B1:BA1,"P"),COUNTIF(INDEX(B1:BA1,MATCH (2,1/(B1:BA1="P"))):BA1 ,"A"),COUNTIF(B1:BA1,"A")) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly. Hope this helps! In article , "jerry37917" wrote: I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting consecutive absences
Domenic,
I had a similar issue and just tried your solution. It worked perfect! I'm trying to read through the function to try to understand what you actually did. Haven't quite figured it out yet, but it works anyway! Thanks again. "Domenic" wrote: Maybe... =IF(COUNTIF(B1:BA1,"P"),COUNTIF(INDEX(B1:BA1,MATCH (2,1/(B1:BA1="P"))):BA1 ,"A"),COUNTIF(B1:BA1,"A")) ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the range accordingly. Hope this helps! In article , "jerry37917" wrote: I have a worksheet that lists all the members of our church. Across the top are all the Sundays in the year. Next to each person's name, I am filling the sheet with either an A (Absent) or P (present). To the far left, I have a column called "number of consecutive absences". What I need is a formula to count the number of consecutive absences from the most reason Sunday. Something like this: John Smith A P A A P A A A 3 In the example John has missed three consecutive Sundays. If he is present at the next meeting, the number would reset to 0. If he is absent, it would then be 4. I don't need to count the total number of absences, just the number of absences since the last attendance. The purpose is to help our outreach teams keep in contact. When a person is absent, we want to send a card, but when a person is absent twice in a row we want to make a call...three times...a visit...and so on. This worksheet will make it a lot easier to keep track of what the teams should be doing. Thanks in advance for your help. Jerry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting consecutive periods | Excel Worksheet Functions | |||
Count Intervals of 2 Consecutive Values in same Row and Return Count across Row | Excel Worksheet Functions | |||
adding three consecutive columns | Excel Worksheet Functions | |||
Counting number of consecutive zeros at the end of a list | Excel Discussion (Misc queries) | |||
Return Consecutive Values - Pairs | Excel Worksheet Functions |