Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum
% in D:D. Can this be done? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
Do you mean all dates in column B being in the same week?
-- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
Your question is not entirely clear. Are you asking to see if the workweek
for the date in E1 matches ANY workweek for ANY date in Column B and, if it does, return the sum of the percentages in Column D? If not, please clarify. If so, which percentages... all of them or just the ones with matching workweeks? Rick "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
just the % for each week. Match each workweek and return the percentage for
each one: Example: Column B will have such as 6/2/08, 6/3/08/,6/4/08,6/5/08,6/6/08, Column D will have the percentages for each day. I then need in column E to give me the percentage for the entire week. And so on for each following week. Thanks in advance for your help. "Bob Phillips" wrote: Do you mean all dates in column B being in the same week? -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
just the % for each week. Match each workweek and return the percentage for
each one: Example: Column B will have such as 6/2/08, 6/3/08/,6/4/08,6/5/08,6/6/08, Column D will have the percentages for each day. I then need in column E to give me the percentage for the entire week. And so on for each following week. Thanks in advance for your help. "Rick Rothstein (MVP - VB)" wrote: Your question is not entirely clear. Are you asking to see if the workweek for the date in E1 matches ANY workweek for ANY date in Column B and, if it does, return the sum of the percentages in Column D? If not, please clarify. If so, which percentages... all of them or just the ones with matching workweeks? Rick "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
Hi Richard,
Suppose your workweek is defined as Mon-Fri. With dates in B2:B102 and data in D2:D102. To get the overall % of column D that are for any weekdays in column B use the formula: =SUM((WEEKDAY(B2:B102,2)<6)*D2:D102)/SUM(D2:D102) This formula is entered as an array - Press Shift Ctrl Enter instead of enter or use: =SUMPRODUCT((WEEKDAY(B2:B102,2)<6)*D2:D102)/SUM(D2:D102) To get the % for a specific week say Jan 7, 2008 to Jan 11, 2008: =SUM((B2:B102=DATE(2008,1,7))*(B2:B102<=DATE(2008 ,1,11))*D2:D102)/SUM(D2:D102) Again entered as an array or using SUMPRODUCT without array entry. Cheers, Shane Devenshire Microsoft Excel MVP Join http://setiathome.berkeley.edu/ and download a free screensaver and help search for life beyond earth. "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
Date Actual % Ran Weekly % Production
6/2/08 100.00% 6/3/08 100.00% 6/4/08 50.00% 6/5/08 50.00% 6/6/08 50.00% Thanks for your help but I still can not get it. On the Weekly % Production I need for it to show 70% by this example at the end of the Week or whatever the results may be. Like 6/2/98 would be 100%, 6/3/08 would be100%, 6/4/08 would be 83%, 6/5/08 would be 75%, 6/6/08 would be 70% etc... Thanks in Advance!!! "Shane Devenshire" wrote: Hi Richard, Suppose your workweek is defined as Mon-Fri. With dates in B2:B102 and data in D2:D102. To get the overall % of column D that are for any weekdays in column B use the formula: =SUM((WEEKDAY(B2:B102,2)<6)*D2:D102)/SUM(D2:D102) This formula is entered as an array - Press Shift Ctrl Enter instead of enter or use: =SUMPRODUCT((WEEKDAY(B2:B102,2)<6)*D2:D102)/SUM(D2:D102) To get the % for a specific week say Jan 7, 2008 to Jan 11, 2008: =SUM((B2:B102=DATE(2008,1,7))*(B2:B102<=DATE(2008 ,1,11))*D2:D102)/SUM(D2:D102) Again entered as an array or using SUMPRODUCT without array entry. Cheers, Shane Devenshire Microsoft Excel MVP Join http://setiathome.berkeley.edu/ and download a free screensaver and help search for life beyond earth. "Richard" wrote in message ... In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum % in D:D. Can this be done? |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
WorkWeek
A tinker for you to try out over there ..
My assumptions: Real dates in B2 down, % in D2 down, desired "Weekly % production" in E2 down, source data in cols B and D expected within row 2 to 100 (say) In E2: =IF(B2="","",SUMPRODUCT(--(F$2:F$100=weeknum(B2,2)),D$2:D$100)) In F2: =IF(B2="","",weeknum(B2,2)) Select E2:F2, copy down to F100. Col E returns the desired results. P/s: "Weeknum" requires the Analysis ToolPak to be loaded -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "Richard" wrote: Date Actual % Ran Weekly % Production 6/2/08 100.00% 6/3/08 100.00% 6/4/08 50.00% 6/5/08 50.00% 6/6/08 50.00% Thanks for your help but I still can not get it. On the Weekly % Production I need for it to show 70% by this example at the end of the Week or whatever the results may be. Like 6/2/98 would be 100%, 6/3/08 would be100%, 6/4/08 would be 83%, 6/5/08 would be 75%, 6/6/08 would be 70% etc... Thanks in Advance!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Variable workweek overtime | Excel Worksheet Functions | |||
how do i put dates into workweek buckets in excel | Excel Discussion (Misc queries) | |||
7 day workweek timesheet - excluding lunch | Excel Worksheet Functions | |||
4 Day Workweek | Excel Worksheet Functions |