• 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.

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

N

nitika.manhas

Member
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
 

Attachments

  • upload_2018-4-13_15-7-59.png
    upload_2018-4-13_15-7-59.png
    27.3 KB · Views: 282
  • upload_2018-4-13_15-13-57.png
    upload_2018-4-13_15-13-57.png
    27.8 KB · Views: 7
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: 
=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:
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.
 
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.
 
Hello All
p45cal said:
While awaiting a workbook with data in it I stole GraH - Guido's file and:
View attachment 51462

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: 
=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")
Click to expand...
p45cal said:
While awaiting a workbook with data in it I stole GraH - Guido's file and:
View attachment 51462

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: 
=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")
Click to expand...
.......................................................................................

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
 
You must log in or register to reply here.
Back
Top