QB Formula not calculating numeric currency formula correctly

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered
I have a weird issue going on in my application. I have a total allotment field which calculates dollars allocated to several departments based on several other fields. This field is a formula which states: (Take field A / the overall total for field A)*5000), else 0.

The idea is that each record in my table will get an allocation of the $5000 dollars with the thinking that when this field is totaled up it would also equal 5000 (no money left over!). However, QB calculates the total for this field as less than 5000. When I put it into excel, the values for each row are different by a few decimal points or so and the total comes to 5000.

I'm not sure how to fix this issue in QB, please help!
Photo of Niraj

Niraj

  • 0 Points

Posted 5 years ago

  • 0
  • 1
Photo of Jack

Jack, Champion

  • 50 Points
Dear Niraj,

This sounds like a rounding issue, you could try the following:

ROUND(Take field A / the overall total for field A)*5000)


This will round it to the nearest whole number.

If you prefer to the nearest cent you could try:

ROUND((Take field A / the overall total for field A)*5000),0.01)

If the above two suggestions don't quite work for you there is further guidance on number functions available here:

https://www.quickbase.com/help/default.html#using_formulas_in_quickbase.html


With examples here:

https://www.quickbase.com/db/6ewwzuuj?a=q&qid=6


Hope this helps

Jack
Photo of Niraj

Niraj

  • 0 Points
Thanks, Jack.

This is helpful. However, often times the formula calculates to much more than a few dollars off. It's very strange as I replicate the formula in Excel and it, somehow, calculates to the way it is supposed to. I'm sure I'm doing something incorrect in QB or have just overlooked something, but I can't figure it out.
Photo of Jack

Jack, Champion

  • 50 Points
You could then try:

ROUND(ROUND((Take field A / the overall total for field A),0.01)*5000),0.01)

This will round up the answer before its multiplied, then also round it again after its multiplied.

Note you can amend the value 0.01 to 0.1 or 1 or 10 to change the way it rounds to suit your needs.

If this still doesnt work could you give me an example of numbers in your fields and what the result is in QB and the numbers in excel and the result?

Jack