Calc occurrances for weekday omitting weekend data
 

I have a list of the count of daily application errors. I need to show the
average for the past 30-workdays. The workdays formula does not appear to be
what I need. What formula can I use that will omit the data for the weekends?

Calc occurrances for weekday omitting weekend data
 

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

'Sum of Last 5
=SUMPRODUCT((ROW(A1:A1000)=LARGE((LEFT(TEXT(A1:A10 00,"dddd"),1)<"S")*ROW(A1:A1000),COLUMN(A:E)))*(B 1:B1000))

'Sum of last 30
=SUMPRODUCT((ROW(A1:A1000)=LARGE((LEFT(TEXT(A1:A10 00,"dddd"),1)<"S")*ROW(A1:A1000),COLUMN(A:AD)))*( B1:B1000))

If this post helps click Yes
---------------
Jacob Skaria


"dtoney" wrote:

I have a list of the count of daily application errors. I need to show the
average for the past 30-workdays. The workdays formula does not appear to be
what I need. What formula can I use that will omit the data for the weekends?

Calc occurrances for weekday omitting weekend data
 

Forgot to mention that I worked with dates in ColA and count in ColB....as
below

'Try out sum of last 5 with the below sample..ColA in excel date format

ColA ColB
Friday, October 23, 2009 1
Saturday, October 24, 2009 2
Sunday, October 25, 2009 3
Monday, October 26, 2009 4
Tuesday, October 27, 2009 5
Wednesday, October 28, 2009 6
Thursday, October 29, 2009 7
Friday, October 30, 2009 8
Saturday, October 31, 2009 9
Sunday, November 01, 2009 10
Monday, November 02, 2009 11


If this post helps click Yes
---------------
Jacob Skaria


"Jacob Skaria" wrote:

Please note that this is an array formula. An array formula can perform
multiple calculations and then return either a single result or multiple
results. Array formulas act on two or more sets of values known as array
arguments. Each array argument must have the same number of rows and columns.
You create array formulas in the same way that you create other formulas,
except you press CTRL+SHIFT+ENTER to enter the formula. If successful in
'Formula Bar' you can notice the curly braces at both ends like "{=<formula}"

'Sum of Last 5
=SUMPRODUCT((ROW(A1:A1000)=LARGE((LEFT(TEXT(A1:A10 00,"dddd"),1)<"S")*ROW(A1:A1000),COLUMN(A:E)))*(B 1:B1000))

'Sum of last 30
=SUMPRODUCT((ROW(A1:A1000)=LARGE((LEFT(TEXT(A1:A10 00,"dddd"),1)<"S")*ROW(A1:A1000),COLUMN(A:AD)))*( B1:B1000))

If this post helps click Yes
---------------
Jacob Skaria


"dtoney" wrote:

I have a list of the count of daily application errors. I need to show the
average for the past 30-workdays. The workdays formula does not appear to be
what I need. What formula can I use that will omit the data for the weekends?

Calc occurrances for weekday omitting weekend data
 

On Fri, 23 Oct 2009 07:38:09 -0700, dtoney
wrote:

I have a list of the count of daily application errors. I need to show the
average for the past 30-workdays. The workdays formula does not appear to be
what I need. What formula can I use that will omit the data for the weekends?

Is data for the weekends included in your database?

If not, then you could use a SUMIF function:

=SUMIF(Dates,"="&WORKDAY(InputDate,-30),Errors)-SUMIF(Dates,""&InputDate,Errors)

Dates is your list of dates
Errors is your error count for each data
InputDate is the date that you want to look back 30 days from.


--ron

Calc occurrances for weekday omitting weekend data
 

=SUMPRODUCT(--(A1:A100=WORKDAY(TODAY(),-29)),--(WEEKDAY(A1:A100,2)<6),B1:B100)/SUMPRODUCT(--(A1:A100=WORKDAY(TODAY(),-29)),--(WEEKDAY(A1:A100,2)<6))

or
=SUMPRODUCT(--(A1:A100=WORKDAY(TODAY(),-29)),--(WEEKDAY(A1:A100,2)<6),B1:B100)/30

I have assumed that the last cell in A is for the current day.

--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email


"dtoney" wrote in message
...
I have a list of the count of daily application errors. I need to show the
average for the past 30-workdays. The workdays formula does not appear to
be
what I need. What formula can I use that will omit the data for the
weekends?