S
Scott Kieta
I have a fairly complex spreadsheet which covers a full month of work
and in alternating columns i have total volume and Average processing time,
per day. The columns look something like this:
A B C D E F
G H
27-Oct 28-Oct 29-Oct
30-Oct
Tue Wed Thu
Fri
RECVD APT RECVD APT RECVD APT RECVD APT
What i need to do is calculate a running average file per day total per
process, but and i have figured out how to calculate the average ignoring
zero values, but then that is not accurate as i only want the average of the
days that have already passed and ignore the zero values on the weekend. My
thought was to use a countif formula to count the cells which have data in
recvd column and ignore the dates with zero value. Basically as of today
there would be 12 working days but my formula counts 31.
Here is the formula i tried.
=IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6=
Recvd", now i want to add the 2nd criteria only counting cells >0.
I tried a sumproduct (below) but it comes back with #VALUE, value is wrong
data type.
=IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$47>0)))
row 47 is the row with the sum of the values.
Sorry this was so long, but i am hoping to clear it up and wanted to provide
a lot of detail.
and in alternating columns i have total volume and Average processing time,
per day. The columns look something like this:
A B C D E F
G H
27-Oct 28-Oct 29-Oct
30-Oct
Tue Wed Thu
Fri
RECVD APT RECVD APT RECVD APT RECVD APT
What i need to do is calculate a running average file per day total per
process, but and i have figured out how to calculate the average ignoring
zero values, but then that is not accurate as i only want the average of the
days that have already passed and ignore the zero values on the weekend. My
thought was to use a countif formula to count the cells which have data in
recvd column and ignore the dates with zero value. Basically as of today
there would be 12 working days but my formula counts 31.
Here is the formula i tried.
=IF($C$47:$BV$47=0,0,COUNTIF($C$6:$BV$6,$C$6)) this total ='s 31, ($c$6=
Recvd", now i want to add the 2nd criteria only counting cells >0.
I tried a sumproduct (below) but it comes back with #VALUE, value is wrong
data type.
=IF(C47:BV47="","",SUMPRODUCT(--($C$6:$BV$6=$C$6),--($C$47:$BV$47>0)))
row 47 is the row with the sum of the values.
Sorry this was so long, but i am hoping to clear it up and wanted to provide
a lot of detail.