You already know how to use simple formulas and functions to create and maintain your budget, as well as how formatting can show the overall trends in your data.
Next, you will learn more complex functions to address specific questions you may have about your budget.
In this video, you will use functions to find out how much money is being spent in a specific project category.
This will help you use the data in your spreadsheet to keep your project moving toward its specific goals.
Look at the budget you created.
A lot of red numbers is an indication that you may not have enough resources to complete your project as outlined.
But it’s difficult to know which project category is creating the problem.
To determine exactly where your project may have gone over budget, calculate the actual cost per category.
Add the heading “By Category” to the summary section of your spreadsheet.
Bold the heading.
List the categories you included in your spreadsheet.
Create a new column in E1, and label it Actual Cost Per Category.
Bold the heading, and format the column for currency.
To find the actual cost per category, use a function.
A function is a preset formula that helps you manipulate data in your spreadsheet.
Functions are a convenient way to save time on your calculations, make sense of your data, or draw conclusions from your spreadsheet.
Determine which function is best for your specific calculations by reviewing them in the Help menu.
A SUMIF function includes three parts, each separated by a comma.
The first is the cell range that contains the category names.
In this case, you listed the categories in Column B, beginning with row 10.
Type B10, colon, B to indicate this range.
Add a comma after the B. The range highlights in your spreadsheet.
Next, add the cell reference by selecting the cell that contains the category name.
Then, type a comma.
Finally, add the sum range, or the range of cells with the values to be added.
In this case, the values are in Column D, starting with D10.
Type D10 colon D, and close the parentheses.
Be sure there are commas between each argument in your function.
Then, press Enter.
The spreadsheet adds all of the expenses for the category you chose, and the total appears in this cell.
Double click on the function cell.
Be sure the function references the appropriate cells or range.
Next, copy the function to other cells.
Drag the cell handle to extend the function to the rest of the categories.
The numbers don’t add up correctly for each category.
Typical cell references--such as D2 or D10--are relative.
That means they change when a function is copied to another cell.
Because you changed the cell reference with the name of the category, the spreadsheet assumed that the other cells would also shift down.
To fix this, add an absolute cell reference to the formula.
Absolute references remain constant, even when you copy them to another cell or sheet.
Absolute references allow you to reference the same cell or range in multiple formulas.
To create an absolute cell reference, place dollar signs before the column letter and the row number.
This “locks” the cell reference, so it does not shift with the rest of the formula.
In this case, the cell range AND the sum range remain constant.
Add dollar signs to the formula in the cell range before the B and before the 10.
Then, add dollar signs to the sum range, before the D and before the 10.
Drag the cell handle to copy the formula to other categories.
The calculations are correct!
Now that you understand how to use complex functions in Google Sheets, you can apply this knowledge to other projects in the future.
Use the Help menu to search for the functions that make the most sense in your future calculations.
Now, it’s your turn: Create a SUMIF function to calculate the total expenditures in one category.
Add absolute references to lock cell references.
Copy the formula to the other categories.
Double-check your math.
1. Keeping Track of Project Finances
2. Set Up a Budget and Estimate Costs
3. Create a Category Menu
4. Compare Expenses to Cost Estimates
5. Use Functions to Calculate Total Costs and Contingency
6. Turn Negative Numbers Red with Conditional Formatting
7. Use Functions to Determine Total Budget by Category
8. Analyze Data to Adjust Project Goals and Purpose
9. Budgeting for Work and Personal Projects
10. Activity Reflection (Budget)
- Create a SUMIF function.
- Add absolute references to lock cell references.
- Copy the function to the other categories.
- Double-check your math.