Solved

Inquiry formula - multiple mathematical steps

  • 10 November 2023
  • 4 replies
  • 124 views
T
Rank
Userlevel 4
Badge

Group,

I have an inquiry that I’m working on and struggling with the formula.  I either get the “divide by zero error encountered” message, or syntax error.  

 

I need to take this formula already set in the inquiry, (this gives us our Average Inventory)

 =([INItemCostHist.FinBegCost]+[INItemCostHist.FinYtdCost])/2 

and divide it by: (This gives us the Sales COGS)

=([INItemCostHist.FinPtdQtySales] + [INItemCostHist.TranPtdQtyAMAssemblyOut]) * [INItemCost.AvgCost]

 

This final number that I am attempting to find is the inventory Turns for my Supply Chain manager. He can achieve this in excel, but I wanted to try and give this to him in the report.

 

I have tried many different ways to write it and sometimes it says it validates, other tries it doesn’t.  I did find another post on in the community that talked about the divide by zero error and tried different ways of writing the code similar to that result but haven’t gotten it to work.

Does Acumatica allow this type of formula in a GI?

Thank you for any pointers.

Trisha

icon

Best answer by BenjaminCrisman 10 November 2023, 16:46

View original

4 replies

BenjaminCrisman
Rank
Userlevel 7
Badge +4

 @tveld It seems like the 0 value results are throwing off the case, are they necessary to include?

If not then I would recommend setting a filter condition to make sure that field only returns results > 0

If they are still needed then the recommendation would be to include an if statement as part of the function to check for 0 values and then make an adjustment to the expression based off that.

T
Rank
Userlevel 4
Badge

@BenjaminCrisman ,

Thank you, I did check with the manager, and he wants to keep those zero items in the report.  To keep that in there, what is the best way to show the formula?  I’ve tried multiple ways and haven’t been able to find the right combination.

Thank you!

T
Rank
Userlevel 4
Badge

This is the way I have the formula currently written.  It comes back with the error “outside the bounds of array”.

=iif([INItemCostHist.FinBegCost]+[INItemCostHist.FinYtdCost])/2 / IIF( ([INItemCostHist.FinPtdQtySales] + [INItemCostHist.TranPtdQtyAMAssemblyOut]) * [INItemCost.AvgCost], 0 ) 

Thank you

T
Rank
Userlevel 4
Badge

I was able to find my correct formula.  Thanks for the help!

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved