formula to show average over specific time frame over multiple sheets

Hi all

My first post and I've got a doozy for you.

Any help would be greatly appreciated!

See attached workbook for what I'm working on. I am trying to make a tool to administrate an order pool we are creating at work. It works like this: Customers place orders during September to December, they then collect their orders between January to July (bulk commodity products).

The principal of an order pool is the customer pays an average price for the time we are holding their order. i.e. a customer orders in November and wishes to collect his order in May, meaning we are holding his order for 7 months - a total of 7 monthly price changes. To avoid trying to guess the market lows/highs, we created this pool concept so they pay an average price.

What I'm trying to do is add to the existing formula (the H column on the main sheet) so the price of each product ordered is averaged from the time the customer placed their order.

You will see in the Pricelist sheet I added an average column which is what the lookup function is returning on the main sheet. The idea is to get rid of this.

The customer order sheet is the only sheet that will be edited by staff, oh and the pricelist sheet as price changes occur. The main sheet is where we want all the info displayed.

If you check out the formula in the H column on the main sheet - this is where I have got to. Basically I want to add to this formula so the average price is returned from the time they order. The end date for their order will be =today()

The expt sheet is what someone else added who tried to help - if you check out their formulas they were getting pretty close.

Any guidance would be great

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.