Conditional calculation of an average of multiple ...

Felipe_Engelber · 07-07-2019 08:00 PM

Hi everyone, I step into the following issue. I wrote a formula using an IF statement that outputs different averages formulas depending on a certain condition. Is a very long formula:

IF(([Pallet Number P6] = “0”), “AVERAGE(LIST([Yellow color P1 %],[Yellow color P2 %],[Yellow color P3 %],[Yellow color P4 %],[Yellow color P5 %]))”, IF(([Pallet Number P5] = “0”), “AVERAGE(LIST([Yellow color P1 %],[Yellow color P2 %],[Yellow color P3 %],[Yellow color P4 %]))”, IF(([Pallet Number P4] = “0”), “AVERAGE(LIST([Yellow color P1 %],[Yellow color P2 %],[Yellow color P3 %]))”, IF(([Pallet Number P3] = “0”), “AVERAGE(LIST([Yellow color P1 %],[Yellow color P2 %]))”, IF(([Pallet Number P2] = “0”), “[Yellow color P1 %]”, “AVERAGE(LIST([Yellow color P1 %],[Yellow color P2 %],[Yellow color P3 %],[Yellow color P4 %],[Yellow color P5 %],[Yellow color P6 %]))”)))))

but based on the “test” function the syntax is ok, the problem arises when I trigger the sync of the app for sending a report and I get the following error:

Also this is a screenshot of the “test” for the app formula in one of the variables

It looks like the IF statement is working fine but after assigning the proper average formula it is not taking it into account for the calculation of the final value. In other words the “Meta-Formula” it is not working. Is there any easy way to circumvent this issue?

Thank you in advance!

Felipe_Engelber

I forgot to mention that all the variables included in the big formula are properly set as percentages

Steve

You have extraneous double-quote characters (") surrounding your AVERAGE() expressions. Consequently, the AVERAGE() expressions are being interpreted as literal Text values rather than as expressions. Remove the extraneous double-quotes.

Your expression reformatted for clarity with extraneous double-quotes removed:

IF(
 ([Pallet Number P6] = “0”),
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %],
   [Yellow color P4 %],
   [Yellow color P5 %]
  )
 ),
 IF(
  ([Pallet Number P5] = “0”),
  AVERAGE(
   LIST(
    [Yellow color P1 %],
    [Yellow color P2 %],
    [Yellow color P3 %],
    [Yellow color P4 %]
   )
  ),
  IF(
   ([Pallet Number P4] = “0”),
   AVERAGE(
    LIST(
     [Yellow color P1 %],
     [Yellow color P2 %],
     [Yellow color P3 %]
    )
   ),
   IF(
    ([Pallet Number P3] = “0”),
    AVERAGE(
     LIST(
      [Yellow color P1 %],
      [Yellow color P2 %]
     )
    ),
    IF(
     ([Pallet Number P2] = “0”),
     [Yellow color P1 %],
     AVERAGE(
      LIST(
       [Yellow color P1 %],
       [Yellow color P2 %],
       [Yellow color P3 %],
       [Yellow color P4 %],
       [Yellow color P5 %],
       [Yellow color P6 %]
      )
     )
    )
   )
  )
 )
)

Your expression with IFS() instead of nested "IF()*:

IFS(
 ([Pallet Number P6] = “0”),
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %],
   [Yellow color P4 %],
   [Yellow color P5 %]
  )
 ),
 ([Pallet Number P5] = “0”),
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %],
   [Yellow color P4 %]
  )
 ),
 ([Pallet Number P4] = “0”),
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %]
  )
 ),
 ([Pallet Number P3] = “0”),
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %]
  )
 ),
 ([Pallet Number P2] = “0”),
 [Yellow color P1 %],
 TRUE,
 AVERAGE(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %],
   [Yellow color P4 %],
   [Yellow color P5 %],
   [Yellow color P6 %]
  )
 )
)

An alternative (if I’ve interpreted your expression correctly):

AVERAGE(
 TOP(
  LIST(
   [Yellow color P1 %],
   [Yellow color P2 %],
   [Yellow color P3 %],
   [Yellow color P4 %],
   [Yellow color P5 %],
   [Yellow color P6 %]
  ),
  IFS(
   ([Pallet Number P6] = “0”), 5,
   ([Pallet Number P5] = “0”), 4,
   ([Pallet Number P4] = “0”), 3,
   ([Pallet Number P3] = “0”), 2,
   ([Pallet Number P2] = “0”), 1,
   TRUE, 6
  )
 )
)

Conditional calculation of an average of multiple percentages