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

Formula for pareto class

stormania

Member
Dear excel Master

Kindly help to solve my problem in excell, that how to determine pareto class formula ini excell to result A, B, C class ? Base on percentage by qty those data ?
Pareto class base on each code & each group.
Herewith i'm attach related file/data


Need your support

Thans in advance
 

Attachments

  • Pareto class.xlsx
    14.4 KB · Views: 9
Does this do what you require?
Code:
= LET(
      acc,        SUMIFS(qty, city, city, group, group, qty, ">="&qty),
      total,      SUMIFS(qty, city, city, group, group),
      normalised, acc / total,
      IFS(normalised<=72%,"A", normalised<=96%,"B",TRUE,"C")
  )
81947
 

Attachments

  • Pareto class.xlsx
    25.1 KB · Views: 5
The LET function appeared in Excel 365 in 2020 and is also available in Office 2021. IFS is somewhat older (Office 2019 I think).
I could regress the formula to an Office 2016 CSE formula, but there is little point if it is not giving the results you require.
My preference would be for someone else to step in because I do not like working with traditional spreadsheet methods.
 
@pecoflyer
I think it is the 'diminishing returns' relationship "It is an adage of business management that "80% of sales come from 20% of clients".

@stormania
This is a version without the LET function
Code:
= IFS(
      SUMIFS(qty, city,city, group,group, qty,">="&qty)<=(SUMIFS(qty, city,city, group,group)*72%), "A",
      SUMIFS(qty, city,city, group,group, qty,">="&qty)<=(SUMIFS(qty, city,city, group,group)*96%), "B",
      TRUE, "C"
  )
It still uses IFS though.
 
@Peter Bartholomew Thanks Peter. I wish OP's would stop using jargon not everybody understands or at least explain in plain words what they need to achieve. BTA...
This also explains why this post had 100+ views without answer
 
The Pareto Principle is the 80/20 rule. According to the 80/20 Rule (also known as the Pareto principle or the law of the vital few and trivial many), for many events, around 80% of the consequences result from 20% of the causes.
 
Back
Top