Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Margin Divisible by 5?

Hi,

I am looking to find a calculation that will tell me whether an item was sold at a margin divisible by five or not. i.e. ..., 35%, 40%, 45%, 50%, ... etc. I figured it would be simple using the MOD function. I tried each of the following

DivisibleBy5 = IF(MOD([Margin], 0.05) = 0, "Yes", "No")
DivisibleBy5 = IF(MOD([Margin] * 100, 5) = 0, "Yes", "No")

 Each give me an error saying "An argument of function 'MOD' has the wrong data type or the result is too large or too small". 

 

My [Margin] is a decimal number and classified as such. Any idea what's going wrong? Any other ways to get the result I'm looking for?

 

Thanks, 
Ryan

  • Anonymous's avatar
    Anonymous
    9 years ago

    MattAllington

     

    Sale Prices of $0.00 were creating -infinity values which was screwing it up. I stacked another IF statement on top of the other, saying IF([Sale]=0, IF(MOD([Margin], .05) = 0, "True", "False"), "False")

12 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi,

    I am looking to find a calculation that will tell me whether an item was sold at a margin divisible by five or not. i.e. ..., 35%, 40%, 45%, 50%, ... etc. I figured it would be simple using the MOD function. I tried each of the following

    DivisibleBy5 = IF(MOD([Margin], 0.05) = 0, "Yes", "No")
    DivisibleBy5 = IF(MOD([Margin] * 100, 5) = 0, "Yes", "No")

     Each give me an error saying "An argument of function 'MOD' has the wrong data type or the result is too large or too small". 

     

    My [Margin] is a decimal number and classified as such. Any idea what's going wrong? Any other ways to get the result I'm looking for?

     

    Thanks, 
    Ryan

    • alanhodgson's avatar
      alanhodgson
      Solution Supplier
      9 years ago

      Hey Anonymous,

       

      The MOD function can be expressed in terms of the INT function: MOD(n, d) = n - d*INT(n/d)

       

      Try this and see if the same error occurs:

       

      DivisibleBy5 = IF(([Margin] - (0.05)*INT([Margin]/0.05)) = 0, "Yes", "No")

      Hope this helps,

       

       

      Alan

    • Phil_Seamark's avatar
      Phil_Seamark
      Microsoft Employee
      9 years ago
      Try creating a calculated column that just uses the inner formula

      Eg. Column = mod ([margin] , 5)

      And what does that show?
      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        alanhodgson 

        That results in an error. "The result of a conversion or arethmatic operation is either too large or too small.

        Phil_Seamark 

        The calculated column  gives me "An argument of function 'MOD' has the wrong data type or the result is too large or too small" again. 

  • MattAllington's avatar
    MattAllington
    Community Champion
    9 years ago

    Is margin a measure or a calc column? Is DivisibleBy5 a measure or a calc column?

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      MattAllington 

       

      Both are calculated columns. I want it to calculate per row.

      • MattAllington's avatar
        MattAllington
        Community Champion
        9 years ago

        Well it looks right. Can you post a sample of the values in the margin column?

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    Please check the data type of the column [Margin]. If it is text, the formula will return #ERROR and you should change it to decimal number.

     

    Thanks,
    Yuliana Gu