Formula to Automatically Calculate Remaining Budget Based on Multiple Conditions

juliey3

Hello Smartsheet Community,

I'm trying to build a formula that automatically calculates the remaining budget for a project based on several conditions, and I'm running into issues with the logic.

Scenario:

• Each row contains:
  • Approved Budget
  • Actual Cost
  • Project Status (Not Started, In Progress, Completed)
  • Department
• I want the formula to:
  1. Return the difference between Approved Budget and Actual Cost when the Project Status is "In Progress" or "Completed".
  2. Return the full Approved Budget when the Project Status is "Not Started".
  3. Return a blank value if either Budget or Status is missing.
  4. Only perform the calculation for rows where Department = "Operations".

Example Data:

Approved Budget	Actual Cost	Status	Department
10000	6500	In Progress	Operations
8000	8000	Completed	Operations
5000	0	Not Started	Operations
12000	4000	In Progress	Finance

Expected Results:

• 3500
• 0
• 5000
• Blank

I've attempted using nested IF and AND statements, but I'm either getting incorrect values or formula errors.

What would be the best Smartsheet formula approach for this requirement?

Thanks in advance for any guidance!

TVang

Hi, @juliey3 , use the following formula:

=IF(Department@row = "Operations", IF(OR([Approved Budget]@row = "", Status@row = ""), "", IF(Status@row = "Not Started", [Approved Budget]@row, [Approved Budget]@row - [Actual Cost]@row)), "")

Here's an alternative view based on IF( expression, if true, if false )

IF(Department@row = "Operations"

	, IF(OR([Approved Budget]@row = "", Status@row = "")

		, ""

		, IF(Status@row = "Not Started"

			, [Approved Budget]@row

			, [Approved Budget]@row - [Actual Cost]@row

			)

		)

	, ""

	)

Start your IF() logical expressions with the most restrictive ("when department is 'Operations'")

Here's a diagram of the formula