Regarding a (specific) nested IF formula results

[EsgibtkeinBier]

Hey everyone!

I was wondering how the following formula produces results for dates > (2021,3,1), since I can't wrap my head around how it handles stuff after the aforementioned date! Thank you for your time!

I =

IF('Overview'[Date] < Date(2020,4,1),
  IF('Overview'[Buyer Type]="A",
    IF('Overview'[Auction Type]="O",
      Divide(Overview[r],76)*100*0.21,
      Divide(Overview[r],85)*100*0.11),
    Divide(Overview[r],73)*100*0.20),

IF('Overview'[Date] < Date(2020,6,1),
  IF('Overview'[Buyer Type]="A",
    IF('Overview'[Auction Type]="O",
      Divide(Overview[r],77)*100*0.20,
      Divide(Overview[r],87)*100*0.1),

    Divide(Overview[r],74)*100*0.20),

IF('Overview'[Date] < Date(2020,11,1),
  IF('Overview'[Buyer Type]="A",
    IF('Overview'[Auction Type]="O",
      Divide(Overview[r],73.5)*100*0.235,
      Divide(Overview[r],87)*100*0.1),
    Divide(Overview[r],70.5)*100*0.235),

IF(Overview[Date] < date(2021,3,1),
  IF(Overview[Publisher]="E",
      0,
      IF(Overview[A]="A",
        DIVIDE(Overview[r],87)*100*0.13,
        Divide(Overview[i],500)
      )
    )
  ,
  IF(Overview[Publisher]="E",
      0,
      IF(Overview[A]="A",
        DIVIDE(Overview[r],87)*100*0.13,
        If(Overview[A]="V",
          DIVIDE(Overview[i],250),
          Divide(Overview[i],500)
      )
    )
)))))

[EsgibtkeinBier · Accepted solution]

Thanks everyone for suggesting to use SWITCH, as it makes sense both in terms of performance and clarity! However, this does not answer the question of how dates > (2021,3,1) are handled.

With a careful look at the expression above, one can see that only the last logical test involving dates has an else statement, therefore, all dates after the 1st of March 2021, are handled by this part of the expression:

  IF(Overview[Publisher]="E",
      0,
      IF(Overview[A]="A",
        DIVIDE(Overview[r],87)*100*0.13,
        If(Overview[A]="V",
          DIVIDE(Overview[i],250),
          Divide(Overview[i],500)

[Anonymous]

Hi @EsgibtkeinBier 

As @daXtreme said, when you have multiple nests, it is better to use SWITCHE function instead of IF function . In terms of execution speed, SWITCHE is faster than IF . And SWITCHE is easier to edit and have a clear hierarchy. 

https://www.scaler.com/topics/c/difference-between-if-else-and-switch/ 

Best Regards,
Community Support Team _ Ailsa Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Hi @EsgibtkeinBier 

I wouldn't even try to understand it 🙂 I would rewrite it using SWITCH instead of IF's. If you start rewriting it, you'll sooner or later understand everything about it. That's the by-product of making code clearer with the correct structures.