Averages for "x" days/weeks (user input)

Hey all...I'm new here, hopefully you will kindly help the newbie with what I imagine will be an easy one for you excel ninjas!

I've got some named ranges, "Sales" and "Weekday" , I'd like to be able to find an average for Sales for the past "x" number of weekdays that match the user input. I'd like the "x" number of weeks to be user defined as well. I've already got a drop-down list for the day of the week that the user would like to display.

Basically, if they want to see the average sales numbers for the past 12 Mondays, I just want them to choose Monday from the drop down and enter a 12 into another cell.

What is the best formula for this? I'm assuming it's some combination of averageif and offset, but I can't seem to get it right.

Thanks in advance!

MJ

So, YOU design the Pivot table, then they need only right-click and refresh it. Show them that once...