Converting Excel formula to FM

[Steve Martino]

Hello forum!  I was wondering if there is any 'tricks' to converting a very long Excel formula to use in FM.  The formula contains Ln( ) and Exp ( ) functions.  I tried the usual-changing the Excel cell references to FM fields, changing the commas to semicolons, but end up with entirely different results.  

I went through and (tried) to break down chunks of the formula and they match up.  But all together, still a different result.

Any tips would be appreciated.  Thanks!
I'm attaching a simple version of the SS and the FM file, just for reference.  Unless it's something obvious I don't expect anyone to delve into it for free.  If it's something someone wants to tackle, PM me and we can go from there.

Thanks

Steve
Edit: updated FM file

Simple Humidity Calc.xlsx

Simple Humidity Calc.fmp12

 

 

Edited July 20, 2022 by Steve Martino
Changed FM file/pics

[comment]

Do you not have a more readable form of the algorithm?

 

[Steve Martino]

Unfortunately I only have the spreadsheet formula I found on the internet. The formula appears to be compiled from 3 other formulas, where solving one part gets you the information for the next.

[comment]

16 minutes ago, Steve Martino said:

The formula appears to be compiled from 3 other formulas, where solving one part gets you the information for the next.

Do you have these 3 separate formulas?

FWIW, I did my own conversion of the Excel formula and I am getting a result of 78214.1485612555043233 - which is very different from both the expected result and the result you get in your FM file.

If I were in your place, I would break the formula into smaller pieces and compare the results in Excel against the results in FMP until the exact location of the discrepancy can be located.  So having those 3 separate formulas would be a good place to start. I would also use a calculation formatter such as https://www.aptworks.com/cgi-bin/calc_formatter.cgi to help with identifying the steps.

 

[Steve Martino]

I'll see if I can find it.  Thanks for looking.  The compiler is a great idea, thanks.  Already starting to find some discrepancies.

[Steve Martino]

Thanks again for your help @commentI got it...sort of.  I took out almost all the If statements and will limit user input to the first If statement parameters. I still have to work on getting the last 2 if statements out, if I can figure out where to find the closing parenthesis.

Round(
    (
        (
            (
                Airflow::cHumPSIA *
                (
                    If(
                        ( Airflow::HumCalcWB ) > 32;
                        (
                            ( 1093 - .556 * ( Airflow::HumCalcWB ) ) *
                            (
                                .621945 *
                                (
                                    Exp(
                                        -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                        -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                    )
                                ) /
                                (
                                    ( Airflow::cHumPSIA ) -
                                    (
                                        Exp(
                                            -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                            -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                        )
                                    )
                                )
                            ) - .24 *
                            ( ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) )
                        ) /
                        ( 1093 + .444 * ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) );
                        (
                            ( 1220 - .04 * ( Airflow::HumCalcWB ) ) *
                            (
                                .621945 *
                                (
                                    Exp(
                                        -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                        -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        6.5459673 * Ln( Airflow::HumCalcWB + 459.67 )
                                    )
                                ) /
                                (
                                    ( Airflow::cHumPSIA ) -
                                    (
                                        Exp(
                                            -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                            -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                        )
                                    )
                                )
                            ) - .24 *
                            ( ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) )
                        ) /
                        (
                            1220 + .444 * ( Airflow::HumCalcDB ) - .48 * ( Airflow::HumCalcWB )
                        )
                    )
                )
            ) /
            (
                .621945 +
                (
                    If(
                        ( Airflow::HumCalcWB ) > 32;
                        (
                            ( 1093 - .556 * ( Airflow::HumCalcWB ) ) *
                            (
                                .621945 *
                                (
                                    Exp(
                                        -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                        -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                    )
                                ) /
                                (
                                    ( Airflow::cHumPSIA ) -
                                    (
                                        Exp(
                                            -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                            -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                        )
                                    )
                                )
                            ) - .24 *
                            ( ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) )
                        ) /
                        ( 1093 + .444 * ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) );
                        (
                            ( 1220 - .04 * ( Airflow::HumCalcWB ) ) *
                            (
                                .621945 *
                                (
                                    Exp(
                                        -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                        -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                        ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                        6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                    )
                                ) /
                                (
                                    ( Airflow::cHumPSIA ) -
                                    (
                                        Exp(
                                            -10440.397 / ( Airflow::HumCalcWB + 459.67 ) + -11.29465 +
                                            -.027022355 * ( Airflow::HumCalcWB + 459.67 ) + .00001289036 *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            -.0000000024780681 * ( Airflow::HumCalcWB + 459.67 ) *
                                            ( Airflow::HumCalcWB + 459.67 ) * ( Airflow::HumCalcWB + 459.67 ) +
                                            6.5459673 * Ln( ( Airflow::HumCalcWB + 459.67 ) )
                                        )
                                    )
                                )
                            ) - .24 *
                            ( ( Airflow::HumCalcDB ) - ( Airflow::HumCalcWB ) )
                        ) /
                        (
                            1220 + .444 * ( Airflow::HumCalcDB ) - .48 * ( Airflow::HumCalcWB )
                        )
                    )
                )
            )
        ) /
        (
            Exp(
                -10440.397 / ( Airflow::HumCalcDB + 459.67 ) + -11.29465 +
                -.027022355 * ( Airflow::HumCalcDB + 459.67 ) + .00001289036 *
                ( Airflow::HumCalcDB + 459.67 ) * ( Airflow::HumCalcDB + 459.67 ) +
                -.0000000024780681 * ( Airflow::HumCalcDB + 459.67 ) *
                ( Airflow::HumCalcDB + 459.67 ) * ( Airflow::HumCalcDB + 459.67 ) +
                6.5459673 * Ln( ( Airflow::HumCalcDB + 459.67 ) )
            )
        )
    ) * 100;
    2
)

