View Full Version : Converting an Excel Formula to Javascript

a4udi

05-22-2012, 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.2-1)))/(-1.05)+4))

Old Pedant

05-22-2012, 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.2-1)))/(-1.05)+4)) );

}

Philip M

05-22-2012, 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.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.

a4udi

05-22-2012, 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.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.

Old Pedant

05-22-2012, 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

05-23-2012, 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.2-1)))/(-1.05)+4)) ;

}

alert (yourFormula(2));

</script>

felgall

05-23-2012, 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

05-23-2012, 01:56 PM

The input values are generally 25,000 or higher.

Philip M

05-23-2012, 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.

Powered by vBulletin® Version 4.2.2 Copyright © 2016 vBulletin Solutions, Inc. All rights reserved.