Skip to main content
Answer

Inquiry formula - multiple mathematical steps

  • November 10, 2023
  • 4 replies
  • 334 views
T
Freshman I
Forum|alt.badge.img

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

Best answer by BenjaminCrisman

 @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.

    L

    4 replies

    BenjaminCrisman
    Acumatica Employee
    Forum|alt.badge.img+4
    • BenjaminCrisman
    • Acumatica Support Team
    • Answer
    • November 10, 2023

     @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.

    Chris Hackett
    T
    Freshman I
    Forum|alt.badge.img
    • tveld
    • Author
    • Freshman I
    • November 10, 2023

    @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
    Freshman I
    Forum|alt.badge.img
    • tveld
    • Author
    • Freshman I
    • November 10, 2023

    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
    Freshman I
    Forum|alt.badge.img
    • tveld
    • Author
    • Freshman I
    • November 14, 2023

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

    Chris Hackett
    Terms & ConditionsCookie settingsAccessibility statement
    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