Enjoy an ad free experience by logging in. Not a member yet? Register.

Results 1 to 9 of 9

05222012, 06:08 PM #1
 Join Date
 Jan 2005
 Posts
 189
 Thanks
 6
 Thanked 0 Times in 0 Posts
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.21)))/(1.05)+4))
05222012, 07:28 PM
#2
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.21)))/(1.05)+4)) ); }
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
Users who have thanked Old Pedant for this post:
a4udi (05292012)
05222012, 07:41 PM
#3
 Join Date
 Jun 2002
 Location
 London, England
 Posts
 17,918
 Thanks
 203
 Thanked 2,531 Times in 2,509 Posts
All the code given in this post has been tested and is intended to address the question asked.
Unless stated otherwise it is not just a demonstration.
05222012, 08:03 PM
#4
 Join Date
 Jan 2005
 Posts
 189
 Thanks
 6
 Thanked 0 Times in 0 Posts
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.
05222012, 08:38 PM
#5
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/>
An optimist sees the glass as half full.
A pessimist sees the glass as half empty.
A realist drinks it no matter how much there is.
05232012, 08:12 AM
#6
 Join Date
 Jun 2002
 Location
 London, England
 Posts
 17,918
 Thanks
 203
 Thanked 2,531 Times in 2,509 Posts
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.21)))/(1.05)+4)) ; } alert (yourFormula(2)); </script>
All the code given in this post has been tested and is intended to address the question asked.
Unless stated otherwise it is not just a demonstration.
05232012, 10:23 AM
#7
 Join Date
 Sep 2005
 Location
 Sydney, Australia
 Posts
 6,620
 Thanks
 0
 Thanked 645 Times in 635 Posts
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.
Stephen
Learn Modern JavaScript  http://javascriptexample.net/
Helping others to solve their computer problem at http://www.felgall.com/
Don't forget to start your JavaScript code with"use strict";
which makes it easier to find errors in your code.
05232012, 12:56 PM
#8
 Join Date
 Jan 2005
 Posts
 189
 Thanks
 6
 Thanked 0 Times in 0 Posts
The input values are generally 25,000 or higher.
05232012, 04:09 PM
#9