Find value in column where running total is equal to a certain percentage

0 votes

I want to determine in Excel which numbers make up 75% of the entire value. To put it another way, if I combine all the largest values together, which ones should I add to get to 75% of the total? (largest to smallest). Finding the "cut-off value," or the minimum number to include in the set of numbers (that add up to 75%), is what I'm looking for. But I don't want to do this before organizing my data.

As you can see in the sample below, the cutoff is at "Company 6," which translates into a "cut-off value" of 750.

I just need to determine what the "cut-off value" should be so that I can know that if the value in the row is greater than that amount, it is a part of the group of values that make up 75% of the total because the data I have is not sorted.

Excel or VBA could be the solution, however, I'd prefer to avoid sorting my data first and having a calculation in each row (so ideally a single formula that can calculate it).

Row number Amount Percentage Running Total
Company 1 1,000 12.9% 12.9%
Company 2 950 12.3% 25.2%
Company 3 900 11.6% 36.8%
Company 4 850 11.0% 47.7%
Company 5 800 10.3% 58.1%
Company 6 750 9.7% 67.7%
Company 7 700 9.0% 76.8%
Company 8 650 8.4% 85.2%
Company 9 600 7.7% 92.9%
Company 10 550 7.1% 100.0%
Total 7,750
75% of total 5,813
Feb 3, 2023 in Others by Kithuzzz
• 38,010 points
204 views

1 answer to this question.

0 votes

VBA bubble sort - no changes to the 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
answered Feb 3, 2023 by narikkadan
• 63,420 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

Excel - How can I get the average of cells where the value in one column is X and the value in another column is Y?

Use AVERAGEIFS ... =AVERAGEIFS(C2:C13,A2:A13,"Yellow Typ ...READ MORE

answered Nov 11, 2022 in Others by narikkadan
• 63,420 points
1,003 views
0 votes
1 answer

Is there a function in excel to find duplicates

Solution: You can use Conditional formatting inside the ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,420 points
409 views
0 votes
1 answer

How to find the last row in a column using openpyxl normal workbook?

ws.max_row will give you the number of rows ...READ MORE

answered Dec 25, 2022 in Others by narikkadan
• 63,420 points
4,878 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,670 points
697 views
0 votes
1 answer

Remove formulas from all worksheets in Excel using VBA

Try this : Option Explicit Sub test1() ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,409 views
0 votes
1 answer

Calculate monthly average from daily data without PivotTable

Assuming you have the months in column D enter ...READ MORE

answered Oct 3, 2022 in Others by narikkadan
• 63,420 points
1,267 views
0 votes
1 answer

Automate compound annual growth rate (CAGR) calculation

The following PowerPivot DAX formulas worked for ...READ MORE

answered Oct 7, 2022 in Others by narikkadan
• 63,420 points
969 views
0 votes
1 answer

How to find a value in an excel column by vba code Cells.Find

Just use: Dim Cell As Range Columns("B:B").Select Set cell = ...READ MORE

answered Nov 17, 2022 in Others by narikkadan
• 63,420 points
2,886 views
0 votes
1 answer

Is there any way in python to auto-correct spelling mistake in multiple rows of an excel files of a single column?

Use Spellchecker for doing your stuff: import pandas ...READ MORE

answered Oct 14, 2022 in Others by narikkadan
• 63,420 points
1,546 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP