excelvbaexcel-formulaaveragetop-n

Average of top 3 values within a specific pivot field

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?

Solution

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