Excel formula translation to laserfiche forms formula

asked on August 31, 2021

Greetings guys,

I had an excel formula which i had to translate to laserfiche formula.

Excel formula

=IF(D13<=0.77,IF(D13>=0.653,EXP(-(D14-F13)*((346.4228+438.8*D13)/(D13*1000)^2)*(1+0.8*(D14-F13)*(346.4228+438.8*D13)/(D13*1000)^2)),""),IF(D13<=0.7875,EXP(-(-0.00336312+2680.3206/(D13*1000)^2)*(D14-F13)*(1+0.8*(D14-F13)*-0.00336312+2680.3206/(D13*1000)^2)),IF(D13<=0.8385,EXP(-(D14-F13)*(594.5418/(D13*1000)^2)*(1+0.8*(D14-F13)*(594.5418/(D13*1000)^2))),IF(D13<=1.075,EXP(-(D14-F13)*((186.9696+486.2*D13)/(D13*1000)^2)*(1+0.8*(D14-F13)*((186.9696+486.2*D13)/(D13*1000)^2))),""))))

My translated formula in a laserfiche form field

=IF(density<=0.77,IF(density>=0.653,EXP((MINUS(1))PRODUCT(SUB(Loading_Temperature_1,20),DIV((SUM(346.4228,PRODUCT(438.8,density))),POWER((PRODUCT(density,1000)),2)),SUM(1,PRODUCT(PRODUCT(0.8,SUB(Loading_Temperature_1,20)))),DIV(SUM(346.4228,PRODUCT(438.8,density)),POWER(PRODUCT(density,1000),2)))),""),IF(density<=0.7875,EXP(MINUS(1)PRODUCT((DIV(SUM(MINUS(0.00336312),2680.3206),POWER(PRODUCT(density,1000),2))),SUB(Loading_Temperature_1,20),SUM(1,PRODUCT(PRODUCT(0.8,SUB(Loading_Temperature_1,20)),SUM(MINUS(0.00336312),DIV(2680.3206,POWER(PRODUCT(density,1000),2)))))),IF(density<=0.8385,EXP(MINUS(1)PRODUCT(SUB(Loading_Temperature_1,20),DIV(594.5418, POWER(PRODUCT(density,1000),2)), SUM(1, PRODUCT(PRODUCT(0.8, SUB(Loading_Temperature_1, 20)), DIV(594.5418, POWER(PRODUCT(density,1000),2)))))),IF(density<=1.075,EXP(MINUS(1)PRODUCT(SUB(Loading_Temperature_1,20), DIV(SUM(186.9696, PRODUCT(486.2, density)), POWER(PRODUCT(density,1000),2)), SUM(1, (PRODUCT(PRODUCT(0.8, SUB(Loading_Temperature_1, 20)), DIV(SUM(186.9696, PRODUCT(486.2, density)),(POWER(PRODUCT(density,1000),2)))))))),"")))))

But it throws an error "This field contains a calculation error".

Can anyone help please?

0 0

replied on September 2, 2021

That's a brut. these are just observations you may want to check into.

It appears you first 2 IF's, should use an AND which means when both are TRUE, run the first statement else the SECOND statement which in your case you would the next IF.

=IF(AND(density<=0.77,density>=0.653),EXP..... , IF

At the end of the second IF, you also have POWER(PRODUCT(density,1000),2)))),"") as if the ,"") was the ELSE portion of the IF statement, when instead this should be where the next IF statement should start.

EXP((MINUS(1))PRODUCT(S & EXP(MINUS(1)PRODUCT

Not sure how you are expecting these to work

If you are expecting the EXP to be the Negative value of your Product Formula, it would need to look like EXP(MINUS(PRODUCT...rest of formula )

Just a TIP, when I get into these really long formulas I work out each IF statement on there own and test, before combining into the final formula.

From the help

EXP(exponent)

The EXP function returns Euler's number (~2.718) raised to the power of the value you provide.

"=EXP(5)" returns "148.413159102577"

MINUS(value)
The MINUS function returns the opposite of the current number sign.

"=MINUS(1)" returns "-1"

" =MINUS(-1)" returns "1"

0 0

replied on March 7, 2022

Thanks, Steve. I broke the formula down into chunks and finally got it working as it should.

0 0

Question

Question

Excel formula translation to laserfiche forms formula

Replies

Sign in to reply to this post.