Help needed with complicated numeric formula

Question

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(    &nbsp;&nbsp;&nbsp; If([(Year) Can  Have Actual Flag],     &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If([(Prior Year Entry) Can Have  Actual Flag], [(Calc) Actual Dollars Total Gross] - [(Prior Year Entry) Actual  Cost Savings],    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [(Calc) Actual  Dollars Total Gross]),    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If([(Prior Year Entry) Can Have  Actual Flag], [(Calc) Budget Dollars Total Gross] - [(Prior Year Entry) Actual  Cost Savings],    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;If([(Prior Year Entry) Budgeted Cost  Savings] &gt; 0 or [(Prior Year Entry) Budgeted Cost Savings] &lt; 0, [(Calc)  Budget Dollars Total Gross]- [(Prior Year Entry) Budgeted Cost Savings],     &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;[(Calc) Budget Dollars Total Gross]))    &nbsp;&nbsp;&nbsp; )    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &lt; 0 and  [(Tactic/Initiative) Zero-out Negative Incremental], 0,    &nbsp;&nbsp;&nbsp; If([(Year) Can  Have Actual Flag],     &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If([(Prior Year Entry) Can Have  Actual Flag], [(Calc) Actual Dollars Total Gross] - [(Prior Year Entry) Actual  Cost Savings],    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; [(Calc) Actual  Dollars Total Gross]),    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If([(Prior Year Entry) Can Have  Actual Flag], [(Calc) Budget Dollars Total Gross] - [(Prior Year Entry) Actual  Cost Savings],    &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;If([(Prior Year Entry) Budgeted Cost  Savings] &gt; 0 or [(Prior Year Entry) Budgeted Cost Savings] &lt; 0, [(Calc)  Budget Dollars Total Gross]- [(Prior Year Entry) Budgeted Cost Savings],     &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;&nbsp;[(Calc) Budget Dollars Total Gross]))    &nbsp;&nbsp;&nbsp; )    )I want to have the same calculations but remove&nbsp;[(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&nbsp;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.

quick_basejunki · Answer

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])))

quickbasecoachd · Answer

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

Forum Discussion

Help needed with complicated numeric formula

2 Replies

Related Content

Formula URL - Edit then Add

Create a formula duration over 5 years

Looking for State abbreviation to full spelling formula

Formula Checkbox box on Numeric field

Numeric Formula with multiple conditions

Recent Discussions

Question about relationships

Reports & Charts - Managing Client-Specific Name Visibility

Summary Table Question

Google Search is back

Can I use quickbase actions within the same table?