Calc occurrances for weekday omitting weekend data

  • Thread starter Thread starter dtoney
  • Start date Start date
D

dtoney

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?
 
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:A1000,"dddd"),1)<>"S")*ROW(A1:A1000),COLUMN(A:E)))*(B1:B1000))

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

If this post helps click Yes
 
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
 
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?
Click to expand...

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
 
=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.
 
Back
Top