| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
uneven ranges and coding
I have a lengthy file of absence data and I am trying to calculate the lenght
of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Hi Lori,
you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Sorry... I should have stated that the date of the SICK1ST code is day 1 of
absence but day 2 through end of absence is coded as SICKCON. I need to be able to pick up the date of the SICK1ST and count up to the end of the SICKCON and then if there is another SICK1ST for the same EE it adds separately. In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a 1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3 would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence. I have this to go through for approx 2,000 employees and my datafile is 30,000 lines of data which is why I want to try to automate -- Lori16 "Eduardo" wrote: Hi Lori, you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Hi Lori,
here is another option you need to create a column which will populate just the month so you enter this formula +trim(B,3) The above will bring JAN in the case of JAN 1 / 06. Then create a table as follow with the people name in a column and the month of the year lets say in row 2 JAN FEB Etc Then enter the formula as follow in the first column and copy all the way up to DEC =+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2)) What you are doing with the above is counting all the sick days by month what will give you the results expected "Lori" wrote: Sorry... I should have stated that the date of the SICK1ST code is day 1 of absence but day 2 through end of absence is coded as SICKCON. I need to be able to pick up the date of the SICK1ST and count up to the end of the SICKCON and then if there is another SICK1ST for the same EE it adds separately. In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a 1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3 would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence. I have this to go through for approx 2,000 employees and my datafile is 30,000 lines of data which is why I want to try to automate -- Lori16 "Eduardo" wrote: Hi Lori, you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Perhaps you could remove the unnecessary + signs? I guess that you're a
Lotus addict? = is the Excel way of starting a formula; it doesn't need =+ -- David Biddulph "Eduardo" wrote in message ... Hi Lori, here is another option you need to create a column which will populate just the month so you enter this formula +trim(B,3) The above will bring JAN in the case of JAN 1 / 06. Then create a table as follow with the people name in a column and the month of the year lets say in row 2 JAN FEB Etc Then enter the formula as follow in the first column and copy all the way up to DEC =+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2)) What you are doing with the above is counting all the sick days by month what will give you the results expected "Lori" wrote: Sorry... I should have stated that the date of the SICK1ST code is day 1 of absence but day 2 through end of absence is coded as SICKCON. I need to be able to pick up the date of the SICK1ST and count up to the end of the SICKCON and then if there is another SICK1ST for the same EE it adds separately. In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a 1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3 would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence. I have this to go through for approx 2,000 employees and my datafile is 30,000 lines of data which is why I want to try to automate -- Lori16 "Eduardo" wrote: Hi Lori, you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Hi David, Thanks I know that it's not necessary I didn't realize if you see my next post the + is not there. By the way the last time I used Lotus was in 1986. a long long time ago "David Biddulph" wrote: Perhaps you could remove the unnecessary + signs? I guess that you're a Lotus addict? = is the Excel way of starting a formula; it doesn't need =+ -- David Biddulph "Eduardo" wrote in message ... Hi Lori, here is another option you need to create a column which will populate just the month so you enter this formula +trim(B,3) The above will bring JAN in the case of JAN 1 / 06. Then create a table as follow with the people name in a column and the month of the year lets say in row 2 JAN FEB Etc Then enter the formula as follow in the first column and copy all the way up to DEC =+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2)) What you are doing with the above is counting all the sick days by month what will give you the results expected "Lori" wrote: Sorry... I should have stated that the date of the SICK1ST code is day 1 of absence but day 2 through end of absence is coded as SICKCON. I need to be able to pick up the date of the SICK1ST and count up to the end of the SICKCON and then if there is another SICK1ST for the same EE it adds separately. In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a 1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3 would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence. I have this to go through for approx 2,000 employees and my datafile is 30,000 lines of data which is why I want to try to automate -- Lori16 "Eduardo" wrote: Hi Lori, you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
uneven ranges and coding
Hi Lori,
Enter this formula instead of the one sent before in order to avoid having "0" everywhere =IF(+SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2))=0,"",SUMPRODUCT(--($A:$A=$F3),--($D:$D=G$2))) "Lori" wrote: Sorry... I should have stated that the date of the SICK1ST code is day 1 of absence but day 2 through end of absence is coded as SICKCON. I need to be able to pick up the date of the SICK1ST and count up to the end of the SICKCON and then if there is another SICK1ST for the same EE it adds separately. In my example, EE1 would have Jan 1, 2, 3 as a 3 day absence and Mar 1 as a 1 day absence. EE2 would be Feb 12 as a 1 day and Feb 27 as a 1 day, EE3 would be June 12 through 17 as 7 day absence and Sep 1 as a 1 day absence and EE4 would be Mar 3 as 2 day absence and Apr 12 as 1 day absence. I have this to go through for approx 2,000 employees and my datafile is 30,000 lines of data which is why I want to try to automate -- Lori16 "Eduardo" wrote: Hi Lori, you can have a summary by type of sickness and then totalize it for example you prepare a summary by employee and type of sickness D E F Employee Sickcon Sick1st EE1 EE2 Then enter the formula as follow =SUMPRODUCT(--(A:A=D4),--(c:c=e3)) I Assumed that 1st employee name is in D4 you can adapt the formula to your needs "Lori" wrote: I have a lengthy file of absence data and I am trying to calculate the lenght of absence (ie number of days) but can't seem to figure this out. I can easily figure out the 1 day absences but I can't figure out how to do a formula around the different amount of entries for each person, different coding for each person, etc. A B C EE1 JAN 1/06 SICK1ST EE1 JAN 2/06 SICKCON EE1 JAN 3/06 SICKCON EE1 MAR 1/06 SICK1ST EE2 FEB 12/06 SICK1ST EE2 FEB 27/06 SICK1ST EE3 JUN 12/06 SICK1ST EE3 JUN 13/06 SICKCON EE3 JUN 14/06 SICKCON EE3 JUN 15/06 SICKCON EE3 JUN 16/06 SICKCON EE3 JUN 17/06 SICKCON EE3 SEP 1/06 SICK1ST EE4 MAR 3/06 SICK1ST EE4 MAR 4/06 SICKCON EE4 APR 12/06 SICK1ST - Lori16 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Font looks uneven in spreadsheet | Excel Discussion (Misc queries) | |||
| lookup with uneven columns | Excel Worksheet Functions | |||
| Counting and colour coding ranges of occurances | New Users to Excel | |||
| Sort a list with uneven row information | Excel Worksheet Functions | |||
| An uneven time-increment | Charts and Charting in Excel | |||
Hybrid Mode
