Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

SmartSheet Function Similar to Excel MOD Function?

Carol Polack
edited 12/09/19 in Archived 2015 Posts

Is there a SmartSheet function similar to Excel's MOD function. I need to create an IF statement based on if a quotient is a whole number/integer or has a decimal (has a remainder after division).



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭





    Do you only care about the binary result (either is a whole number or not) or do you need the actual result of the MOD (modulus) function?



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Assuming the answer is binary, then try this:


    =IF([Value1]23 / [Value2]23 > INT([Value1]23 / [Value2]23), "mod", "whole")


    for two columns of data in row 23.


    If  VALUE1/VALUE2 is greae than the integer portion of VALUE1/VALUE2, then there is a remainder ("mod" result). If not, there isn't ("whole" result).


    Hope this help.




  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    Sorry, just occurred to me that if either VALUE1 or VALUE2 are negative, this won't work. Let me know if that data input is valid and I'll make the formula more Craig-proof.



  • Thanks J. Craig. I actucally came up with a similair solution yesterday that worked.


    I needed a formula to find the majority. A majority is more than one. Example: 35/2=17.5 and a majority is 18. 34/2=17 and a majority is also 18. So, if it's a decimal it needs to round up. If it's whole it needs to add 1.


    =IF(AND([VALUE]54 / 2 > INT([VALUE]54 / 2), [VALUE]54 / 2 < INT([VALUE]54 / 2) + 1), INT([VALUE]54 / 2) + 1, [VALUE]54 / 2 + 1)


    If the quotient (VALUE/2) is greater than the interger of the quotient; AND less than interger of the quotient plus 1; then return the interger of the quotient plus 1; else return the quotent plus 1.



  • J. Craig Williams
    J. Craig Williams ✭✭✭✭✭✭

    So you are always dividing by 2?

    And looking for the majority?


    How about




    for row 23.


    For both 35 and 34, the integer value is 17. +1 is 18.




This discussion has been closed.