Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    Regular Coder
    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.2-1)))/(-1.05)+4))

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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)) );
    }
    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 (05-29-2012)

  • #3
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,013
    Thanks
    203
    Thanked 2,538 Times in 2,516 Posts
    Quote Originally Posted by Old Pedant View Post
    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.

    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.

  • #4
    Regular Coder
    Join Date
    Jan 2005
    Posts
    189
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Philip M View Post
    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.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,519
    Thanks
    77
    Thanked 4,381 Times in 4,346 Posts
    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.

  • #6
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,013
    Thanks
    203
    Thanked 2,538 Times in 2,516 Posts
    Quote Originally Posted by Old Pedant View Post
    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)) ;
    }
    
    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.

  • #7
    Master Coder felgall's Avatar
    Join Date
    Sep 2005
    Location
    Sydney, Australia
    Posts
    6,640
    Thanks
    0
    Thanked 649 Times in 639 Posts
    Quote Originally Posted by Philip M View Post
    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.
    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.

  • #8
    Regular Coder
    Join Date
    Jan 2005
    Posts
    189
    Thanks
    6
    Thanked 0 Times in 0 Posts
    The input values are generally 25,000 or higher.

  • #9
    Supreme Master coder! Philip M's Avatar
    Join Date
    Jun 2002
    Location
    London, England
    Posts
    18,013
    Thanks
    203
    Thanked 2,538 Times in 2,516 Posts
    Quote Originally Posted by a4udi View Post
    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.

    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.


  •  

    Posting Permissions

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