Jump to content

Converting Excel formula to FM


This topic is 617 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

image.png.17bf2d0c40819843f080c35b040162c1.png

Simple Humidity Calc.xlsx

Simple Humidity Calc.fmp12

 

 

Edited by Steve Martino
Changed FM file/pics
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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
)

Link to comment
Share on other sites

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.

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 by comment
Link to comment
Share on other sites

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 by Steve Martino
Link to comment
Share on other sites

This topic is 617 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.