In Excel I have a list of values (in random order), where I want to figure out which values that comprise 75% of the total value; i.e. if adding the largest values together, which ones should I include in order to get to 75% of the total (largest to smallest). I would like to find the "cut-off value", i.e. the smallest number to include in the group of values (that combined sum up to 75%). However I want to do this without first sorting my data.

Consider below example, here we can see that the cutoff is at "Company 6", which corresponds to a "cut-off value" of 750.

The data I have is not sorted, hence I just want to figure out what the "cut-off value" should be, because then I know that if the amount in the row is above that number, it is part of group of values that constitute 75% of the total.

The answer can be either Excel or VBA; but I want to avoid having to sort my table first, and I want to avoid having a calculation in each row (so ideally a single formula that can calculate it).

EDIT: My initial thought was to use percentile/quartile function, however that is not giving me the expected results. I have been trying to use a combination of percentrank, sort, sum and aggregate - but cannot figure out how to combine them, to get the result I need.

In the example I want to include Companies 1 through 6, as that summarize to 5250, hence the smallest number to include is 750. If I add Company 7 I get above the 5813 (which is where 75% is).

CodePudding user response：

VBA bubble sort - no changes to sheet.

Option Explicit

Sub calc75()

    Const PCENT = 0.75

    Dim rng, ar, ix, x As Long, z As Long, cutoff As Double
    Dim n As Long, i As Long, a As Long, b As Long
    Dim t As Double, msg As String, prev As Long, bFlag As Boolean
   
    ' company and amount
    Set rng = Sheet1.Range("A2:B11")
    ar = rng.Value2
    n = UBound(ar)
   
    ' calc cutoff
    ReDim ix(1 To n)
    For i = 1 To n
       ix(i) = i
       cutoff = cutoff   ar(i, 2) * PCENT
    Next

    ' bubble sort
    For a = 1 To n - 1
        For b = a   1 To n
            ' compare col B
            If ar(ix(b), 2) > ar(ix(a), 2) Then
                z = ix(a)
                ix(a) = ix(b)
                ix(b) = z
            End If
        Next
    Next
   
    ' result
    x = 1
    For i = 1 To n
        t = t   ar(ix(i), 2)
        If t  > cutoff And Not bFlag Then
           msg = msg & vbLf & String(30, "-")
           bFlag = True
           If i > 1 Then x = i - 1
        End If
        
        msg = msg & vbLf & i & ") " & ar(ix(i), 1) _
             & Format(ar(ix(i), 2), "  0") _
             & Format(t, "  0")
   Next
   
   MsgBox msg, vbInformation, ar(x, 1) & " Cutoff=" & cutoff

End Sub

CodePudding user response：

So, set this up simply as I suggested.

You can add or change the constraints as you wish to get the results you need - I chose Binary to start but you could limit to integer and to 1, 2 or 3 for example.

I included the roundup() I used as well as the sumproduct.

I used Binary as that gives a clear indication of the ones chosen, integer values will also do the same of course.

CodePudding user response：

Smallest Value of a Running Total...

=LET(Data,B2:B11,Ratio,0.75,
    Sorted,SORT(Data,,-1),MaxSum,SUM(Sorted)*Ratio,
    Scanned,SCAN(0,Sorted,LAMBDA(a,b,IF((a b)<=MaxSum,a b,0))),
    srIndex,XMATCH(0,Scanned)-1,
Result,INDEX(Sorted,srIndex),Result)

G2:  =SORT(B2:B11,,-1)
H2:  =SUM(B2:B11)*0.75
I2:  =SCAN(0,G2#,LAMBDA(a,b,IF((a b)<$H$2,a b,0)))
J2:  =XMATCH(0,I2#)
K2:  =INDEX(G2#,XMATCH(0,I2#)-1)

• The issue that presents itself is that there could be duplicates in the Amount column when it wouldn't be possible to determine which of them is the correct result.
• If the company names are unique, an accurate way would be to return the company name.

=LET(rData,A2:A11,lData,B2:B11,Ratio,0.75,
    Data,HSTACK(rData,lData),Sorted,SORT(Data,2,-1),
    lSorted,TAKE(Sorted,,-1),MaxSum,SUM(lSorted)*Ratio,
    Scanned,SCAN(0,lSorted,LAMBDA(a,b,IF((a b)<=MaxSum,a b,0))),
    rSorted,TAKE(Sorted,,1),rIndex,XMATCH(0,Scanned)-1,
Result,INDEX(rSorted,rIndex),Result)

• Note that you can define a name, e.g. GetCutOffCompany with the following part of the LAMBDA version of the formula:

=LAMBDA(rData,lData,Ratio,LET(
    Data,HSTACK(rData,lData),Sorted,SORT(Data,2,-1),
    lSorted,TAKE(Sorted,,-1),MaxSum,SUM(lSorted)*Ratio,
    Scanned,SCAN(0,lSorted,LAMBDA(a,b,IF((a b)<=MaxSum,a b,0))),
    rSorted,TAKE(Sorted,,1),rIndex,XMATCH(0,Scanned)-1,
Result,INDEX(rSorted,rIndex),Result))

• Then you can use the name like any other Excel function anywhere in the workbook e.g.:

=GetCutOffCompany(A2:A11,B2:B11,0.75)