Copy Value only and not the function


My second column has an IF function based on the first column that returns one of 4 possible values. I then need to preform another function (basic division) in a 3rd column based on the result in the second column. However, when I enter the function it returns a #INVALID OPERATION.

Is there a way to just extract the value from the second column sans function? Or do I need add some sort of helper column?

Thank you,


Best Answer


  • Jana Brits
    Jana Brits ✭✭✭✭

    Here is my test that works:

    First column: Programme Manager

    Second column: Point score

    Third column: Points devided by 2

    Second column formula: =IF([Programme Manager]@row = "Anton Kriel", 5, IF([Programme Manager]@row = "Riaan du Preez", 4, IF([Programme Manager]@row = "Dirk Theart", 2, 0)))

    Third column formula: =[Point score]@row / 2

    My results in my third column:

    For Anton Kriel = 2.5

    For Riaan du Preez = 2

    For Dirk Theart = 1

  • Bob Parrett
    Bob Parrett ✭✭✭


    Thank you for the quick response. This did not work for me. I need to divide a value in a column by the resulting value from the IF formula column. Basically I have:

    Column One: Buses Onsite

    Column Two: Occupied Parking Spaces

    Column Three: Calculation based on the number of buses =IF([BUSES ONSITE]@row = 0, "42", IF([BUSES ONSITE]@row = 1, "34", IF([BUSES ONSITE]@row = 2, "28", IF([BUSES ONSITE]@row = 3, "20"))))

    Column Four: Column Two/Column Three

    Still returns an #INVALID OPERATION

    It doesn't seem to like dividing by the result of a function. Easy in Excel but I am new to Smartsheet.

    Any suggestions?

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    edited 11/15/20

    Dear Bob,

    please check the column type and try to write 42 instead of "42" in your formula.

    Best Regards

    PMP Certified

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Bob Parrett
    Bob Parrett ✭✭✭

    Thank you both!!!! I removed the commas and it worked.