[comment]

19 minutes ago, Steve Martino said:

I got it...sort of.

 So did you figure out what caused the difference? I am curious, because AFAICT the only functions being used here are If, Ln and Exp - and I think they should behave the same in FMP as they do in Excel.

 

23 minutes ago, Steve Martino said:

if I can figure out where to find the closing parenthesis

Any decent text editor should be able to do that for you.

 

[Steve Martino]

I'm trying on the parenthesis.  All the function work the same.  I isolated the Ln & Exp first, and they were the same.

I think what was happening is maybe Excel or FM somehow does something different with the parenthesis in the If statement or just in the overall calculation. It seems like the Excel used more (or maybe just the original author), or maybe just the way it was written in Excel.  I started from the bottom pulling out If statements in the data viewer, counting open/close parenthesis.  I had to add and subtract them to zero out, and all of a sudden, the right number appeared.  I also changed numbers, i.e   12345e-4 to .00012345, but I don't think it may have been necessary, but I did that first.
That's not a good description, but I think you get the gist.
Thank again for you help and guidance.

[comment]

18 minutes ago, Steve Martino said:

maybe Excel or FM somehow does something different with the parenthesis

I don't think so. Not to mention that the formula has so many redundant parentheses that it's very unlikely the order of evaluation could be any different.

18 minutes ago, Steve Martino said:

I also changed numbers, i.e   12345e-4 to .00012345,

Ok, that's weird because I don'r see any numbers in scientific notation.

 

Edited July 20, 2022 by comment

[Steve Martino]

I took out the scientific notation before the first post.  I cleaned up all the redundancy with the let function.  In Excel it was just converting the same number many times to absolute temperature.
More manageable now.

Thanks again for your help!

Steve

Let(
    [
        @db = Airflow::HumCalcDB;
        @wb = Airflow::HumCalcWB;
        @wbK= @wb + 459.67;
        @dbK= @db + 459.67;
        @psia= Airflow::cHumPSIA;
        @exp= Exp(-10440.397 / @wbK + -11.29465 + -.027022355 *
                  @wbK + .00001289036 * @wbK *
                  @wbK + -.0000000024780681 * @wbK *
                  @wbK * @wbK + 6.5459673 *
                  Ln(@wbK)
                 );
        @calc =
        ((@psia * ((1093 - .556 *(@wb))*(.621945 *(@exp) 
      / ((@psia) - (@exp))) - .24 * ((@db) - (@wb))) 
      / ( 1093 + .444 * (@db ) - (@wb))) 
      / (.621945 + (((1093 - .556 * (@wb)) * (.621945 * (@exp) 
      / (( @psia ) - (@exp))) - .24 * ((@db) - (@wb))) 
      / (1093 + .444 * (@db) - (@wb))))) 
      / (Exp(-10440.397 / @dbK + -11.29465 + -.027022355 * @dbK + .00001289036 * @dbK * @dbK + -.0000000024780681 * @dbK *
 @dbK * @dbK + 6.5459673 * Ln(@dbK)))

    ];

    Round( @calc * 100; 2 )
)

Edited July 20, 2022 by Steve Martino