• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Top 5 & Bottom 5 using formula

aparvez007

aparvez007

Member
Hi Ninja's

Please help me.
I want formula for top 5 & Bottom 5 name using formula. I have data in RAW sheet and I want formula for View sheet. I put the required value in manually. I have drop down in C2.

Regards,
pAvi
 

Attachments

  • Book2.xlsx
    37.1 KB · Views: 14
@#aparvez007,

Top/Bottom five based on TT or PP??

@Somendra,

Remove subtaotals from pivot table, it will improve its appearance.
 
Last edited:
Thanks SM/Faseeh,

I want my output in Top5 Table (C5:F9) and Bottom5 Table (H5:K9) using formula.

@Faseeh,
Top/Bottom 5 based on total of TT & PP.

Regards,
pAvi
 
Dear SM,

Thanks alot you are genius dear.

Thanks again.

I have one question if I want only top 5 Label name based on total without product then formula will be small lengthy like your last reply, because I didn't understand the formula it's look very complicated.

If I get Label name based on total then I will change my format.


Regards,
pAvi
 
Hi pAvi,

See the attached file. Its only bring total and names. Unfortunately, I could not come up with a shorter formula. But the helper Table is short.

Yellow Cells are Helper cells. Blue Cells is original Data and orange cells are final output.

Now drawbacks... This uses lots of volatile functions INDIRECT & OFFSET which slows down the calculation. With your current dataset of approx. 1200 rows it consumed 1.5 seconds to do full calculations. I don't know the exact data size that you have.

If this was for only learning purpose than this is one way, there can be many ways of doing this.

If you want it in your actual work than I will suggest you to go with pivot tables, managers want data, how you bring it they are not intereseted in it. ;) or wait for some time till somebody comes up with more quicker and better solution.

If you need any clarification on the approach used please get back.

Regards,
 

Attachments

  • pAvi_ChandooTOpBottom5.xlsx
    90.9 KB · Views: 5
Thanks alot SM for your precious time for my query.
This is require for Dashboard and I will adding data every month so may be your solution will slow down my report.
But I will learn lot of things from your solution. I will utilise this in other report.

I will get back to you if need more clarification.

Thanks again,

Regards,
Parvez
 
You must log in or register to reply here.
Back
Top