Need help with formula

Ask for Help
1

Hi guys, sorry for my dumb question (playing with glide only since 4 days…) but this thing drive me crazy: I have a complex formula from excel whoch work on excel and on google sheet…but I think the syntax is different on Glide… Here is the formula:

=1-(0,9144^EXP((12,344*LN(A2)+11,853*LN(B2)-2,664*LN(A2)*LN(B2)-7,99*LN(C2)+1,769*LN(A2)*LN(C2)+1,764*LN(D2)+7,837*E2-1,795*LN(A2)*E2+0,658*F2)-61,18))

I have this error:

image
image466×616 25.8 KB

If someone can help me for my mental health…thank you :slight_smile:

2

IMG_3570
IMG_35701290×2796 301 KB

3

I put your code in a code block above so it displays correctly in the forum. Some characters were being excluded.

This works in a javascript column. Since the javascript column can only accept 3 parameters, we have to join all input values together into one p1 parameter and split it out in the code. You will have to do that with a template column. Then you can pass the template column into a javascript column as a p1 parameter. You could have maybe done it with an excel column, but that column is one parameter short, so it would have been a lot of extra work to get the extra parameter included. Javascript is a bit easier to follow than an Excel formula.

Here is the code:

let param = p1.split('|');
let A2 = parseFloat(param[0]);
let B2 = parseFloat(param[1]);
let C2 = parseFloat(param[2]);
let D2 = parseFloat(param[3]);
let E2 = parseFloat(param[4]);
let F2 = parseFloat(param[5]);

let result = 1 - Math.pow(0.9144, Math.exp((12.344 * Math.log(A2) + 11.853 * Math.log(B2) - 2.664 * Math.log(A2) * Math.log(B2) - 7.99 * Math.log(C2) + 1.769 * Math.log(A2) * Math.log(C2) + 1.764 * Math.log(D2) + 7.837 * E2 - 1.795 * Math.log(A2) * E2 + 0.658 * F2) - 61.18));

return result;

Here is how I set up the javascript column. Just replace p1 with appropriate template column.

image
image663×824 49.7 KB

Notice that I’m using a Pipe (|) to separate each value. It’s safer than using commas because I assume your number inputs could have commas. The pipe is what you will use in your template column to separate each input value.

4

And this is why we are all grateful for your contributions!

Awesome technique

5

I use a double pipe. Because one can never be too careful :joy:

6

We process a lot of pipe delimited data for work, so it’s habit for me. But yes, you can’t be too careful. I find comma delimited to be about the worst. Pipe is pretty safe. Double Pipe is almost Murphy’s law proof.

7

Hi, thank you for your quick reply! I dont have the plan for javascriipt….and I spent one day to reverse engeener the javascript which calcul this!!! :-).

Now I have to upgrade my plan…. :slight_smile:

8

You don’t have to upgrade. The JavaScript column is available on all plans, including the Free Plan.