need array assistance for database sorting

I am attaching a worksheet which shows what I would like to see. I am having difficulty writing the formula.

The database I have is a list of forex trades. Each trade is made at a specific time, and involves a specific currency pair (such as EURUSD).

I want to summarize the trades in a table which sums up a week's trades for each pair. (see worksheet).

Can anyone help? Thanks!

Hi

You can use a pivot table for this (see attached). The trick is in getting your dates to group by every seven days. Please see the following link for how to use pivot tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

Richard

I agree with Richard that a PT is the way to go though a PT won't show currency pairs that don't exist in your db (eg AUDJPY)
(Note: you could create dummy records (0) for each pair to rectify this)

Alternative approach given you're using 2007 is to use SUMIFS to generate your table:

Please Login or Register  to view this content.

(Note the numbers generated differ from your ex. given I think you have recorded the 80 EURUSD on 9th Feb in the wrong row in your sample)

Pre XL2007 you'd have to use arrays like Sumproduct which if used repeatedly are normally best avoided.

Also, for the manual table you could use the below to generate your week list:

Please Login or Register  to view this content.

If the database size changes then if using a pivot table you want to ensure that you define a named range to hold the database data (this is so that the Grouping feature works consistently). If you are using xl2003 or xl2007 then you have another option which is to specify the data range is a List (xl2003) or a Table (xl2007) - these will expand and contract as data is added or deleted.

For information on Dynamic Named Ranges (which expand or contract as data is added/deleted) which is what you want (unless using a List/Table) see the following link:

http://www.contextures.com/xlNames01.html#Dynamic

Richard