Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 Quote:
|
#3
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Calculation | Excel Worksheet Functions | |||
Excel Range Names | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
UDF and Calculation tree | Links and Linking in Excel | |||
Include Saturday in the WORKDAY function | Excel Worksheet Functions |