Date Range and calculation

[vgreen]

I was wondering if an array formula was best for this or hopefully there
is a shorter way?

I have a sheet (Base) which has the information for interrogation and a
summary sheet (Monthly). I wish the monthly sheet to extract data from
the base sheet according to the date range entered. There is two cells,
with a start and end date. The rest of the fields then calculate
depending on that. One of the fields needs to return an average net
commission %. So, depending on the date range entered it needs to find
(filter) within that date range in the Base sheet on Col P (which is
list of dates of exchange), then get the average of Col Y (which is a
fee amount in £) and divide it by the average of Col S (which is sale
price in £).

sorry for being long winded!

any suggestions gratefully received.

 

[sirknightly]

Green,

Will the date ranges vary, or are you just trying to get a monthl
summary of the numbers (and therefore the "Monthly" name for the tab).
If you're just looking for a monthly summary, I'd add a new column t
your data table that calculates the month of each date like this:

=DATE(YEAR(A1),MONTH(A1),1)

and then create a PivotTable that references that data range an
summarize by the new "Month" field.

If you are looking for a way to select between dates, I'm thinking a
array formula would be best for that. Assuming that your criteria ar
in A1 (first date) and A2 (subsequent date), and replacing GT and L
with the appropriate symbols below, change the ranges in base a
necessary and enter as an array formula:

=(SUM(IF(Base!P125GT=Base!A1,IF(Base!P125LT=Base!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P125GT=Base!A1,IF(Base!P125LT=Base!A2,1,0),0)))/
(SUM(IF(Base!P125GT=Base!A1,IF(Base!P125LT=Base!A2,Base!S1:S25,0),0))/SUM(IF(Base!P125GT=Base!A1,IF(Base!P125LT=Base!A2,1,0),0)))

Knightly

 

[vgreen]

knightly, many thanks for your response. the first approach reducin
value to monthly one is useful and I will incorporate.

the second approach is great also and I will be tweaking it for us
with the other calcuations that I have to make. thank you again fo
such a clear answer