Date Range and calculation
 

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.


--
vgreen
------------------------------------------------------------------------
vgreen's Profile: http://www.excelforum.com/member.php...o&userid=26510
View this thread: http://www.excelforum.com/showthread...hreadid=397768

Green,

Will the date ranges vary, or are you just trying to get a monthly 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 to 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 and summarize by the new "Month" field.

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

=(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Ba se!A2,Base!Y1:Y25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base !A2,1,0),0)))/
(SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Bas e!A2,Base!S1:S25,0),0))/SUM(IF(Base!P1:P25GT=Base!A1,IF(Base!P1:P25LT=Base !A2,1,0),0)))

Knightly

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

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


--
vgreen
------------------------------------------------------------------------
vgreen's Profile: http://www.excelforum.com/member.php...o&userid=26510
View this thread: http://www.excelforum.com/showthread...hreadid=397768