'///////////////////////////////////////////////////////////////////////////
'Faz Histograma - Freq Acumulada
Sub Histograma_R(Optional ByVal incells As Variant, Optional ByVal numclasses As Variant, Optional ByVal outcells As Variant, Optional ByVal op_plota As Variant)
    
    Dim r As Range, output As Range, plota As Boolean
    Dim i As Long, j As Long
    Dim rmax As Double, rmin As Double, rband As Double
    Dim M As Long, N As Long
    Dim cl() As Double
    Dim maisprovavel As Double
    Dim soma As Double, somaq As Double, media As Double, devP As Double, moda As Double
    Dim NP As Long
    
    
    If IsMissing(numclasses) Then
        Set r = Application.InputBox(prompt:="Enter Data Matrix:", Type:=8)
        M = Application.InputBox(prompt:="Number of classes:", Default:="20", Type:=1)
        Set output = Application.InputBox(prompt:="Enter Top Column Output Data:", Type:=8)
        plota = True
    Else
        Set r = incells
        M = numclasses
        Set output = outcells
        plota = op_plota
    End If
    
    NP = 0
    soma = 0
    somaq = 0
    rmin = 1000000#
    rmax = -1000000#
    For i = 1 To r.Rows.Count
        For j = 1 To r.Columns.Count
            If r(i, j) <> "" Then
                NP = NP + 1
                soma = soma + r(i, j)
                somaq = somaq + r(i, j) ^ 2
                If r(i, j) > rmax Then rmax = r(i, j)
                If r(i, j) < rmin Then rmin = r(i, j)
            End If
        Next j
    Next i
    
    'NP = r.Rows.Count * r.Columns.Count
    
    media = soma / NP
    devP = ((somaq - soma ^ 2 / NP) / (NP - 1)) ^ 0.5
    
    ReDim cl(M)
    
    rband = (rmax - rmin) / M
    
    If rband <> 0 Then
        For i = 1 To r.Rows.Count
            For j = 1 To r.Columns.Count
                If r(i, j) <> "" Then
                    N = Int((r(i, j) - rmin) / rband)
                    cl(N) = cl(N) + 1
                End If
            Next j
        Next i
        
        j = 0
        For i = 0 To M
            If cl(i) > j Then
                j = cl(i)
                N = i
            End If
        Next i
        moda = rmin + (N + 0.5) * rband
    Else
        moda = 0
    End If
    DoEvents
    
    j = 1:  output(j, 1) = "Média:"
            output(j, 2) = media
            output(j, 3) = "Máximo:"
            output(j, 4) = rmax
    j = 2:  output(j, 1) = "DesvPad:"
            output(j, 2) = devP
            output(j, 3) = "Mínimo:"
            output(j, 4) = rmin
    j = 3:  output(j, 1) = "Valor"
            output(j, 2) = "Frequência"
            output(j, 3) = "Freq Acumul"
            output(j, 4) = "Dist Normal"
            output(j, 5) = "Normal Acumul"
    
    
    For i = 0 To M
        j = j + 1
        If i < M Then output(j, 1) = rmin + (i + 0.5) * rband Else output(j, 1) = rmin + i * rband
        output(j, 2) = cl(i) / NP
        If i > 0 Then output(j, 3) = output(j - 1, 3) + cl(i) / NP Else output(j, 3) = cl(i) / NP
        
        'output(j, 4) = Exp(-((output(j, 1) - media) / DevP) ^ 2 / 2) / (2 * pi) ^ 0.5 / DevP / M
        If devP > 0 Then output(j, 5) = WorksheetFunction.NormDist(output(j, 1) + rband / 2, media, devP, True)
        'If i > 0 Then output(j, 5) = output(j - 1, 5) + output(j, 4) Else output(j, 5) = output(j, 4)
        If i > 0 Then output(j, 4) = output(j, 5) - output(j - 1, 5) Else output(j, 5) = output(j, 5)
    Next i
    
    'FAz o gráfico
    If plota Then
        Dim nome
        nome = ActiveSheet.Name
        Charts.Add
        For i = ActiveChart.SeriesCollection.Count To 1 Step -1
                ActiveChart.SeriesCollection(i).Delete
        Next i
    
        ActiveChart.ChartType = xlXYScatterLinesNoMarkers
        ActiveChart.ChartType = xlXYScatter
        
        ActiveChart.Location Where:=xlLocationAsObject, Name:=nome
        
        
        With ActiveChart
            .SeriesCollection.NewSeries
            .SeriesCollection(1).Name = "Histograma"
            .SeriesCollection(1).XValues = Range(output(4, 1), output(M + 4, 1)) '(valorX)
            .SeriesCollection(1).Values = Range(output(4, 2), output(M + 4, 2)) '(valorY)
            '.SeriesCollection(1).Select
            .SeriesCollection(1).Smooth = True
            .SeriesCollection(1).MarkerStyle = -4142
            .SeriesCollection(1).Format.Line.Weight = 1
            .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
            
            
            .SeriesCollection.NewSeries
            .SeriesCollection(2).Name = "Freq Acumulada"
            .SeriesCollection(2).XValues = Range(output(4, 1), output(M + 4, 1)) '(valorX)
            .SeriesCollection(2).Values = Range(output(4, 3), output(M + 4, 3)) '(valorY)
            '.SeriesCollection(2).Select
            .SeriesCollection(2).Smooth = True
            .SeriesCollection(2).MarkerStyle = -4142
            .SeriesCollection(2).Format.Line.Weight = 1
            .SeriesCollection(2).Format.Line.DashStyle = msoLineDashDotDot
            .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(255, 0, 0)
                  
                    
            .SeriesCollection.NewSeries
            .SeriesCollection(3).Name = "Normal"
            .SeriesCollection(3).XValues = Range(output(4, 1), output(M + 4, 1)) '(valorX)
            .SeriesCollection(3).Values = Range(output(4, 4), output(M + 4, 4)) '(valorY)
            '.SeriesCollection(3).Select
            .SeriesCollection(3).Smooth = True
            .SeriesCollection(3).MarkerStyle = -4142
            .SeriesCollection(3).Format.Line.Weight = 1
            .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(0, 128, 255)
            
            .SeriesCollection.NewSeries
            .SeriesCollection(4).Name = "Normal Acumulada"
            .SeriesCollection(4).XValues = Range(output(4, 1), output(M + 4, 1)) '(valorX)
            .SeriesCollection(4).Values = Range(output(4, 5), output(M + 4, 5)) '(valorY)
            '.SeriesCollection(3).Select
            .SeriesCollection(4).Smooth = True
            .SeriesCollection(4).MarkerStyle = -4142
            .SeriesCollection(4).Format.Line.Weight = 1
            .SeriesCollection(4).Format.Line.DashStyle = msoLineDashDotDot
            .SeriesCollection(4).Format.Line.ForeColor.RGB = RGB(0, 128, 255)
            
            
                    
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Valor "
            '.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Power Density (" & unidade & ")/Hz^0.5"
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Freq "
    
            .HasLegend = False
            .PlotArea.Interior.ColorIndex = xlNone
        
            '.SetElement (msoElementPrimaryCategoryAxisNone)
            '.SetElement (msoElementPrimaryValueAxisNone)
            .ChartArea.width = 330
            .ChartArea.height = 165
            .ChartArea.Top = output.Top
            .ChartArea.Left = output.Left
            
            .PlotArea.Top = 15
            .PlotArea.Left = 15
            .PlotArea.width = 315
            .PlotArea.height = 135
        
        
        End With
        
        With ActiveChart.Axes(xlCategory)
            '.MaximumScale = rmax
            '.MinimumScale = -rmax
            .HasMajorGridlines = False
            .HasMinorGridlines = False
            .MajorTickMark = xlInside
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
        With ActiveChart.Axes(xlValue)
            '.MaximumScale = 1
            '.MinimumScale = 0
            .HasMajorGridlines = False
            .HasMinorGridlines = False
            .MajorTickMark = xlInside
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
    
    
        ActiveChart.HasAxis(xlValue, xlSecondary) = True
        ActiveChart.SeriesCollection(2).AxisGroup = 2
        ActiveChart.SeriesCollection(4).AxisGroup = 2
        With ActiveChart.Axes(xlValue, xlSecondary)
            .MaximumScale = 1
            .MinimumScale = 0
            .HasMajorGridlines = False
            .HasMinorGridlines = False
            .MajorTickMark = xlInside
            .MinorTickMark = xlInside
            .TickLabelPosition = xlNextToAxis
        End With
        
        
        
    
        With ActiveChart.TextBoxes.Add(90, 30, 65, 22)
                    .Select
                    .Text = "Total Series Length: " & Str(NP) & Chr(10) & "Average:" & Format(media, "0.00") _
                            & Chr(10) & "Standard Dev: " & Format(devP, "0.00") _
                            & Chr(10) & "Most Frequent: " & Format(moda, "0.00")
                    .AutoSize = True
                    .Font.Size = 9
        End With
    
        ActiveChart.SetElement (msoElementLegendRightOverlay)
    End If
    
End Sub