Custom Log Return Function

[Scrabble2]

I am trying to write a function that when I input an array of numbers(through selection) the function would take value 2 and return log(value 2/Value 1) and returns an array of numbers. I thought it would be simple to do but cannot get it. Any help is appreciated or if anyone has written a similar function where it performs calculations and returns an array would be helpful to learn. thanks a lot

 

[shg]

You mean to generate a result like this?

	A​	B​	C​	D​
1​	value1​	value2​	​	​
2​	49​	59​	0.08066​	C2: =LOG(B2/A2)
3​	47​	22​	-0.32968​
4​	26​	39​	0.17609​
5​	46​	84​	0.26152​

 

[Scrabble2]

You mean to generate a result like this?

	A​	B​	C​	D​
1​	value1​	value2​
2​	49​	59​	0.08066​	C2: =LOG(B2/A2)
3​	47​	22​	-0.32968​
4​	26​	39​	0.17609​
5​	46​	84​	0.26152​

<tbody>
</tbody>

Yes but as a function in VBA where you could highlight all Column A and B and row c would be returned

 

[shg]

If it's going to operate on the selection, it would have to be a Sub, not a Function, and you would invoke it via a shortcut or the Macros dialog (Alt+F8).

If it were a function, you would select (for the example) C2:C5, paste

=ScrabbleFunc(A2:A2, B2:B5)

... into the formula bar, and confirm it with Ctrl+Shift+Enter.

So ... which?

 

[Scrabble2]

If it's going to operate on the selection, it would have to be a Sub, not a Function, and you would invoke it via a shortcut or the Macros dialog (Alt+F8).

If it were a function, you would select (for the example) C2:C5, paste

=ScrabbleFunc(A2:A2, B2:B5)

... into the formula bar, and confirm it with Ctrl+Shift+Enter.

So ... which?

The second one please, where I could use =ScrabbleFunc(A2:A2, B2:B5)

 

[shg]

Here you go:

Function ScrabbleFunc(r1 As Range, r2 As Range) As Variant
    ' returns an array log(r2/r1)
    
    Dim i           As Long
    Dim j           As Long
    Dim adOut()     As Double

    ReDim adOut(1 To r1.Rows.Count, 1 To r1.Columns.Count)
    For i = 1 To r1.Rows.Count
        For j = 1 To r1.Columns.Count
            adOut(i, j) = Log(r2(i, j).Value2 / r1(i, j).Value2) / Log(10)
        Next j
    Next i
    ScrabbleFunc = adOut
End Function

 

[Scrabble2]

Here you go:

Function ScrabbleFunc(r1 As Range, r2 As Range) As Variant
    ' returns an array log(r2/r1)
    
    Dim i           As Long
    Dim j           As Long
    Dim adOut()     As Double

    ReDim adOut(1 To r1.Rows.Count, 1 To r1.Columns.Count)
    For i = 1 To r1.Rows.Count
        For j = 1 To r1.Columns.Count
            adOut(i, j) = Log(r2(i, j).Value2 / r1(i, j).Value2) / Log(10)
        Next j
    Next i
    ScrabbleFunc = adOut
End Function

Awesome! Thanks a lot for the help

 

[shg]

You're welcome.