Flag Budget Overruns for Materials
I want a formula that displays 'Over Budget' if the actual material cost exceeds the estimated cost, otherwise 'Within Budget'.
=IF(C2>B2, "Over Budget", "Within Budget") How it works: This formula uses the IF function to perform a logical test. It checks if the 'Actual Cost' in cell C2 is greater than the 'Estimated Cost' in cell B2. If this condition (C2>B2) is TRUE, the formula returns "Over Budget". If the condition is FALSE (meaning C2 is less than or equal to B2), it returns "Within Budget". This provides an immediate visual indicator of budget performance for each material item.
Data Setup
| Material Item | Estimated Cost | Actual Cost | Budget Status |
|---|---|---|---|
| Concrete | 10000 | 10500 | Over Budget |
| Steel Beams | 15000 | 14800 | Within Budget |
| Lumber | 5000 | 5200 | Over Budget |
| Pipes | 3000 | 2900 | Within Budget |
| Electrical | 8000 | 8000 | Within Budget |
Step-by-Step Guide
Select the cell where you want the budget status to appear (e.g., D2).
Enter the formula: `=IF(C2>B2, "Over Budget", "Within Budget")`
Press Enter. The cell will now display 'Over Budget' or 'Within Budget' based on the values in C2 and B2.
Drag the fill handle (the small square at the bottom-right of the selected cell) down to apply the formula to the rest of your material items.
Explore More
Get Material Cost per Unit
I need to pull the unit cost for 'Concrete' and 'Lumber' from a vendor price list into my estimation sheet.
Count Safety Incidents by Site
I need to count the number of safety reports logged for 'Site A' to monitor compliance.
Calculate Total Hours by Subcontractor
I want to sum the total billable hours worked by the 'Plumbing' subcontractor across all project days.
Find Supplier Contact by Material
I need to find the phone number of the supplier who provides 'Drywall', searching through a messy vendor list.