AVERAGE problem

[malik641]

Okay, to start...I have 2 rows, 1 and 2, where Row1 has dd-"month" and Row2 has "day". And a 3rd row has percent values which are to be averaged. Example:

D1=01-Apr--- E1=02-Apr
D2=Fri---------E2=Sat
D3=50%-------E3=100%

and so on and so forth. We'll just say it goes until the end of the month and the following months thereafter.

Now let's say that:
D5=Apr-05-----E5='The average of the month EXCLUDING weekends and holidays'.
D6=May-05-----E6='Same formula as E5 except using the month of May'
and so on...

How could I sum the values of the percentages given the criteria?
I would guess it would be using the Index & Match functions, but how?

[Morrigan]

Try this with 2 helper rows:

Helper1 = assign "Weekday" or "Weekend" to the corresponding day
Helper2 = Concatenate(Month,Helper1) (May look something like "AprWeekday", "AprWeekend", "MayWeekday", etc)

Apply

SUMIF(D2:XX2,"AprWeekday",D3:XX3)/COUNTIF(D2:XX2,"AprWeekday")


Hope it helps.

Okay, to start...I have 2 rows, 1 and 2, where Row1 has dd-"month" and Row2 has "day". And a 3rd row has percent values which are to be averaged. Example:

D1=01-Apr--- E1=02-Apr
D2=Fri---------E2=Sat
D3=50%-------E3=100%

and so on and so forth. We'll just say it goes until the end of the month and the following months thereafter.

Now let's say that:
D5=Apr-05-----E5='The average of the month EXCLUDING weekends and holidays'.
D6=May-05-----E6='Same formula as E5 except using the month of May'
and so on...

How could I sum the values of the percentages given the criteria?
I would guess it would be using the Index & Match functions, but how?

[Domenic]

Assumptions:

1) D1:CP3 contains your data

2) D5, D6, etc. contain the first day for each month and year

3) A1:A10 contains your list of holidays


Formula:

E5, copied down:

=AVERAGE(IF($D$3:$CP$3<>"",IF(($D$1:$CP$1-DAY($D$1:$CP$1)+1=D5)*(WEEKDAY($D$1:$CP$1,2)<6)*(1-ISNUMBER(MATCH($D$1:$CP$1,$A$1:$A$10,0))),$D$3:$CP$3)))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Adjust the ranges accordingly.

Hope this helps!

Okay, to start...I have 2 rows, 1 and 2, where Row1 has dd-"month" and Row2 has "day". And a 3rd row has percent values which are to be averaged. Example:

D1=01-Apr--- E1=02-Apr
D2=Fri---------E2=Sat
D3=50%-------E3=100%

and so on and so forth. We'll just say it goes until the end of the month and the following months thereafter.

Now let's say that:
D5=Apr-05-----E5='The average of the month EXCLUDING weekends and holidays'.
D6=May-05-----E6='Same formula as E5 except using the month of May'
and so on...

How could I sum the values of the percentages given the criteria?
I would guess it would be using the Index & Match functions, but how?

[malik641]

Thanks! It's working Great now!