Calculating average based on 7 days or 5 days

M

Mas

Hi all,

I am trying to generate an average based on two criteria. I have column
A as the date that covers every day of a month, column B is colour (i.e.
blue, red, yellow, etc), and column C a number, column D is the total.

Every day column D updates automatically based on the date. I must fill
in the number or colour. If I was to fill in colour then it is simple as
it covers every day of the month. But, if I was to fill in the number,
I only want it to calculate the average over networkdays for the month
but only up to that the numbers that I have entered.

Is it possible?

Thank you for your help in advance.

Mas
 
R

RNC

Mas, I'm sorry, but I can't visualize your problem. Would you be able
to type in 4 or 5 rows of your data and what you would hypothetically
want in...is it column D?
 
M

Mas

Sorry about that. Try this and I will use numbers instead of colors:

Column A Column B Column C
01-May 10 5
02-May 10 10
03-May 10 5
04-May 10 5
05-May 10 10
06-May 10 0 (weekend)
07-May 10 0 (weekend)

I would like to work out the average for both column B and C every time
a number is entered into both columns.

The average of Column B is calculated over every day of the year whereas
Column C is calculated over a 5 day week (business week).

Therefore, for the above example, column B average would be 10 whereas
column C would be 7.

Hope this is a little clearer.

Mas
 
D

Domenic

[1] To average Column B, try...

=AVERAGE(B2:INDEX(B2:B65536,MATCH(BigNum,B2:B65536)))

Each time a number is entered, it's included in the average.


[2] To average Column C, try...

E2:

=MATCH(9.99999999999999E+307,C:C)

F2:

=AVERAGE(IF(ISNA(MATCH(WEEKDAY(A2:INDEX(A:A,E2)),{1,7},0)),C2:INDEX(C:C,E
2)))

....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Each time a
number is entered, it's included in the average unless the corresponding
date in Column A falls on a weekend.

Hope this helps!
 
R

RNC

This solution assumes you'll only have one year's worth of data and the
data begins in cell A1:

in D1, to average column B's data, enter:
=AVERAGE(B:B)

in E1, to average column C's data over the workdays, enter:
=SUM(C:C)/NETWORKDAYS(MIN(A:A),MAX(A:A))
 
Top