AVERAGE problem
 

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
------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388046

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.



malik641 Wrote:
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
------------------------------------------------------------------------
Morrigan's Profile: http://www.excelforum.com/member.php...fo&userid=7094
View this thread: http://www.excelforum.com/showthread...hreadid=388046

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!

malik641 Wrote:
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
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=388046

Thanks! It's working Great now!


--
malik641


------------------------------------------------------------------------
malik641's Profile: http://www.excelforum.com/member.php...o&userid=24190
View this thread: http://www.excelforum.com/showthread...hreadid=388046