Managing a project that involves multiple tasks

Ask the Community Formulas
#1

I am managing a project that involves multiple tasks, each with a budget and actual expenditure. I need to track the financial status of each task in relation to its budget and completion status. My goal is to categorize each task based on whether it is over or under budget and whether it is completed or still in progress.

#2

To create a formula that categorizes each task based on whether it is over or under budget and whether it is completed or still in progress, you can use the following formula

IF(
  {Status} = "Completed",
  IF(
    {Spent} > {Budget},
    "overbudget and completed",
    "within budget and completed"
  ),
  IF(
    {Spent} > {Budget},
    "overbudget and in progress",
    "within budget and in progress"
  )
)
  • {Status} = "Completed": Checks if the task status is “Completed”.
  • {Spent} > {Budget}: Compares the actual expenditure (Spent) with the allocated budget (Budget).
  • IF(..., ..., ...): Nested IF statements are used to handle the different conditions:
  • If the task is completed:
    • Checks if Spent is greater than Budget and returns “overbudget and completed” if true, otherwise “within budget and completed”.
  • If the task is not completed:
    • Checks if Spent is greater than Budget and returns “overbudget and in progress” if true, otherwise “within budget and in progress”.