View Full Version : Converting an Excel Formula to Javascript
a4udi 05222012, 07:08 PM 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.21)))/(1.05)+4))
Old Pedant 05222012, 08:28 PM 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:
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.21)))/(1.05)+4)) );
}
Philip M 05222012, 08:41 PM 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:
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.21)))/(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.
a4udi 05222012, 09:03 PM 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.21)))/(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.
Old Pedant 05222012, 09:38 PM 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/>
Philip M 05232012, 09:12 AM 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.
<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.21)))/(1.05)+4)) ;
}
alert (yourFormula(2));
</script>
felgall 05232012, 11:23 AM 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.
a4udi 05232012, 01:56 PM The input values are generally 25,000 or higher.
Philip M 05232012, 05:09 PM 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.

