When no actual cost is entered, want variance to show 0
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.
Best Answer

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.
Answers

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.

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

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

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

=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?

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

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

Glad we got it to work for you. Come back to the community any time.
Have a great day!
Kelly
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives