Average of top 3 values within a specific pivot field

Question

I have a table in which in one column I have values that are assigned to special models of hardware. The table looks like this:

Screenshot

We need to find an average of three of the highest numbers for each model in that table, for example:

We have a model 22PFx4109, we need to get an average of three of the highest numbers from values assigned to it. So for 22PFx4109 it would be average(278,279,289). Then we need to do it for each of the other models. The result has to be displayed at the end of each model value list, so here it would be displayed to the right of 289 value.

I have failed to write a function that would be able to do that. Could you please help me with that using VBA? Or maybe it's possible to do it with a function only?

score 1 · Accepted Answer · answered May 23 '15 at 01:12

As for formula solution, if the cell containing "OUTPUT" was C3, the following formula should be put in D4 and pasted downwards.

=IF(C4="","",IF(C5<>"","",IF(C3="",C4,AVERAGE(C2:C4))))

This will only work if your top 3 values are at the bottom of every group (model).

score 0 · Answer 2 · answered May 22 '15 at 20:13

0

try this formula:

AVERAGE(OFFSET(E1,COUNTA(E:E)-3,0,3,1))

in my example, in E1 I have the header for the column and no values after the last value of that column (to use the counta)

answered May 22 '15 at 20:13

Federico Sanchez

145
1
2
12

It seems that this formula finds the average of the 3 most bottom values. However, OP needs such averages for each "model", not just for 3 values at the bottom. – ZygD May 23 '15 at 00:48

ZygD · Answer 3 · 2015-05-23T01:18:48.090

Seeing your pivot table I created a very similar one.

The following sub works for me, I believe it will work for you too :)

Sub Average_Top3()

    Const top As Byte = 3
    Dim pi As PivotItem
    Dim pi2 As PivotItem
    Dim i As Byte
    Dim vSUM As Variant
    Dim dAVG As Double
    Dim rng As Range

    With ActiveSheet.PivotTables(1)
        For Each pi In .PivotFields("WORK_DATE").PivotItems
            For Each pi2 In .PivotFields("ID_MODELU").PivotItems
                Set rng = Intersect(pi.DataRange, pi2.DataRange)
                If Not rng Is Nothing Then
                    dAVG = 0
                    vSUM = 0
                    For i = 1 To top
                        vSUM = vSUM + Application.WorksheetFunction.Large(rng, i)
                    Next
                    dAVG = Round(vSUM / top, 2)
                    rng.Cells(rng.Rows.Count).Offset(0, 1).Value2 = dAVG
                    Set rng = Nothing
                End If
            Next
        Next
    End With

End Sub

Even if your top 3 values were not at the bottom of every group (model), this sub would still work.

score -1 · Answer 4 · edited May 23 '15 at 00:55

-1

You can also try below formula:

=AVERAGE(LARGE(C:C,1),LARGE(C:C,2),LARGE(C:C,3))

edited May 23 '15 at 00:55

L42

19,427
11
44
68

answered May 22 '15 at 20:31

snb

333
1
4

This solution would yield only one average value for the whole column, while the OP wants separate averages for different groups. – ZygD May 23 '15 at 00:37
@ZygD is right, I need separate averages, thank you for clarification. – NakedCat May 24 '15 at 10:34

Average of top 3 values within a specific pivot field

4 Answers4