Counting amounts in a spreadsheet is one of the main reasons to use a spreadsheet program like Excel or a spreadsheet website like Google Sheets. This is useful when working with large datasets, automating expense tracking, and more.
A formula that automatically adds cells is much easier than pulling out a calculator to calculate by hand. The same is true for other mathematical calculations. All you need are values ??to work with, and the formulas below will do all the hard work for you.
Most spreadsheet programs work the same way when it comes to addition, subtraction, multiplication, and division, so these steps should work no matter which spreadsheet tool you use.
Automatic addition and subtraction in a spreadsheet h1>
Consider a simple spreadsheet that stores expenses, deposits, and current balance. You start with a balance sheet, which shows how much money you have, and you need to deduct expenses and add deposits from it to stay in the know. Formulas are used to easily calculate balance.
Here’s a simple example of how to subtract large expenses from your balance sheet:
We want the current balance to be below the current $ 10,000. To do this, we selected the cell in which the calculations should be displayed, and then put an = sign, followed by the calculation.
The = sign is always required to start any formula in a spreadsheet. The rest is pretty simple: take the current balance (C2) minus expenses (A3), as if you were subtracting these values ??on paper. When you press Enter at the end of the formula, the value is automatically calculated at $ 9,484.20.
Likewise, if we wanted to add a deposit to the balance, we would select the cell in which the data should be displayed, put an = sign in it, and then continue with simple math for what we need to add: C3 + B4.
So far, we’ve shown you how to perform simple addition and subtraction on a spreadsheet, but there are some complex formulas we can use to calculate these results right after you enter the expense or deposit. Using them will allow you to enter numbers in these columns so that the final balance is displayed automatically.
To do this, we need to create if / then formulas. This can be a little confusing if you’re looking at a long formula for the first time, but we’ll break it down into small parts to get a feel for what they all mean.
= ifs (A5> 0, C4-A5, B5> 0, C4 + B5, TRUE, â€â€)
The â€œifâ€ part just says that we want to match more than one â€œifâ€ because we don’t know if the expense or deposit will be paid. We want one formula to work if an invoice was filled (this would be a subtraction as shown above) and another (add) if a deposit was entered.
- A5> 0: This is the first if statement that says if A5 is greater than 0 (that is, if there is a value at all), then do the following
- C4-A5: This is what happens if there is a value in A5; we will take the balance minus the value in A5.
- B5> 0: This is another â€œifâ€ operator that asks if the deposit field is filled in.
- C4 + B5: If there is a deposit, add it to the balance to calculate the new balance.
- TRUE, â€â€: This is a placeholder that marks a cell with nothing if there is nothing to calculate. If you omit it, then every cell that uses a formula, but no data to calculate, will display # N / A, which doesn’t look good.
Now that we have a formula that will automatically calculate these amounts, we can drag the formula down the table to prepare for any entries we make in the expense or deposit column.
As soon as you enter these values, the balance column will calculate the amounts immediately.
Spreadsheet programs can work with more than two cells at the same time, so if you need to add or subtract multiple cells at the same time, there are several ways to do it:
- = ADD (B2, B30)
- = MINUS (F18, F19)
- = C2 + C3 + C4 + C5
- = A16-B15-A20
How to divide, multiply, and more
Dividing and multiplying is as easy as adding and subtracting. Use * for multiplication and / for division. However, what can get a little confusing is when you need to combine all these different calculations into one cell.
For example, when division and addition are used together, it could be formatted as = sum (B8: B9) / 60. It takes the sum of B8 and B9, and then takes this answer divided by 60. Since we you need to do the addition first, we first write it into the formula.
Here’s another example where all the multiplication is nested in separate sections, so they run together, and then these individual answers are added together: = (J5 * 31) + (J6 * 30) + (J7 * 50).
In this example = 40- (sum (J3: P3)) we determine how many hours are left out of 40 when calculating the sum from J3 to P3. Since we are subtracting the amount from 40, we first set 40 like a common math problem and then subtract the total from it.
When nesting calculations, remember the order of operations so that you know how things will be calculated:
- The calculations in parentheses are performed first.
- Exponents follow.
- Then multiplication and division.
- Addition and subtraction is the last.
Here’s an example of correct and incorrect use of order of operations in a simple math problem:
30 times 5 times 3
The correct way to calculate this is to take 30/5 (which is 6) and multiply by 3 (to get 18). If you fail and first take 5 * 3 (to get 15) and then take 30/15, you will get the wrong answer 2.
Comment on “How To Format Spreadsheet Cells To Automatically Calculate Amounts”