how to do a function in excel to replicate the whole shopping bill

And we can change the percentage to calculate a different buffer amount. Now when you AutoFill the cell, all the formulas point to D20. The dollar signs tell Excel that the formula must always refer to the cell in column D in the 20th row. In the formula, click D20, and then type dollar signs in front of the D and the 20. To make that happen, we need to change the cell reference to an absolute reference. When you use AutoFill, Excel changes the cell reference based on location to row 7 and D21.īut in this case, we want the formulas to always use the value in this particular cell. In the first cell, the formula refers to these cells in row 6 and D20. And since D21 is empty, the formula multiplies by zero, and returns a value of zero.īut why did Excel change this cell reference to D21?īecause when you use AutoFill or copy a formula, Excel uses relative references by default. In the first cell, the formula gets its buffer percentage from D20.īut in the next cell, the formula refers to D21. Now we can use AutoFill to add the formulas to the other cells.

Now instead of multiplying, then adding, Excel will add the first two numbers, then multiply. To tell Excel to add first, we simply insert parentheses. And in our formula, we wanted Excel to add the first cells, then multiply. Just keep in mind that multiplication and division are calculated before addition and subtraction. You don't need to remember all the details. This is the order in which Excel calculates the parts of a formula. And the reason it's not is because I didn't consider calculation order. Well, it's pretty obvious that this amount is not 10 percent of the first two columns. The formula adds the Material and Labor cells, and then, multiplies that amount by the buffer percentage cell. When we are finished, we'll be able to change this number, if we want to calculate a different Cost buffer amount with our formula.

Then, come up here to the Number group and click the % button to format the cell as a percentage. The first thing we need is a cell to hold the percentage amount.

We could format the data as a table, but let's leave it as is for this demonstration. The formula we are going to write will go in this column and use the amounts in these two columns and a percentage amount.

Then, double-click the worksheet tabs and type names. I am going to show you how to write a basic formula to demonstrate a couple of important concepts.įirst, to help us keep everything straight, let's click this + sign to add a new worksheet.

How to do a function in excel to replicate the whole shopping bill