Converting Excel Formula to Java Script

[A_P_3]

I'm trying to convert a formula from my excel spread sheet to java script please help. I wonder if it has something to do with the LN() excel function. Based on my research Math.log is equal to LN() function in adobe. 

This is the native excel formula:

=((9/5)*(((237.3*(LN(((6.108*(2.718281828^((17.27*((5/9)*(B26-32)))/(237.3+((5/9)*(B26-32))))))-(0.00066*(1+0.00115*((5/9)*(B26-32)))*(((5/9)*(C26-32))-((5/9)*(B26-32)))*1013))/6.108)/17.27))/(1-(LN(((6.108*(2.718281828^((17.27*((5/9)*(B26-32)))/(237.3+((5/9)*(B26-32))))))-(0.00066*(1+0.00115*((5/9)*(B26-32)))*(((5/9)*(C26-32))-((5/9)*(B26-32)))*1013))/6.108)/17.27)))))+32

This is the excel formula with the cell references replaced with Field Names in adobe:

=((9/5)*(((237.3*(LN(((6.108*(2.718281828^((17.27*((5/9)*(WetBulbTempFRow1-32)))/(237.3+((5/9)*(WetBulbTempFRow1-32))))))-(0.00066*(1+0.00115*((5/9)*(WetBulbTempFRow1-32)))*(((5/9)*(AmbientTempFRow1-32))-((5/9)*(AmbientTempFRow1-32)))*1013))/6.108)/17.27))/(1-(LN(((6.108*(2.718281828^((17.27*((5/9)*(WetBulbTempFRow1-32)))/(237.3+((5/9)*(AmbientTempFRow1-32))))))-(0.00066*(1+0.00115*((5/9)*(WetBulbTempFRow1-32)))*(((5/9)*(AmbientTempFRow1-32))-((5/9)*(WetBulbTempFRow1-32)))*1013))/6.108)/17.27)))))+32

If the ambient temp is 72 F and the wet bulb temp is 55 F the answer should be 40 F. 

The only way i could get the formula to calculate something in adobe is by changing the LN to math.log in adobe but Its calculating the wrong number. The result ends up being 47 F instead of 40 F.

The adobe formula that is not working. 

