Average of first three days of the week

Hi goodboy,

I think you can accomplish this with a fancy pivot table. See the attached showing how to filter a pivot by first 3 days of a week and then only using the maximum and then averaging those values. I don't think I did exactly what you want but it should give you some ideas on how I'd attack the problem.

Why? You want to filter some data and then find the maximum and then average the maximums of filtered data. A pivot table does all these and you want to do the problem in a HARDER way. It all seems backwards to me..

Look at Array formulas to pull out weekdays of 2,3,4 and then Large functions to pull out the 1,2,3 largest values. Then you can Sum them and divide by 3 or use the Average formula.

Your needing "A formula" is like asking to need the Sum divided by the count instead of being able to use the "Average" function.

I'd suggest you look at the possibilities available in a Pivot Table kind of answer. Maybe it will allow you to get more answers and easier than using basic formulas.

If you keep insist on using a formula:
Try in I4,J4,M4,N4:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

With 2007+, it become easier alot with AVERAGEIFS.

Hi,

See attached

Late edit: Just realised. If you only have XL2003 ignore this response. I'd missed that salient point.

@MarvinP

Neat. I like it.