Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
We have to track our day in 15 minutes increments, some which are billiable
or non-billable. I have set up the fomula that calculates the 15 minute increments for each billable / non-billable. There are 4 billable and 4 non-billable categories, numbered 1 through 8 and these numbers are placed in 1 column which corrresponds with each time. What I can't figure out is how to "pull" the data from the cells in each of the categories to one cell representing each categorie. Each day will be different with different times for each category, so nothing remains static. Suggestions would be greatly appreciated. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
=SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200)
where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the amounts. You can put the test values in cells and check against these, better in a table. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... We have to track our day in 15 minutes increments, some which are billiable or non-billable. I have set up the fomula that calculates the 15 minute increments for each billable / non-billable. There are 4 billable and 4 non-billable categories, numbered 1 through 8 and these numbers are placed in 1 column which corrresponds with each time. What I can't figure out is how to "pull" the data from the cells in each of the categories to one cell representing each categorie. Each day will be different with different times for each category, so nothing remains static. Suggestions would be greatly appreciated. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
Below is the table that I'm using. (hope it posts this legible) The formula I
need is that everything that is a 1 in Activity, will add the those 15/min increments up in a separate cell; everthing that is a 2, etc. Again each month will be different. Date Activity Begin Time End Time "15/min. Increments" 1/1/08 1 7:00 15:00 32.00 1/1/08 2 15:00 17:00 8.00 1/1/08 1 17:00 17:30 2.00 1/1/08 8 17:30 17:55 1.67 0.00 0.00 0.00 Formula for this column =(D2-C2+(D2<C2))*24/0.25 I couldn't make your formula work. "Bob Phillips" wrote: =SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200) where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the amounts. You can put the test values in cells and check against these, better in a table. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... We have to track our day in 15 minutes increments, some which are billiable or non-billable. I have set up the fomula that calculates the 15 minute increments for each billable / non-billable. There are 4 billable and 4 non-billable categories, numbered 1 through 8 and these numbers are placed in 1 column which corrresponds with each time. What I can't figure out is how to "pull" the data from the cells in each of the categories to one cell representing each categorie. Each day will be different with different times for each category, so nothing remains static. Suggestions would be greatly appreciated. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
Linda,
If you put the activities in M1,N1,O1, etc., then in M2 add =SUMPRODUCT(--($B$2:$B$5=M$1),($D$2:$D$5-$C$2:$C$5))*24*4 and copy across. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... Below is the table that I'm using. (hope it posts this legible) The formula I need is that everything that is a 1 in Activity, will add the those 15/min increments up in a separate cell; everthing that is a 2, etc. Again each month will be different. Date Activity Begin Time End Time "15/min. Increments" 1/1/08 1 7:00 15:00 32.00 1/1/08 2 15:00 17:00 8.00 1/1/08 1 17:00 17:30 2.00 1/1/08 8 17:30 17:55 1.67 0.00 0.00 0.00 Formula for this column =(D2-C2+(D2<C2))*24/0.25 I couldn't make your formula work. "Bob Phillips" wrote: =SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200) where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the amounts. You can put the test values in cells and check against these, better in a table. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... We have to track our day in 15 minutes increments, some which are billiable or non-billable. I have set up the fomula that calculates the 15 minute increments for each billable / non-billable. There are 4 billable and 4 non-billable categories, numbered 1 through 8 and these numbers are placed in 1 column which corrresponds with each time. What I can't figure out is how to "pull" the data from the cells in each of the categories to one cell representing each categorie. Each day will be different with different times for each category, so nothing remains static. Suggestions would be greatly appreciated. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
Your formula worked great. Exactly what I wanted it to do. Thank you so
very much. "Bob Phillips" wrote: Linda, If you put the activities in M1,N1,O1, etc., then in M2 add =SUMPRODUCT(--($B$2:$B$5=M$1),($D$2:$D$5-$C$2:$C$5))*24*4 and copy across. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... Below is the table that I'm using. (hope it posts this legible) The formula I need is that everything that is a 1 in Activity, will add the those 15/min increments up in a separate cell; everthing that is a 2, etc. Again each month will be different. Date Activity Begin Time End Time "15/min. Increments" 1/1/08 1 7:00 15:00 32.00 1/1/08 2 15:00 17:00 8.00 1/1/08 1 17:00 17:30 2.00 1/1/08 8 17:30 17:55 1.67 0.00 0.00 0.00 Formula for this column =(D2-C2+(D2<C2))*24/0.25 I couldn't make your formula work. "Bob Phillips" wrote: =SUMPRODUCT(--($B$2:$B$200=--"10:00:00"),--($B$2:$B$200<--"10:15:00"),--($C$2:$C$200=1),$D$2:$D$200) where B2:b200 is the times, C2:C200 is the categories, D2:D200 is the amounts. You can put the test values in cells and check against these, better in a table. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... We have to track our day in 15 minutes increments, some which are billiable or non-billable. I have set up the fomula that calculates the 15 minute increments for each billable / non-billable. There are 4 billable and 4 non-billable categories, numbered 1 through 8 and these numbers are placed in 1 column which corrresponds with each time. What I can't figure out is how to "pull" the data from the cells in each of the categories to one cell representing each categorie. Each day will be different with different times for each category, so nothing remains static. Suggestions would be greatly appreciated. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
I'm back. The formula worked great for the first 4 rows that you wrote the
formula for. But when I added new numbers (1,2, or 3 thru 8) to the activity column and the times in the begin and end and tried to expand the array to include the new numbers and times, I received "0:00 or ##. Any suggestions? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
formula for Excel
Linda,
Use this =SUMPRODUCT(--(OFFSET($B$2,0,0,COUNTA($A:$A)-1,1)=M$1),(OFFSET($D$2,0,0,COUNTA($A:$A)-1,1)-OFFSET($C$2,0,0,COUNTA($A:$A)-1,1)))*24*4 You can then add more rows without changing the formula. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Linda" wrote in message ... I'm back. The formula worked great for the first 4 rows that you wrote the formula for. But when I added new numbers (1,2, or 3 thru 8) to the activity column and the times in the begin and end and tried to expand the array to include the new numbers and times, I received "0:00 or ##. Any suggestions? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
When I enter a formula, Excel shows the formula not the results | Excel Worksheet Functions | |||
Excel 2002 Formula: Urgent Conditional Formula Required Right Away - if possible | Excel Discussion (Misc queries) | |||
Build excel formula using field values as text in the formula | Excel Worksheet Functions | |||
Excel 2002 formula displayed not value formula option not checked | Excel Worksheet Functions | |||
Converting an Excel formula to an Access query formula | Excel Discussion (Misc queries) |