Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old November 19th 05, 02:45 PM posted to microsoft.public.excel.worksheet.functions
Joe D
 
Posts: n/a
Default Top Five selections based on sum of items meeting criteria

I currently have a spreadsheet of ithousands of investment data recods that
each have specific criteria:

Examples
Fund- 1,2,3, etc.
Issuer - IBM, Ford, etc
Asset - Common Stock, Bond, etc
Country - USA, China, France,etc
Category - 1,2,3,4, etc
Industry - Auto, Health care, manufacturing
MV - Market Value


On a daily basis I refresh the data with current market price information.

At that point I need to be able to update a report that shows the top five
issuers in each Category (1,2,3,4,etc) by Market Value for each fund. I
currently run a pivot table on the data table and sort descending and then
copy and paste the results into the report. The problem is that I have to do
this approx. 50 times per week and it takes alot of time.

Example
I would like the report to link to the data table and somehow be able to
total the MV of all Category 1's, 2's etc and then list the top 5 in each
category in descending order. I need to do this for each of 3 funds( the
fund (1,2,3) are noted in one of the columns described above.

Category 1
IBM $567,897
Ford $234,152
3
4
5

Category 2
Fiat $545,666
Toyota $332,123
3
4
5

etc

Is this something I can do in excel or do I need something else?

Any suggestions would be appreciated. I can provide a sample of the data if
that helps.


--
Joe D

--
Joe D

  #2   Report Post  
Old November 20th 05, 10:42 AM posted to microsoft.public.excel.worksheet.functions
Ken Wright
 
Posts: n/a
Default Top Five selections based on sum of items meeting criteria

Just add another field to your source data that gives you a TRUE / FALSE
result as to whether or not that issuer is in the top 5 for that fund, then
pull that field into the PAGE fields and filter on TRUE.

As an example, assuming the following data is in A230:-

Fund Issuer MV Top5
2 a 4,653 FALSE
3 b 325 FALSE
1 c 3,780 TRUE
2 d 1,381 FALSE
2 e 6,544 TRUE
1 f 1,536 TRUE
... .. .. ..

The last column is generated by a formula such as:-

=C3=LARGE(IF($A$3:$A$30=A3,$C$3:$C$30),5) array entered using
CTRL+SHIFT+ENTER

Pivot that lot, pull Top5 into Page field and filter on True and set Issuer
field to sort Descending based on Sum of MV field.

Assumes you have at least 5 issuers in each fund, but I can always fix it if
you don't

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Joe D" wrote in message
...
I currently have a spreadsheet of ithousands of investment data recods that
each have specific criteria:

Examples
Fund- 1,2,3, etc.
Issuer - IBM, Ford, etc
Asset - Common Stock, Bond, etc
Country - USA, China, France,etc
Category - 1,2,3,4, etc
Industry - Auto, Health care, manufacturing
MV - Market Value


On a daily basis I refresh the data with current market price information.

At that point I need to be able to update a report that shows the top five
issuers in each Category (1,2,3,4,etc) by Market Value for each fund. I
currently run a pivot table on the data table and sort descending and then
copy and paste the results into the report. The problem is that I have to
do
this approx. 50 times per week and it takes alot of time.

Example
I would like the report to link to the data table and somehow be able to
total the MV of all Category 1's, 2's etc and then list the top 5 in each
category in descending order. I need to do this for each of 3 funds( the
fund (1,2,3) are noted in one of the columns described above.

Category 1
IBM $567,897
Ford $234,152
3
4
5

Category 2
Fiat $545,666
Toyota $332,123
3
4
5

etc

Is this something I can do in excel or do I need something else?

Any suggestions would be appreciated. I can provide a sample of the data
if
that helps.


--
Joe D

--
Joe D



  #3   Report Post  
Old November 20th 05, 11:49 PM posted to microsoft.public.excel.worksheet.functions
Joe D
 
Posts: n/a
Default Top Five selections based on sum of items meeting criteria

Ken, Thanks A follow up qiuestion if you don't mind. In my data records I
might have 10 issuer A's, 5 B's, 25C's, etc that must be summed befroe I can
determine which of the issuers are in the top 5 based on issuer cumulative
MV. Can this still be done?
--
Joe D


"Ken Wright" wrote:

Just add another field to your source data that gives you a TRUE / FALSE
result as to whether or not that issuer is in the top 5 for that fund, then
pull that field into the PAGE fields and filter on TRUE.

As an example, assuming the following data is in A230:-

Fund Issuer MV Top5
2 a 4,653 FALSE
3 b 325 FALSE
1 c 3,780 TRUE
2 d 1,381 FALSE
2 e 6,544 TRUE
1 f 1,536 TRUE
... .. .. ..

The last column is generated by a formula such as:-

=C3=LARGE(IF($A$3:$A$30=A3,$C$3:$C$30),5) array entered using
CTRL+SHIFT+ENTER

Pivot that lot, pull Top5 into Page field and filter on True and set Issuer
field to sort Descending based on Sum of MV field.

Assumes you have at least 5 issuers in each fund, but I can always fix it if
you don't

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"Joe D" wrote in message
...
I currently have a spreadsheet of ithousands of investment data recods that
each have specific criteria:

Examples
Fund- 1,2,3, etc.
Issuer - IBM, Ford, etc
Asset - Common Stock, Bond, etc
Country - USA, China, France,etc
Category - 1,2,3,4, etc
Industry - Auto, Health care, manufacturing
MV - Market Value


On a daily basis I refresh the data with current market price information.

At that point I need to be able to update a report that shows the top five
issuers in each Category (1,2,3,4,etc) by Market Value for each fund. I
currently run a pivot table on the data table and sort descending and then
copy and paste the results into the report. The problem is that I have to
do
this approx. 50 times per week and it takes alot of time.

Example
I would like the report to link to the data table and somehow be able to
total the MV of all Category 1's, 2's etc and then list the top 5 in each
category in descending order. I need to do this for each of 3 funds( the
fund (1,2,3) are noted in one of the columns described above.

Category 1
IBM $567,897
Ford $234,152
3
4
5

Category 2
Fiat $545,666
Toyota $332,123
3
4
5

etc

Is this something I can do in excel or do I need something else?

Any suggestions would be appreciated. I can provide a sample of the data
if
that helps.


--
Joe D

--
Joe D






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Returning Results Based on Two Criteria [email protected] Excel Worksheet Functions 7 October 23rd 05 02:53 PM
calculation based on meeting two criteria jerry Excel Discussion (Misc queries) 2 October 7th 05 12:35 AM
Locate a Cell based upon criteria Stan Excel Discussion (Misc queries) 1 September 23rd 05 03:05 PM
Counting the number of cells meeting conditional formating criteria Jeff Excel Worksheet Functions 4 July 9th 05 01:18 AM
counting cells (COUNTIF) based on two or more criteria Tricia S. Excel Worksheet Functions 10 March 17th 05 02:17 PM


All times are GMT +1. The time now is 12:33 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017