Why are values acting like text and how do I rectify it?

Scott Cornish

My (Costing) formula was working yesterday until late in the afternoon then something happened. The formula is supposed to SUM the 'Cost' cells which have calculate the ‘material’ value (light green columns with fx) on a project. The individual cells should already be values, however these cells are acting like text.

The formula reads; =([PR1-Cost]@row + [PR2-Cost]@row + [SP-Cost]@row + [IN1-Cost]@row + [IN2-Cost]@row + [BN1-Cost]@row + [BN2-Cost]@row + [CO1-Cost]@row + [CO2-Cost]@row)

When I insert SUM at the beginning of the formula it results in $0.00

Jeff Reisman

@Scott Cornish

The answer is going to lie in the formulas you're using to get the individual values.

The way Smartsheet handles addition is that if there is any text value, even a blank space or a number represented as text, among the values you're trying to add, it will concatenate instead of add, and the result it treats like text as well. So check all your Cost formulas to see which is ending up as a text value.

If you're using IF statements or anything like that in calculating your various cost cells, check to make sure you're not setting a false condition as "", or " ". Either use 0, or just don't put a false condition at all (which Smartsheet treats as literally nothing, no value at all in the field.)

Jeff Reisman

@Scott Cornish

Perhaps this is the first time you've had an error result in the formula?

Is there a completely empty blank text/number cell on your sheet that you can reference? Maybe add a hidden helper column for this if needed? If so, we can get around errors resulting in a 0 value in the field. Replace the "" at the end with the cell reference for that completely empty cell:

=IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, HelperColumn1)

where HelperColumn1 is the completely empty blank text/number cell. This sets the cell in error to be nothing at all instead of an empty text cell, and allows Smartsheet to treat the addition formula as numbers and not text.

Jeff Reisman

@Scott Cornish

The answer is going to lie in the formulas you're using to get the individual values.

The way Smartsheet handles addition is that if there is any text value, even a blank space or a number represented as text, among the values you're trying to add, it will concatenate instead of add, and the result it treats like text as well. So check all your Cost formulas to see which is ending up as a text value.

If you're using IF statements or anything like that in calculating your various cost cells, check to make sure you're not setting a false condition as "", or " ". Either use 0, or just don't put a false condition at all (which Smartsheet treats as literally nothing, no value at all in the field.)

Scott Cornish

@Jeff Reisman

First, thank you for responding so quickly.... My Cost formulas are written; =IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, "")

If I understand you correctly, I should replace the false condition "" with 0 (zero). This would fill the column with $0.00 unless there is material (and quantity) entered to calculate, which I was trying to avoid so that I would have a quick visual that the fields were empty, like the rows above this one.

Your answer makes complete sense and definately does work. I do not recall any information like that when I was researching formula writting. What doesn't make sense is that I have had this sheet functioning for months now and this was somehow triggered yesterday. Additionally, I am using similar formulas across this sheet (and others) that have not been an issue. Any idea on how it may have been triggered?

Jeff Reisman

@Scott Cornish

Perhaps this is the first time you've had an error result in the formula?

Is there a completely empty blank text/number cell on your sheet that you can reference? Maybe add a hidden helper column for this if needed? If so, we can get around errors resulting in a 0 value in the field. Replace the "" at the end with the cell reference for that completely empty cell:

=IFERROR((INDEX({PC | Material Price}, MATCH([PRINT-1]@row, {PC | Material Data Entry}, 0))) * [PR1-Units]@row, HelperColumn1)

where HelperColumn1 is the completely empty blank text/number cell. This sets the cell in error to be nothing at all instead of an empty text cell, and allows Smartsheet to treat the addition formula as numbers and not text.

Scott Cornish

@Jeff R

That is a great idea. Thank you again...