Creating graphs from data 1

[abienz]

Ho there I've got a question that I hope will be farely easy to answer.

I have a single column in Excel with about 60 different rows, the column is titled companyID and the values in the rows are either 1, 2, or 3.

I want to create a pie chart that will display the percentage of each value from the total number of rows.
E.G. if there are 6 1's then it will have 10% for the 1 value etc.

I've tried selecting the whole column and clicking the graph button but this just gives me a pie chart with 60 different values.

Thanks in advance!

Cheers,
Al.

 

[DaleWatson123321]

Hi Al,

Simple solution:

In 3 separate cells, use the following formula:

=SUMIF(A1:A100,"1&quot,
=SUMIF(A1:A100,"2&quot,
=SUMIF(A1:A100,"3&quot

This should work just fine.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[logius]

Well, here's what I have. Bear in mind that I threw this together in about 6 minutes. It works, but the data is rounded up (stinkin autoformatting).

Sub ThisIsOnlyATest()
Dim ThisRange As Range
Dim CellInRange As Object
Dim Total1 As Integer
Dim Total2 As Integer
Dim Total3 As Integer
Dim Percent_1 As Long
Dim Percent_2 As Long
Dim Percent_3 As Long
Dim ctname As String


    'Selecting range (this was the tricky part)
    Set ThisRange = Range("A2", Range("A2").End(xlDown))
    
    For Each CellInRange In ThisRange
        With CellInRange
            If .Value = 1 Then
                Total1 = Total1 + 1
            Else
                If .Value = 2 Then
                    Total2 = Total2 + 1
                Else
                    Total3 = Total3 + 1
                End If
            End If
        End With
    Next CellInRange
    
    Percent_1 = ThisRange.Count / Total1
    Percent_2 = ThisRange.Count / Total2
    Percent_3 = ThisRange.Count / Total3
    
    Range("C1").Value = Percent_1
    Range("C2").Value = Percent_2
    Range("C3").Value = Percent_3
    
    Set ThisRange = Range("C1", Range("C1").End(xlDown))
    ThisRange.Select
    
   'Creating the chart
    Charts.Add
    
    'Selecting Chart type
    ActiveChart.ChartType = xlPie
    
    'Embeds chart in current worksheet
    ActiveChart.Location Where:=xlLocationAsObject, _
                         Name:="Sheet1"
    
    'Can't have a chart without a title
    ctname = InputBox("Enter a title for the chart", _
                      "Title Prompt")
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Caption = ctname

End Sub

 

[logius]

Whoops! Instead of the

Total2 = Total2 + 1

I should have put

Total2 = Total2 + 2

and likewise for Total3. Sorry, misunderstood what you wanted.

 

[DaleWatson123321]

Hi Al,

Thanks for the STAR,

I tried your code, and while it is interesting, the numbers I got didn't seem to be accurate.

I too proved myself "human" - i.e. the formula I first sent was a "brief" example. As you know, it should have used "COUNTIF" and NOT "SUMIF".

The "complete" formulas for a "percentages" (which you probably now have already created) are:

=ROUND(COUNTIF($A$2:$A$100,"1&quot/COUNTA($A$2:$A$100)*100,0)

=ROUND(COUNTIF($A$2:$A$100,"2&quot/COUNTA($A$2:$A$100)*100,0)

=ROUND(COUNTIF($A$2:$A$100,"3&quot/COUNTA($A$2:$A$100)*100,0)

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca