0

I have a formula in excel that is as follows:

=Round(33.468*(Log(Log(M+0.8)))+23.097;2)

as i have a large amount of data and need to use this frequently i created a UDF based off this formula as follows:

Function visco(M)

visco = Round(33.468 * (Log(Log(M + 0.8))) + 23.097, 2)

End Function

however when i plug in a value for M the value in each is different, the excel formula directly in the cell is correct whereas the UDF result is wrong.

I am unsure what has happened here if anyone is able to explain

thanks

iversen92
  • 13
  • 2
  • 1
    Neither `VBA.Round` nor `VBA.Log` are the same as the worksheet functions. If you want to get the same result, you could use `WorksheetFunction.Round` and `WorksheetFuncction.Log` Examine `HELP` for those functions to help understand the differences. – Ron Rosenfeld Oct 13 '20 at 23:24

1 Answers1

0

Log in vba does not equal LOG in the worksheet. in vba it is Log(e) not Log(10).

Either use worksheet function:

visco = Round(33.468 * (Application.Log(Application.Log(M + 0.8))) + 23.097, 2)

or divide by the log of the base desired.

visco = Round(33.468 * ((Log((Log(M + 0.8) / Log(10#))) / Log(10#))) + 23.097, 2)
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
  • Don't forget `Round()` also ! https://stackoverflow.com/questions/265926/round-function-in-excel-worksheet-function-vs-vba – Tim Williams Oct 13 '20 at 23:23