Forum Discussion

BarrbC's avatar
BarrbC
Frequent Visitor
7 years ago
Solved

multiply function IF OR AND

Hello,

 

what should be the proper formula if you have to calculate (A-B)B, if there a options that A and B are zeros, or only B is zero.

 

column = if(and('A=0,B=0), 0, (IF('B=0), 0, A/'B])

 

Would it be correct?

 

thanks in advance

 

Need Help
Tutorial Requests
  • themistoklis's avatar
    themistoklis
    7 years ago

    BarrbC

     

    Why dont you keep the formula as is (A-B)/B and if it gives you an error then convert the error to zero.

    Simpler formula i think.

     

    IFERROR( ([Sales]-[Target] / [Target]), 0)

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    SWITCH(TRUE(),[a]=0,0,[b]=0,0,[a]/[b])

     

    This will only divide A by B if both aren't equal to 0, did I understand you correctly?

    • BarrbC's avatar
      BarrbC
      Frequent Visitor
      7 years ago

      hi Tom,

       

      actually the A -is the sales, and B - is the target sales, so it can be that that there's no sales which means no target 

      • Fcoatis's avatar
        Fcoatis
        Post Patron
        7 years ago

        How about:

         

        =IF([Sales]<>0;DIVIDE([Sales]-[Target];[Target]))