+ Reply to Thread
Results 1 to 3 of 3

How to return results of a top 5 lowest price, top 5 highest price and price difference?

  1. #1
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    83

    Smile How to return results of a top 5 lowest price, top 5 highest price and price difference?

    Hi community.
    I'm stuck on the same project since 1 month.
    I work on the workbook Clean Data.

    On the worksheet Raw Data, I have all my raw data ordered by column (country, category of item, brand, name of item... end user price in EUR after VAT, GDP). The photo is not very clear.
    Picture1.png

    My source data must be the worksheet Raw data and update in my source data must update results of report.


    I would like 3 different report:
    Report 1: Top 5 highest price in France (with all informations: country, category of item, brand name, name of item... end user price in EUR after VAT, GDP)
    Report 2: Top 5 lowest price in France (with all informations: country, category of item, brand name, name of item... end user price in EUR after VAT, GDP)
    Report 3 (the most difficult one): Price difference per category of item compared to Cochlear

    I tried to make pivot table but update of data is not intuitive.

    Thank you for your answer. It's quite difficult, it could be grateful to help me.
    Attached Files Attached Files
    Last edited by Luu4466; 12-05-2020 at 05:30 AM.

  2. #2
    Forum Expert
    Join Date
    09-07-2014
    Location
    Israel
    MS-Off Ver
    Office2010/office 365
    Posts
    1,753

    Re: How to return results of a top 5 lowest price, top 5 highest price and price differenc

    For 5 lowest price in France:
    E49 =IF(COUNTA($E$48:E48)>=5,"",AGGREGATE(15,6,(('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw data'!$H$2:$H$1000)<>0)),ROWS('Raw data'!$A$1:E1)))

    D49 (and drag to the left)
    HTML Code: 
     =IF(COUNTA($E$48:$E48)>=5,"",INDEX('Raw data'!D$1:D$1000,AGGREGATE(14,6,(((ROW('Raw data'!$B$2:$B$1000)))/((('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw 
    data'!$H$2:$H$1000)<>0))<=AGGREGATE(15,6,(('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw data'!$H$2:$H$1000)<>0)),5))),ROWS('Raw data'!$A$1:E1))))
    For 5 highest price in France:
    E56 =IF(COUNTA($E$48:E48)>=5,"",AGGREGATE(14,6,(('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw data'!$H$2:$H$1000)<>0)),ROWS('Raw data'!$A$1:E1)))

    D56 (and drag to the left)
    HTML Code: 
    =IF(COUNTA($E$55:$E55)>=5,"",INDEX('Raw data'!D$1:D$1000,AGGREGATE(15,6,(((ROW('Raw data'!$B$2:$B$1000)))/((('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw data'!$H$2:$H$1000)<>0))>=AGGREGATE(14,6,(('Raw data'!$H$2:$H$1000))/(('Raw data'!$A$2:$A$1000="France")*('Raw data'!$A$2:$A$1000<>"")*(('Raw data'!$H$2:$H$1000)<>0)),5))),ROWS('Raw data'!$A$1:E1))))
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-07-2020
    Location
    Copenhague
    MS-Off Ver
    Microsoft office 365
    Posts
    83

    Re: How to return results of a top 5 lowest price, top 5 highest price and price differenc

    Thank you belinda for this big help. It's really wonderful.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Find the latest price and lowest price out of formatted text
    By anonymous321 in forum Excel General
    Replies: 9
    Last Post: 03-23-2018, 03:11 PM
  2. [SOLVED] Comparing my price to the lowest competitor's price in an excel row
    By yr25 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 05-10-2017, 11:05 AM
  3. [SOLVED] $ Total a Quantity from Lowest to Highest Price
    By uberathlete in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-17-2015, 08:57 PM
  4. Replies: 1
    Last Post: 07-24-2013, 12:13 PM
  5. Replies: 3
    Last Post: 01-03-2013, 12:32 AM
  6. Replies: 4
    Last Post: 08-15-2012, 09:49 AM
  7. Mark the Lowest price in a price-matrix
    By raed_237 in forum Excel General
    Replies: 8
    Last Post: 04-07-2009, 01:24 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1