# Custom Log Return Function

#### Scrabble2

##### New Member
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

### Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

#### shg

##### MrExcel MVP
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

##### New Member
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

##### MrExcel MVP
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

##### New Member

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

##### MrExcel MVP
Here you go:

Code:
``````Function ScrabbleFunc(r1 As Range, r2 As Range) As Variant
' returns an array log(r2/r1)

Dim i           As Long
Dim j           As Long

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
End Function``````

#### Scrabble2

##### New Member
Here you go:

Code:
``````Function ScrabbleFunc(r1 As Range, r2 As Range) As Variant
' returns an array log(r2/r1)

Dim i           As Long
Dim j           As Long

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
End Function``````

Awesome! Thanks a lot for the help

You're welcome.

Replies
1
Views
15
Replies
4
Views
75
Replies
5
Views
76
Replies
6
Views
69
Replies
5
Views
58