((9/5)*(((237.3*(math.log(((6.108*(2.718281828^((17.27*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))/(237.3+((5/9)*(this.getField("WetBulbTempFRow1").value-32))))))-(0.00066*(1+0.00115*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*(((5/9)*(this.getField("AmbientTempFRow1").value-32))-((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*1013))/6.108)/17.27))/(1-(math.log(((6.108*(2.718281828^((17.27*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))/(237.3+((5/9)*(this.getField("WetBulbTempFRow1").value-32))))))-(0.00066*(1+0.00115*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*(((5/9)*(this.getField("AmbientTempFRow1").value-32))-((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*1013))/6.108)/17.27)))))+32

PLEASE HELP

[Bernd Alheit]

Add event.value= at the begin.

And change math.log to Math.log

[A_P_3]

With the changes you mentioned above, it is calculating to 31 but its suppose to be 40.  

This is the script based on your recommendations. 

event.value = ((9/5)*(((237.3*(Math.log(((6.108*(2.718281828^((17.27*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))/(237.3+((5/9)*(this.getField("WetBulbTempFRow1").value-32))))))-(0.00066*(1+0.00115*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*(((5/9)*(this.getField("AmbientTempFRow1").value-32))-((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*1013))/6.108)/17.27))/(1-(Math.log(((6.108*(2.718281828^((17.27*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))/(237.3+((5/9)*(this.getField("WetBulbTempFRow1").value-32))))))-(0.00066*(1+0.00115*((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*(((5/9)*(this.getField("AmbientTempFRow1").value-32))-((5/9)*(this.getField("WetBulbTempFRow1").value-32)))*1013))/6.108)/17.27)))))+32;

[try67]

It's Math.log(), not math.log(). Also, just saying "it doesn't work" is not very helpful. Please describe in detail what the results are: Is there an output at all? If so, is it incorrect? If not, is there an error message in the Console? What does it say? etc.

[A_P_3]

I did describe what the results are in the initial comment. 

"If the ambient temp is 72 F and the wet bulb temp is 55 F the answer should be 40 F. 

The only way i could get the formula to calculate something in adobe is by changing the LN to math.log in adobe but Its calculating the wrong number. The result ends up being 47 F instead of 40 F."

[try67]

That's not possible. Using math.log() will result in an error message, not an incorrect value.

[A_P_3]

I understand, but all I can do is tell you what I am seeing on my end. All I know for a fact is that this it the orginal excel formula below and I need to put it into a PDF. 

The only cell reference in formula is B 26 = Field 1  and C26 = Field 2

=((9/5) * (((237.3 * (LN(((6.108 * (2.718281828^((17.27 * ((5/9)*(B26-32))) / (237.3 + ((5/9)*(B26-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(B26-32))) * (((5/9)*(C26-32)) - ((5/9)*(B26-32))) * 1013)) / 6.108) / 17.27)) / (1 - (LN(((6.108 * (2.718281828^((17.27 * ((5/9)*(B26-32))) / (237.3 + ((5/9)*(B26-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(B26-32))) * (((5/9)*(C26-32)) - ((5/9)*(B26-32))) * 1013)) / 6.108) / 17.27))))) + 32

[A_P_3]

To verify the formula B26 can be 55 and C26 can be 72. The result should be 40.

[try67]

Are you saying the result in Excel is wrong? If so, that's not the topic of this forum. You need to figure out the right formula and then we could help you convert it to a script in Acrobat JS.

[A_P_3]

The excel formula is returning the correct result.

[Thom Parker]

I would suggest that you organize the script/calculation text so that it is easy to read and compare with the Excel formula. 

I would also suggest replacing the explicit "this.getField" calls in the Acrobat script with variables that match the cell names in Excel. 

[A_P_3]

I believe this is what you are asking me to do

=((9/5) * (((237.3 * (LN(((6.108 * (2.718281828^((17.27 * ((5/9)*(Wet Bulb Temp-32))) / (237.3 + ((5/9)*(B26-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(Wet Bulb Temp-32))) * (((5/9)*(Ambient Temp-32)) - ((5/9)*(Wet Bulb Temp-32))) * 1013)) / 6.108) / 17.27)) / (1 - (LN(((6.108 * (2.718281828^((17.27 * ((5/9)*(Wet Bulb Temp-32))) / (237.3 + ((5/9)*(Wet Bulb Temp-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(Wet Bulb Temp-32))) * (((5/9)*(Ambient Temp-32)) - ((5/9)*(Wet Bulb Temp-32))) * 1013)) / 6.108) / 17.27))))) + 32

[Thom Parker]

You are getting close. But, never (ever) use spaces in a variable name. See this article:

https://www.pdfscripting.com/public/PDF-Form-Scripting.cfm

Watch the video under the "Field Names" section. 

The idea behind reorganizing is about breaking the code into bits that make sense and can be easily understood. It should be applied to both the Excel formula and the JavaScript code.  And this is for you, so you can compare them, not just for us so we can examine the code. 

BTW: the "^" operator in JavaScript is a bitwise exclusive OR. Whereas in Excel formulas it exponentiation. 

Again, it would be best to break down the formula into parts that can be easily understood and converted, and so to make it easier to catch issues like this.

[A_P_3]

I'm so confused what you are asking me. In the intial message where i showed the Java script i tried to use, there are no spaces in the field names. I showed the original Excel formula using with the cell references and what they were. I don't know how to break it down any further than i already have. I tried to explain the formula 3 different ways in the beggining. I did change the ^ to ** and its still calculating the same.

[A_P_3]

Correction, making that change is not calculating. See below. 

This is the formula I am have in the custom calculate field now based on your suggestions.

event.value = ((9/5) * (((237.3 * (Math.log(((6.108 * (2.718281828**((17.27 * ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) / (237.3 + ((5/9)*(this.getField("WetBulbTempFRow1").value-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) * (((5/9)*(this.getField("AmbientTempFRow1").value-32)) - ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) * 1013)) / 6.108) / 17.27)) / (1 - (Math.log(((6.108 * (2.718281828 ** ((17.27 * ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) / (237.3 + ((5/9)*(this.getField("WetBulbTempFRow1").value-32)))))) - (0.00066 * (1 + 0.00115 * ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) * (((5/9)*(this.getField("AmbientTempFRow1").value-32)) - ((5/9)*(this.getField("WetBulbTempFRow1").value-32))) * 1013)) / 6.108) / 17.27))))) + 32;

[Thom Parker]

"**" was introduced as a math operator in ECMA 2016, which is not in the current version of Acrobat.  So, it causes a syntax error, which you can see in the Console Window. Press Ctrl-J to display the console. The Console is also a really good place to debug this code. 

See this video: https://www.pdfscripting.com/public/images/video/AcroJSIntro/AcroJSIntro_ConsoleWindow.cfm

Use the Math.pow() function instead.

This is a complex calculation and needs to be approached carefully, with knowledge and patience. 

A simple way to break the calculation into smaller parts is to divide it along natural barriers, such as function calls, math operations, and elements contained in parentheses. 

Start by creating variables for the field values. Like this:

var B26 = this.getField("WetBulbTempFRow1").value;

var C26 = this.getField("AmbientTempFRow1").value;

[A_P_3]

I don't have much knowlege to be able to explainin any more detail than that. Sorry.