When no actual cost is entered, want variance to show 0

Jessica Zahner

I am very new to smart sheets. Trying to figure out a formula that would show my variance as 0 or blank if there is no actual cost entered instead of showing a negative number (the budgeted cost number). To spare any confusion I won't even begin to type the formulas I have written.

Kelly Moore

Hey @Jessica Zahner

If I understand, if the Actual cost cell is blank, you want the value of your variance column cell to show 0?

If yes, try this

IF([Actual Cost]@cell<>"", <enter your regular formula without last paranthesis>, 0)

Change the column name to match yours. This says, if the Actual Cost is not blank, perform your formula, otherwise = zero.

If you wanted to highlight which variance cells were zero from real numbers and which were zero's based on no data, you could use conditional formatting looking for the blank cell in Actual cost and coloring the Variance cell when Actual cost was blank.

Kelly Moore

Hey @Jessica Zahner

If I understand, if the Actual cost cell is blank, you want the value of your variance column cell to show 0?

If yes, try this

IF([Actual Cost]@cell<>"", <enter your regular formula without last paranthesis>, 0)

Change the column name to match yours. This says, if the Actual Cost is not blank, perform your formula, otherwise = zero.

If you wanted to highlight which variance cells were zero from real numbers and which were zero's based on no data, you could use conditional formatting looking for the blank cell in Actual cost and coloring the Variance cell when Actual cost was blank.

Jessica Zahner

IT WORKED! I appreciate this more than you know. Thank you so much!

Jessica Zahner

Would you be able to help again!? That worked when there was no formula in the cell I was referencing, but now I'm trying to use same formula just referencing different cells that are not blank but have $0.00 because there is a function nested in it. What would I use?, I tried putting <0> or <$0.00>, neither worked.

=IF([Actual Cost]@row <> "", (SUM([Actual Scheduled Cost]@row - [Budgeted Scheduled Cost]@row)))

Kelly Moore

Hey @Jessica Zahner

I'm always happy to help

If you wanted the formula to execute when the cell was not zero, you would write it, for example, [Actual Cost]@row <> 0. See if the <>0 (which reads as Not Zero) works. Are your cells a mixture of blanks and zeros? If yes, I don't know if smartsheet counts the blank cell as zero or not. We'll see.

If the <>0 doesn't work, call out to me and we'll continue to tweak it until we get it right.

cheers,

Kelly

Jessica Zahner

=IF([Actual Cost]@row <>0 "", (SUM([Actual Scheduled Cost]@row - [Budgeted Scheduled Cost]@row)))

did not work :(

I'm working on 2 different sheets, so I would want a formula for both. 1 sheet working with the first formula you provided me because the cell is simply blank. Sheet 2, I am trying to reference a formula that currently has a $0.00 place holder because no dollar value produced by the formula.

Maybe there is something to convert my $0.00 to blank so the 1st formula you gave me would work?

Kelly Moore

Hey

Try this for your sheet 2 formula

=IF([Actual Cost]@row<>0, [Actual Scheduled Cost]@row - [Budgeted Scheduled Cost]@row)

One error in the formula you tried was too many parentheses. Smartsheet tries to help count parentheses by showing a blue parenthesis at the end of the formula when you have the correct number of them. This doesn't mean they are in the right place, only the correct number. You also had a stray "" by the <>0.

Let me know if this doesn't work either by giving you an error, or by not producing the correct value.

Kelly

Jessica Zahner

That worked!!! Next time I will need to look over what I paste before I send off for help ;)

Thank you so very much!

Kelly Moore

Glad we got it to work for you. Come back to the community any time.

Have a great day!

Kelly