Pivot table and calculations

Hi all!

I am currently really stuck on a certain project of mine and I'm looking for a solution to make my life a lot easier. I am interested in calculating, from a pivot table, averages of certain values within a specific time frame.

For example:

I am interested in only the deals withing a month's timeframe (1st-28th/30th/31st), and for each of these deals that qualify I wish for excel to identify them and add all the costs and then divide by the quanitity (as to receive an average). Is this possible? Can excel identify monthly parameters and then proceed to add the costs of those who qualify and divide by the quantity? Any help would be greatly appreciated! Thank you very much!

Allow me to rephrase my question.

I have a start date in one colum and an end date in another column for a variety of projects.

I also have daily values corresponding to each day for an entire year.

I need somehow to make it so that Excel will take the dates between the start and end date (including the start and end date), and find an average value across all these days with the help of daily corresponding values. I tried using a VLOOKUP function but my success was limited to only managing to get the averages of the start and the end date, and not the average value of all the dates also in between the start and end date.

Thank you!

Hi, just an attempt: column A starting dates, B ending dates, C values

=AVERAGE(INDIRECT("C"&MATCH(Start date,A2:A100,0)+1&":"&"C"&MATCH(End date,B2:B100,0)+1))

I've attached a brief example

Cheers

That formula seems to do something but its not collecting the right numbers. In your example there was a project starting every day but that is no the case with the data that I'm using. How can I change that? Is it possible to do this without a VLOOKUP function?

Hi,
attached it's another example about how to calculate an average.

=SUMPRODUCT((A2:A100="A")*(B2:B100>=B5)*(C2:C100<=C15)*(D2:D100)/(SUMPRODUCT(--(A2:A100="A"))))

Column A name of projects, B beginning date (b5) , C ending date (c15) , D values.

Maybe a sample file from you could help friends of the Forum in finding better solutions.

Cheers