#INVALID OPERATION

HI!

I have an equation in collumn5 and its creating a invalid operation error. The data in columns 3 & 4 are linked/referencing data in another sheet. What do i have to do to get this equation to work, pleae help!!

Best Answer

  • Kelly Moore
    Kelly Moore âś­âś­âś­âś­âś­âś­
    Answer âś“

    @jchambers

    Your IF statement is not inserting numeric values - it is inserting textstrings that appear like numbers to us. Double quotes around characters convert those characters to textstrings.

    Change your IF statement to this:

    =IF({Installation Progress Range 1}="yes", 1, 0)

    Then format those cells (entire column?) as Percent. You may need to do the same for your %Billed column. If your dollar columns are not formatted as currency, you need to format them into currency.

    Also, your formula in the Value Complete column will be more robust if you change your absolute row number to an @row designation since the values exist on the same row you're sitting on.

    =[Column3]@row * [Column2]@row

    Will this work for you?

    Kelly

Answers

  • Kelly Moore
    Kelly Moore âś­âś­âś­âś­âś­âś­

    Hey @jchambers

    Are the other rows populated by the same formula as showing in your Permits row, or did you manually enter their value? It appears those rows calculated correctly? Could you share the formulas that are being used in your column2 and column3? Also, what formula is being used in the Variance column?

  • jchambers
    jchambers âś­âś­

    Thanks @Kelly Moore

    No, the ones that add up correctly are not being pulled from another sheet like this one.

    Collumn 2 for the permits is just linked in from another sheet and below is the equation in column3 of permits.

  • Kelly Moore
    Kelly Moore âś­âś­âś­âś­âś­âś­
    Answer âś“

    @jchambers

    Your IF statement is not inserting numeric values - it is inserting textstrings that appear like numbers to us. Double quotes around characters convert those characters to textstrings.

    Change your IF statement to this:

    =IF({Installation Progress Range 1}="yes", 1, 0)

    Then format those cells (entire column?) as Percent. You may need to do the same for your %Billed column. If your dollar columns are not formatted as currency, you need to format them into currency.

    Also, your formula in the Value Complete column will be more robust if you change your absolute row number to an @row designation since the values exist on the same row you're sitting on.

    =[Column3]@row * [Column2]@row

    Will this work for you?

    Kelly

  • jchambers
    jchambers âś­âś­

    @Kelly Moore

    Works perfect, thank you so much!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!