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?

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?

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?

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?

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?

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?

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?

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