WorkWeek

R

Richard

In cell E1: If B:B(Date) is not equal to same WorkWeek do nothing, else, Sum
% in D:D. Can this be done?
 
B

Bob Phillips

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)
 
R

Rick Rothstein \(MVP - VB\)

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
 
R

Richard

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.
 
R

Richard

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.
 
S

Shane Devenshire

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.
 
R

Richard

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!!!
 
M

Max

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
 
Top