Generate chart from highest three values in a table

[sogs0707]

Hello all,

I have a table which lists categories with percentages which will change every month. I want to have a chart which will automatically pull the highest three values from the table. Is that possible?

Thanks,

Sogs0707

 

[Momentman]

What you should do is to set up a small table which uses formulas to pick the highest values from your data. Using a function like LARGE

So its the smaller table with the highest 3 values that you then plot,. Once the big table changes, the smaller table('cos it uses formulas to pick) updates as well

 

[sogs0707]

What you should do is to set up a small table which uses formulas to pick the highest values from your data. Using a function like LARGE

So its the smaller table with the highest 3 values that you then plot,. Once the big table changes, the smaller table('cos it uses formulas to pick) updates as well

A great starting point. I shall give it a try, thank you!

 

[ParamRay]

.
.

Try something like this:

Sub AddColumnChart()
    
    'Adds a clustered column chart
    'to the active sheet
    'using the 3 largest series values
    'from the specified range
    
    Dim s_vals As Range
    Dim add As String
    Dim cht As Chart
    
    Set s_vals = ActiveSheet.Range("A1:A16")    'change this line
                                                'to the range
                                                'containing your
                                                'series values
    
    add = "=" & _
        LargeAdd(s_vals, 1) & "," & _
        LargeAdd(s_vals, 2) & "," & _
        LargeAdd(s_vals, 3)
    
    Set cht = ActiveSheet.Shapes.AddChart.Chart
    
    With cht
        .ChartType = xlColumnClustered
        .SeriesCollection.NewSeries
        .SeriesCollection(1).Values = add
    End With

End Sub

'--------------------------------------------------------------------------

Private Function LargeAdd(rng As Range, k As Long) As String

    'Returns the address
    'of the k-th largest value
    'in a range
    
    LargeAdd = rng.Parent.Name & "!" & _
        rng.Find(WorksheetFunction.Large(rng, k)).Address

End Function