none
Log of Range RRS feed

  • Question

  • Can you take the log of a range of data? Like under optionbutton2 I am trying to update the ycol range so it is a range of natural log'd values

    Private Sub CommandButton3_Click()
    Dim xval As String
    Dim yval As String
    Dim xcol As Range
    Dim ycol As Range
    Dim ycol1 As Range
    Dim a As Double 'a is slope
    Dim b As Double 'b is intercept
    
    xval = RefEdit1.Value
    yval = RefEdit2.Value
    Set xcol = Range(xval)
    Set ycol = Range(yval)
    
    If OptionButton1 = True Then
    a = Application.WorksheetFunction.Slope(ycol, xcol)
    b = Application.WorksheetFunction.Intercept(ycol, xcol)
    MsgBox "y = ax + b" & vbNewLine & "a= " & a & vbNewLine & "b= " & b
    End If
    
    If OptionButton2 = True Then
    ycol = Log(ycol)
    a = Application.WorksheetFunction.Slope(ycol1, xcol)
    b = Application.WorksheetFunction.Intercept(ycol1, xcol)
    MsgBox a
    End If

    Sunday, March 31, 2013 7:39 PM

Answers

  • ycol = Log(ycol) won't work if ycol consists of more than one cell. You could use

        Dim cel As Range
        For Each cel in ycol.Cells
            cel.Value = Log(cel.Value)
        Next cel


    Regards, Hans Vogelaar

    Sunday, March 31, 2013 8:10 PM

All replies

  • ycol = Log(ycol) won't work if ycol consists of more than one cell. You could use

        Dim cel As Range
        For Each cel in ycol.Cells
            cel.Value = Log(cel.Value)
        Next cel


    Regards, Hans Vogelaar

    Sunday, March 31, 2013 8:10 PM
  • Is there a way I could set it up to not change the actual values? Or put the values in another column or something? 
    Sunday, March 31, 2013 9:09 PM
  • You could change

            cel.Value = Log(cel.Value)

    to

            cel.Offset(0, 1).Value = Log(cel.Value)

    This will change the cells immediately to the right of ycol. By changing the offset value 1 you can populate another column.


    Regards, Hans Vogelaar

    Sunday, March 31, 2013 9:12 PM
  • For another one I have to do it to both X and Y so I made a celx and cely. However, when I try to run it I get an "Error 5." I don't see anything wrong with my code, do you? I tried your initial code and it worked so I don't see why this one does not.

    If OptionButton3 = True Then
    For Each celx In xcol.Cells
        celx.Offset(0, 2).Value = Log(celx.Value)
    Next celx
    For Each cely In ycol.Cells
        cely.Offset(0, 2).Value = Log(cely.Value)
    Next cely
    a = Application.WorksheetFunction.Slope(cely, celx)
    b = Application.WorksheetFunction.Intercept(cely, celx)
    MsgBox a
    End If

    Sunday, March 31, 2013 9:19 PM
  • You don't want to use celx and cely in the Slope and Intercept functions - they represent single cells. You need to use the ranges populated with log values, i.e.

    a = Application.WorksheetFunction.Slope(ycol.Offset(0, 2), xcol.Offset(0, 2))
    b = Application.WorksheetFunction.Intercept(ycol.Offset(0, 2), xcol.Offset(0, 2))
    


    Regards, Hans Vogelaar

    Sunday, March 31, 2013 9:24 PM
  • Thank you so much for your help so far! You really know your stuff! I am also getting an Error 5 with the "celx.Offset(0,2).Value = Log(celx.Value)". Any idea on that one?
    Sunday, March 31, 2013 9:56 PM
  • Just kidding! can't take the log of 0!
    Sunday, March 31, 2013 9:57 PM