Hi,
I would like to get the Top 5 Selling Funds with Amounts of each Branch, and put the values in the Standard Template (Fixed).
I've tried to use the following formula, but it has problems when I come across the second branch e.g. HK East, Excel will return the values of the 1st branch (i.e. Central).
=IFERROR(INDEX(Pivot!$A$4:$C$12,LARGE(IF(Pivot!$A$4:$A$12='Standard template'!A3,ROWS($A$1:A1)),ROW(A1)),2),"")
Appreciate if you could help on this.
Regards,
Hiu Wah
Hi. It's MUCH easier if you take the values from the raw data using SUMPRODUCT.
=SUMPRODUCT((Table1[Branch]='Standard template'!A3)*(Table1[Fund Name]='Standard template'!B3)*Table1[Dealing Amount])
Copy down as far as needed.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Hi Glenn,
How about how to get the Top 5 Selling Fund Names of each branch and show in the "Standard Template" worksheet?
Thanks!
I had hoped to do away with the Pivot Table.. However, I got a bit stuck. So.. how does this look
Hi,
That's the problem. You can see the top 5 fund names of HK East branch are those of Central branch which is incorrect.
Maybe this will help. Each branch is selected separately and the following formula is entered after selecting the Dealing Amount range for the branch. This will return the top 5 for the branch.
Formula:
Please Login or Register to view this content.
This formula will match the Branch and the Dealing Amount for the funds in column B:
Formula:Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
Not sure about this..........
I think that I have improved the solution that I previously submitted. This assumes that you will limit the number of rows on the Standard Template worksheet to 5 rows per branch. This will return the top 5 values from column C of Raw Data for each branch and also return the proper value from column B on Raw Data.
On the Standard Template worksheet C3 array enter this formula and fill down:
Formula:Please Login or Register to view this content.
In B3 array enter this formula and fill down:
Formula:Please Login or Register to view this content.
Hi newdoverman,
Thanks for your improved version. That's really great.
However, would you mind helping me to further amend the formula so that the fields will show in blank (e.g. in last row) if let's say the branch has only 4 selling funds?
Appreciated your help!
If you can use the IFERROR in C3 and fill down: (enter with Ctrl + Shift + Enter)
Formula:Please Login or Register to view this content.
In B3 enter this with Ctrl + Shift + Enter:
Formula:Please Login or Register to view this content.
Alternatively, you can continue with the current formulae without change and use Conditional formatting to hide error values.
Select B3 to maybe C100 then in Conditional Formatting, New Rule, Use Formula use this short formula and format the font with white text:
Formula:Please Login or Register to view this content.
Hi newdoverman,
Thank you very much for your help. That's brilliant!
One more question though I know there might not be a perfect solution. In case the 4th & 5th funds have the same amount, Excel will return the same fund name (i.e. the 4th fund name) in both rows which is not quite right. Just wonder if it can be further fine-tuned.
Cheers!
Is NDM's working? there are 2 entries for AB China fund in central and two in HK east...
Yes, the NDM is working fine. If I use pivot table to get the sum of amount of each fund per branch and use the pivot result as "Table1" then that' fine.
But assume "AB China Fund" & "AB European Fund" have the same amount under Central branch, then it will only return "AB China Fund" in 4th & 5th rows.
I understand your Q... but is this really correct?? See yellow cells.
I've amended the raw data a little bit. In fact, it won't happen to have 2 same fund names per branch. Try the attached.
I have inserted 2 helper columns to "rank" the Dealing Amounts. The helper column on the Raw Data worksheet is in the table. If you make edits to the Dealing Amounts there may be a green error triangle appear in the Rank column. Fill the formula down from D2 to refresh the formulae. There is a helper column on the Standard Template worksheet (column D which has white text to make it invisible) that uses the values from the Raw Data worksheet Rank column. This is a bit of an inconvenience but the formulae like this will not update properly on their own.
Excellent! Thank you so much for your help!!
Here is your file with the amended data. I don't think the name of the fund really affected anything because the name returned is a result of the Dealing Amount and the Branch.
Thank you for the feedback.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks