I need to calculate an index that is to be derived like this:

If we have some data: 850 700 500 480 300 100 50, we first sort it from the large to small:


Then calculate the sum: 2980.

Then we take a given number of percents, for example, 50% and get a 50% of the sum:1490. Then we go down the data, calculating the sum for the given row plus the sum of all the rows prior:

850    850
700    1550
500    2050
480    2530
300    2830
100    2930
50     2980

Once the sum in the second row equals or exceed the 50% of the total sum = 5050 we take the corresponding number from the first row: 700. So that number is the aswer.

What is that number called? I need to know to find an appropriate excel function to calculate it.

  • $\begingroup$ Your procedure, for $50\%$, is a greedy algorithm that does successfully generate a set of the smallest size that contains a majority of the total. It doesn't have to have a name for you to be able to make an excel function for it. $\endgroup$ – Zubin Mukerjee Nov 26 '17 at 18:50
  • $\begingroup$ For this I would recommend writing a VBA Excel macro $\endgroup$ – Zubin Mukerjee Nov 26 '17 at 18:53
  • $\begingroup$ @Zubin Mukerjee I have a feeling a function for this already exists, it looks like some common statistical index to me. I just don't know its name. $\endgroup$ – user78920 Nov 26 '17 at 18:55
  • $\begingroup$ This question had been better asked at stats.stackexchange.com I think you can answer your question yourself, after reading stats.stackexchange.com/questions/137931/… $\endgroup$ – kjetil b halvorsen Nov 26 '17 at 19:43
  • 1
    $\begingroup$ @kjetil b halvorsen So it's called tantile? Thank you for the link. It's weird how I can hardly google anythin for that word. It must have a very rare use. $\endgroup$ – user78920 Nov 26 '17 at 20:00

Still no idea how it's called, but here's how I implemented the search for it in vba:

Function getSecondAverage(tempArray() As Variant)
    Dim globals As Object
    Set globals = getGlobalVariables()

    Dim arr As Object
    Set arr = CreateObject("System.Collections.ArrayList")

    Dim elements_sum As Double
    elements_sum = 0
    Dim i As Long
    For i = LBound(tempArray) To UBound(tempArray)
        arr.Add tempArray(i)

        elements_sum = elements_sum + tempArray(i)


    Dim percentile_value As Double
    percentile_value = _
        globals("DEBT_DIVIDE_PERCENTILE_PERCENT") * elements_sum

    Dim accumulated_sum As Double
    accumulated_sum = 0

    Dim element As Variant
    For Each element In arr
        accumulated_sum = accumulated_sum + element

        If accumulated_sum >= percentile_value Then
            getSecondAverage = element
            Exit Function
        End If
End Function

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.