Discussions

Expand all | Collapse all

Help needed with complicated numeric formula

  • 1.  Help needed with complicated numeric formula

    Posted 08-17-2018 16:27
    I inherited an application with a numerical formula that I cannot figure out. The formula is currently based on if "Year Flag" or "Prior Year Flag" field is checked. The problem is that when the Flag is unchecked for a particular year then it throws off the calculation. I need the calculation to not be dependent upon if it is checked as flagged or not and no matter how I try I can't get a formula that works. Here is the current formula:

    If(

        If([(Year) Can Have Actual Flag],

                    If([(Prior Year Entry) Can Have Actual Flag], [(Calc) Actual Dollars Total Gross] - [(Prior Year Entry) Actual Cost Savings],

                                    [(Calc) Actual Dollars Total Gross]),

                    If([(Prior Year Entry) Can Have Actual Flag], [(Calc) Budget Dollars Total Gross] - [(Prior Year Entry) Actual Cost Savings],

                        If([(Prior Year Entry) Budgeted Cost Savings] > 0 or [(Prior Year Entry) Budgeted Cost Savings] < 0, [(Calc) Budget Dollars Total Gross]- [(Prior Year Entry) Budgeted Cost Savings],

                                        [(Calc) Budget Dollars Total Gross]))

        )

            < 0 and [(Tactic/Initiative) Zero-out Negative Incremental], 0,

        If([(Year) Can Have Actual Flag],

                    If([(Prior Year Entry) Can Have Actual Flag], [(Calc) Actual Dollars Total Gross] - [(Prior Year Entry) Actual Cost Savings],

                                    [(Calc) Actual Dollars Total Gross]),

                    If([(Prior Year Entry) Can Have Actual Flag], [(Calc) Budget Dollars Total Gross] - [(Prior Year Entry) Actual Cost Savings],

                        If([(Prior Year Entry) Budgeted Cost Savings] > 0 or [(Prior Year Entry) Budgeted Cost Savings] < 0, [(Calc) Budget Dollars Total Gross]- [(Prior Year Entry) Budgeted Cost Savings],

                                        [(Calc) Budget Dollars Total Gross]))

        )

    )

    I want to have the same calculations but remove [(Year) Can Have Actual Flag] and [(Prior Year Entry) Can Have Actual Flag]. Instead I want to use If(NotIsNull([(Calc) Actual Dollars Total Gross] for the Actual dollars and If(NotIsNull([(Calc) Budget Dollars Total Gross] for the Budget dollar calculations. I just seem to be missing some logic or parenthesis no matter what I come up with.

    Any help appreciated.


  • 2.  RE: Help needed with complicated numeric formula

    Posted 08-17-2018 23:09
    I'm not familiar with a function called NotIsNull, but perhaps something like the following is what you're trying to achieve.

    If([(Calc) Actual Dollars Total Gross]<>0, [(Calc) Actual Dollars Total Gross] - [(Prior Year Entry) Actual Cost Savings],

    If([(Calc) Budget Dollars Total Gross]<>0, [(Calc) Budget Dollars Total Gross] -

    If([(Prior Year Entry) Actual Cost Savings]<>0, [(Prior Year Entry) Actual Cost Savings], [(Prior Year Entry) Budgeted Cost Savings])))



  • 3.  RE: Help needed with complicated numeric formula

    Posted 08-18-2018 14:25
    I�m not going to try to debug that formula but

    Not IsNull(some value) With a space after the not just means not IsNull