#INVALID OPERATION

jchambers

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!!

Kelly Moore

@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

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

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

@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

@Kelly Moore

Works perfect, thank you so much!!