Calculated column not calculating division correctly

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a summary table where I am adding a calculated column. Calculated column = ([Field 1]-[Field 3])/([Field 1]+[Field 2]+[Field 3]) Calculated column is not displaying the correct value based on the formula above. I assume it is user error as this formula works in excel as attached. I appreciate any help you can provide!
Photo of Mark

Mark

  • 30 Points

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
The "calculated column" on a Summary report is a bit misleading.  You are actually creating a pseudo fields at the detail level, not the summary level.

So, for example if you had two batches of productions and 1000 widgets were produced and 90% failed, and another small batch of 100 were produced and only 10% failed, the average batch failure rate would calculate to be 50%.  But in fact, to get a proper calculation, you need to weight the calculation by the batch size.

I suspect that is what is happening on your case.


If you describe your real world situation I may be able to offer some suggestions.
Photo of Mark

Mark

  • 30 Points
Thank you Mark, that is exactly what is occurring. I ended up summarizing the data to a parent table and have added a numeric summary field for the calculation and it is now working correctly. I appreciate the help!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Yes, that is exactly the solution.  QuickBase Engineers know that many users get tripped up and want to do what they rightfully feel is stupid simple math on the "columns" of a Summary report. Hopefully in a future release you won't have to  summarize up to a Parent to get proper totals to do math on.