Forum Discussion

SkaistePapuskai's avatar
SkaistePapuskai
New Member
9 years ago

integer numbers for new column calculation

Hi,

I have a small issue: my dataframe contains two columns - OrderedQuantity and DeliveredQuantity which contains decimal and integer values. I need to make a new column like this:

=0 in those rows where OrderedQuantity and DeliveredQuantity has integer values

= OrderedQuantity-DeliveredQuantity where OrderedQuantity and DeliveredQuantity has decimal values.

 

I was thinking about using ROUND() function:

= if [OrderedQuantity]<>ROUND(OrderedQuantity,0) and ... then [OrderedQuantity]-[DeliveredQuantity]

But it is not working. Can someone suggest other solution?

 

Thank you a lot

 

3 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    9 years ago

    SkaistePapuskai Created calculated column as below,

     

    = IF( IFERROR(SEARCH(".",YOUR COLUMN),0) > 0, RESULT IF DECIMAL, RESULT IF INTEGER ) 

  • diego_salinas's avatar
    diego_salinas
    Advocate I
    9 years ago

    If you don't need the decimals at all, you can format the data type of your columns to "WHOLE NUMBER".

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

    Hi SkaistePapuskai,

     

    I tried the round function and it works, please try the formula when creating the column:

    Column = if(value(Sheet1[Sales])<>round(Sheet1[Sales],0)&&value(Sheet1[Sales1])<>round(Sheet1[Sales1],0),

                      Sheet1[Sales]-Sheet1[Sales1],

                       0)

    See the result:

    If any further help needed, please feel free to post back.

    Regards