Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.

# Thread: Converting an Excel Formula to Javascript

1. ## Converting an Excel Formula to Javascript

Hi, I have a pretty basic question, I'm trying to convert a formula from Excel to Javascipt but am not sure on the javascript syntax or operators.

(x) is the column or entry being converted
The formula in Excel is:

10^((LN((4.9/(LOG(x)-1.7)-1)/(4.9/1.2-1)))/(-1.05)+4))

• Well, for starters, JavaScript's Math.log() function is the natural log (that is, same as LN() in Excel) and it doesn't have an equivalent of Excel's LOG() [which is log to base 10].

To get Excel's LOG(x) you have to use Math.log(x)/Math.log(10)

It might be worth creating your own Log10() function to keep it all simpler.

Then JS doesn't have a power "operator". Instead, to do a^b you use Math.pow(a,b).

So...Let's do this:
Code:
```function log10(n) { return Math.log(n) / Math.log(10); }

function yourFormula(x)
{
return Math.pow(10,((Math.log((4.9/(log10(x)-1.7)-1)/(4.9/1.2-1)))/(-1.05)+4)) );
}```

• ## Users who have thanked Old Pedant for this post:

a4udi (05-29-2012)

• Originally Posted by Old Pedant
Well, for starters, JavaScript's Math.log() function is the natural log (that is, same as LN() in Excel) and it doesn't have an equivalent of Excel's LOG() [which is log to base 10].

To get Excel's LOG(x) you have to use Math.log(x)/Math.log(10)

It might be worth creating your own Log10() function to keep it all simpler.

Then JS doesn't have a power "operator". Instead, to do a^b you use Math.pow(a,b).

So...Let's do this:
Code:
```function log10(n) { return Math.log(n) / Math.log(10); }

function yourFormula(x)
{
return Math.pow(10,((Math.log((4.9/(log10(x)-1.7)-1)/(4.9/1.2-1)))/(-1.05)+4)) );
}```
Something is not right! I count 9 opening and 10 closing brackets!
It would be helpful if the OP gave us an example showing the correct calculated result for a certain x.

• Originally Posted by Philip M
Something is not right! I count 9 opening and 10 closing brackets!
It would be helpful if the OP gave us an example showing the correct calculated result for a certain x.
Hi Philip,

Sorry, I took out a piece from the Excel formula that accounted for commas in the entry values, just to try and simplify the formula a bit and left a closing bracket there by mistake.
The full formula in excel would be:
=IF(E6="","",10^((LN((4.9/(LOG(E6)-1.7)-1)/(4.9/1.2-1)))/(-1.05)+4))

(E6 being x, of course.)

Old Pendant, that makes sense with the log function! Thank you, I will give that a try.

• Good eyes, Philip! Sorry. I just cloned his expression and then added the extras for the Math.pow. I was concentrating too hard on getting the log stuff right. <grin/>

• Originally Posted by Old Pedant
Good eyes, Philip! Sorry. I just cloned his expression and then added the extras for the Math.pow. I was concentrating too hard on getting the log stuff right. <grin/>
I don't like to say it, but there is still some error. I get NaN.

Code:
```<script type="text/javascript">

function log10(n) { return Math.log(n) / Math.log(10); }

function yourFormula(x) {
return Math.pow(10,((Math.log((4.9/(log10(x)-1.7)-1)/(4.9/1.2-1)))/(-1.05)+4)) ;
}

</script>```

• Originally Posted by Philip M
I don't like to say it, but there is still some error. I get NaN.
Part way through the calculation you get to

Math.log(-1.4602952059469452)

which returns NAN and then it stays NAN from there on.

This is because -1.4602952059469452 is less than zero.

It works provided that the original number is 50.1188 or larger - 50.1187 produces a negative just as anything smaller (including 2) does and therefore cannot calculate the log.

Perhaps Math.abs() around the value inside the Math.log() is the required fix.

• The input values are generally 25,000 or higher.

• Originally Posted by a4udi
The input values are generally 25,000 or higher.
OK, but it would be a good idea to restrict the value to at least 51 and avoid any possibility of an error.

•

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•