add totals from 2 fields (duh?)

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

Boy do I feel stupid. I have two formula numeric fields, we'll call them "those months" and "this month". These two fields record the numeric value in a record if the formula criteria are met. In a summary report they both return proper totals. The third formula numeric field looks like this . . . [those months] + [this month]  . . . and in the same summary report it returns squat, just a big empty field. Surely it's a simple fix but I can't get on my own, please help.

Thanks,

Rp

Photo of Ryan

Ryan

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Usually when a summary total goes blank, its because one or more values are null, null being not the same as zero.  Can you check your two formuals  as well as the third formula to be sure that they cannot calculate to a blank (null).  Since your two formulas are totaling, i would suspect that your third formula might have some values calculating to null.   The only other situation I have seen has to do with circular relationships.  Those can be a bugger to resolve.
Photo of Ryan

Ryan

  • 0 Points
First, A huge THANKS for a timely and well communicated response. I hate to seem dumb, but since that train already left the station, how would I know if a field can calculate to "null"?  Would it have anything to do with the "treat blanks as zero in calculations" checkbox that I see in some, but not these, fields? While I'm at it, would a field need to be part of a table to table relationship in order to create a circular relationship?

More thanks!

Rp
Can you post the three formuals involved?
Photo of Ryan

Ryan

  • 0 Points
Sure,

Field Name: "Month - Prior Months Total Target"  Formula: If([Month - Prior]=true,[Target Total])
Filed Name: "Month - Current MTD Total Target"  Formula: If([Month - Current]=true,[Target Total]*[Month - percent passed])
Filed Name: "YTD Total SIP Target"  Formula:  [Month - Prior Months Total Target]+[Month - Current MTD Total Target]

The first two seem to be working fine, the last one is causing the problem. In the spirit of thoroughness, the formula for the field referenced in the second formula is:

Filed Name: "Month - percent passed"   Formula:  If([Month - Current]=true,[Month - Day num]/[Month - days in each])
Right, so for example this formula

If([Month - Prior]=true,[Target Total])

If the month prior is false, then this formula is silent on the result - which makes it null.  You need to have these formuals all calculate to "else zero" if they do not pass the test.

If([Month - Prior]=true,[Target Total],0)
If([Month - Current]=true,[Target Total]*[Month - percent passed],0)

That should fix it.
Photo of Ryan

Ryan

  • 0 Points
You sir, are a steely-eyed missile-man! If there is ever anything I can do for you (obviously not in QB) let me know.
Photo of Brenda Wenger

Brenda Wenger

  • 80 Points 75 badge 2x thumb
I am trying to add together the results in 2 simple multiplication formula fields and the result in the addition formula field is coming back as 0 and don't know why. 

First formula is Quantity 1*Unit Price 1 (shows result in Amount 1 field)

Second Formula is Quantity 2*Unit Price 2 (shows result in Amount 2 field)

Third formula is Amount 1+Amount 2 (suppose to show results in Total Purchase Order)

Third formula is the one that comes back as 0 even when there are amounts showing if fields Amount 1 or Amount 2.
Are there values in the first two fields on a sample record? Are all three fields formula numeric field types?
Photo of Brenda Wenger

Brenda Wenger

  • 80 Points 75 badge 2x thumb
All 3 are numeric fields.  The default value in the first two fields is 0, but even when there are other values showing in the first two fields, the third field still shows 0.  e.g. first field's result is 10, second field's result is 10, the third field's result should be 20, but it is not doing that.
Is there any possibility of a circular formula?  Is [Unit Price 1] a data entry field or a formula derived from other fields which use the value for the third field?

You could try making the field over again from scratch just to be sure that the third field was not corrupted on some weird way.