In the previous tutorial about absolute cell reference https://www.2hac.com/google-sheets-tutorial-for-beginners-part-3 ,we talk about how to lock cell. We will talk more about how to lock columns, rows and ranges in this tutorial.
If you want to 10% sale off of total, you set formula =F4*G3 for G4
Click on little box in the bottom right corner of G4 and drag it down
Click on G5 cell and see the formula of G5 is F5*G4. You want to use G3 cell instead.
So we will change to F4*$G$3 for G4 cell and drag it down again.
The result looks good
Next step, we want to calculate 20%, 30% and 25%. One way to do that is we can go H4 cell and lock H3 cell by entering formula =F4*$H$3 . Similarly, you calculate the value of remaining saleoff of 20, 30, 40%
The better way to do that we will change the original formula a little bit. The original formula of G4 is F4$G$3. When you drag it down, we do not need to lock row number G because G3 will change to G5, G6. G still is the same. Therefore, we only need to lock 3 and the formula will be:
If i drag it down, this formula still works well. But if we drag it right
You see the problem. F4 is changed to G4. So we need to lock F
And then drag G4 right and see the formula of H4 is =$F4*H$3. F is still intact. It is perfect.
You drag G4 down to G6, G4 right to J4, J4 down J6, H4 to H6, I4 to I6, J4 to J6. You will fill table
We calculate total saleoff of 10% by entering =SUM(G4:G6) for G9
Click on little box at the right corner of G9 cell and drag it right to J9
We calculate total after saleoff for G10, enter formula =sum(F4:F6)-G9 for G10
Drag G10 right and see the formula of H10 is =sum(G4:G6)-H9. It is wrong because F move G so you need to lock range. So we will change the formula of G10 to =sum($F$4:$F$6)-G9
After that, you drag it right to fill the remaining cells
Finally, you want to change 10% title to Saleoff of 10% by enter text and press Enter
The below error occurs:
We should use custom format for G4 by clicking on Format/Number/More format/Custom number format
Enter text using quote