Calculating average based on 7 days or 5 days

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mas - 31 Aug 2006 22:05 GMT

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

Reply to this Message

RNC - 01 Sep 2006 14:17 GMT

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?

> Hi all,
>
[quoted text clipped - 13 lines]
>
> Mas

Reply to this Message

Mas - 03 Sep 2006 00:30 GMT

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

> 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
[quoted text clipped - 17 lines]
>>
>> Mas

Reply to this Message

Domenic - 03 Sep 2006 15:18 GMT

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

> Sorry about that. Try this and I will use numbers instead of colors:
>
[quoted text clipped - 19 lines]
>
> Mas

Reply to this Message

RNC - 03 Sep 2006 16:51 GMT

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

> Sorry about that. Try this and I will use numbers instead of colors:
>
[quoted text clipped - 41 lines]
> >>
> >> Mas

Reply to this Message

Joe_Germany - 03 Sep 2006 17:09 GMT

If I understood correctly, this may work for Column C..

=SUMIF(C12:C18,"<>0")/COUNTIF(C12:C18,"<>0")

> > Sorry about that. Try this and I will use numbers instead of colors:
> >
[quoted text clipped - 41 lines]
> > >>
> > >> Mas

Reply to this Message