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 |
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 A2:D30:- 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 |
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 A2:D30:- 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 |
All times are GMT +1. The time now is 09:59 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com