 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006occurence count
I've got a row representing 31 days in a month. If a worker calls in sick, the hours missed goes in that cell. That could be any number up to 8 hours. That absence may carry into many days. I need to calculate the number of occurences. How many times did they call in sick that month? Assume worker calls in sick: 3rd,4th,5th Assume worker calls in sick: 8th Assume worker calls in sick: 21st,22nd That's 3 occurences. What's the simplest formula to show 3 occurences? thanks! One way: Assume A1:AE1 are days of the month A2:AE2 is where you enter the hours missed (if any) otherwise the cells are empty. Enter this formula in A3: =IF(A2>0,1,"") Enter this formula in B3 and copy across to AE3: =IF(B2="","",IF(AND(B2>0,A2>0),"",MAX($A3:A3)+1)) That will show the number of occurrences. You can put the formulas in an out of sight area of the sheet and then use a MAX() formula to get the occurrences. Biff > I've got a row representing 31 days in a month. > If a worker calls in sick, the hours missed goes in that cell. [quoted text clipped - 14 lines] > > thanks! Even better: =SUMPRODUCT(--(A2:AD2<>""),--(B2:AE2=""))+(AE2<>"") Biff > One way: > [quoted text clipped - 34 lines] >> >> thanks! I have the Day numbers in: E2:AI2 The sick row: E4:AI4 I tried: =SUMPRODUCT(--(E2:AI2<>""),--(E4:AI4<>""))+(AI4<>"") But it counts the totals days off. That's not what I need. I need a count of the "occurrences" of sick calls. Assume sick calls... Days: 1__2__3__4__5__6__7__8__9__10__11__12__13__14__15__16__17__18__19__20__21__22__23__24__25__etc.... Sick: 8__8__4___________8________8____8 The above person had 3 occurrences. I need to account the "groups": 8,8,4 = 1 group... 8 = 1 group... 8,8 = 1 group... TOTAL 3 occurrences. What formula would do that? thanks! > Even better: > [quoted text clipped - 40 lines] >>> >>> thanks! > =SUMPRODUCT(--(A2:AD2<>""),--(B2:AE2=""))+(AE2<>"") > I tried: =SUMPRODUCT(--(E2:AI2<>""),--(E4:AI4<>""))+(AI4<>"") Try this: =SUMPRODUCT(--(E4:AH4<>""),--(F4:AI4=""))+(AI4<>"") Notice how the ranges are "staggered": (E4:AH4<>"") (F4:AI4="") That's needed for this to work! Biff >I have the Day numbers in: E2:AI2 > The sick row: E4:AI4 [quoted text clipped - 57 lines] >>>> >>>> thanks! =SUMPRODUCT(--(E4:AH4<>""),--(F4:AI4=""))+(AI4<>"") Notice how the ranges are "staggered": (E4:AH4<>"") (F4:AI4="") This works! But, could you explain in plain english what this is doing? SUMPRODUCT in the help section is used to sum an array of cells, but the above is all on one row. What do these do "--"? I appreciate the help! thanks! >> =SUMPRODUCT(--(A2:AD2<>""),--(B2:AE2=""))+(AE2<>"") >> I tried: =SUMPRODUCT(--(E2:AI2<>""),--(E4:AI4<>""))+(AI4<>"") [quoted text clipped - 70 lines] >>>>> >>>>> thanks! Even though all the data is in 1 row we've created 2 arrays by staggering the references: A1 B1 C1 ......B1 C1 D1 Basically, all this is doing is testing a series of 2 cells. It tests 1 cell to see if it is not empty then it tests the cell next to it to see if it is empty. Where those 2 conditions are TRUE the "--" converts the TRUE to a 1 and where those 2 conditions are FALSE the "--" converts the FALSE to a 0. Then Sumproduct justs adds up all the 1's and 0's to arrive at the result. I just thought of something. What if a person misses Friday and the following Monday? Is that a single instance or is that 2 instances? If that's considered 2 instances then there's no problem. If that's considered 1 instance then the formula will count that as 2 instances. One way to fix that is to enter something (anything, like a "x") in the cells for Saturday and Sunday. Biff > =SUMPRODUCT(--(E4:AH4<>""),--(F4:AI4=""))+(AI4<>"") > Notice how the ranges are "staggered": (E4:AH4<>"") (F4:AI4="") [quoted text clipped - 78 lines] >>>>>> >>>>>> thanks!
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.