Talking to a teammate, we came to the conclusion that we should use an iteractive macro, pinning one of the variables on each iteration (in our case, Net Value), and validating whether the calculated gross value was equal to the actual Gross Value or not. We’ve reached the circular expression condition. This one needs Tax1 Amt and, to close the circle, the last one needs Net Value to be calculated. ![]() But it’s not possible to calculate this without knowing Tax3 Amt. Our goal is to know Net Value, that is the basis to perform other calculations. Net Value: Gross Value - Tax1 Amt – Tax2 Amt – Tax3 Amt Tax3 Amt: (Reference Value * Tax1%) - Tax1 Amt Tax2%: Second Tax percent applied to Net Value. Tax1%: First Tax percent applied to Net Value. Reference Value: Amount established by the government based on price research, used to trigger a new tax in some cases. Sales Value or Gross Value: Final amount in the invoice, taxes included. The case below is simpler version of the original problem, based on tax calculations in a country in the southern hemisphere. ![]() I’ve recently had to use Alteryx to solve a case that was previously solved only by enabling Iterative Calculation in Excel, once the case generated a circular reference formula.
0 Comments
Leave a Reply. |