1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

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

categorizing based on rule ($value more than 50% of total value)

Discussion in 'Ask an Excel Question' started by nitika.manhas, Apr 13, 2018.

  1. nitika.manhas

    nitika.manhas New Member

    Messages:
    16
    upload_2018-4-13_15-15-4.png

    • A105959 is order number (i have 900 of them in my report)
    • Product type column is the list of items that go into the order
    • Each item is either aftermarket or equipment and has the unit price
    For example, for order A105959, the total price for aftersales items are $63 and equipment is $2884. according to my manager, the whole order becomes equipment because the equipment item value is > 50% of the total value of the order and vice versa.

    we have other orders where the item price for aftersales is more than 50% of the total order value.

    I have 900 orders, what would be the best way to categorize each order as equipment or aftersales based on 50% rule.

    Hope to get some awesome responses!

    Regards,
    Nitika Manhas

    Attached Files:

  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    16,619
    Hi ,

    Can you upload your workbook with the data in it , instead of screenshots ?

    Narayan
  3. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    Hi, it is possible via Get & Transform.
    Would that be suitable?

    Attached Files:

  4. p45cal

    p45cal Well-Known Member

    Messages:
    1,231
    While awaiting a workbook with data in it I stole GraH - Guido's file and:
    upload_2018-4-14_13-49-36.png

    1. Fill in the missing data in the first column (it looks that this might be a pivot table judging by Nitika's picture, so might only need to Repeat all labels in the Report Layout dropdown in the Layout section of Pivottable tools section of the ribbon).
    2. Create a pivot table as shown in the picture from the data in columns A to D (bold outline in the picture)
    3. Filter the Item Category in the pivot table using Value Filters, and Top 10… to show only the top 1. This creates a table (highlighted in yellow) we can use to look up the category.
    4. Add a new column (CAT 2) in column E with the formula shown in the formula bar in the diagram.

    QED.

    Or forget all that (except for point 1 above) and use this in cell E2:
    Code (vb):
    =IF(SUMIFS([Sum of Unit Price],[Order number],[@[Order number]],[Item category],"Equipment")>SUMIFS([Sum of Unit Price],[Order number],[@[Order number]],[Item category],"Aftermarket"),"Equipment","Aftermarket")
    Last edited: Apr 14, 2018
  5. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    What?!? You stole from me, p45cal? ;-)

    I did not look at the pictures, but indeed you are soooo right. Nice one.
    I was working on a formula solution as well, but I got interrupted. Yours is shorter then what I had in mind, though. Could well be the simplest of solutions provided.
  6. GraH - Guido

    GraH - Guido Well-Known Member

    Messages:
    791
    On second thought, since the data is a pivot, there is raw data somewhere and power query could do the full transformation from beginning 'till end.
    nitika.manhas likes this.
  7. nitika.manhas

    nitika.manhas New Member

    Messages:
    16
    Hello All
    .......................................................................................

    Hello P45Cal,

    Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!! You are amazing

    I am new to chandoo and checked my Gmail numerous times during the weekend hoping to get an reply. I should have logged into the account. you solved my problem.

    I love excel, hope one day I could think like you!

    Thank you again!

    Regards,
    Nitika Manhas
  8. nitika.manhas

    nitika.manhas New Member

    Messages:
    16
    Thank you Grah!

    Appreciate you helping me :)

    Regards,
    Nitika Manhas

Share This Page