0402 Cell Referencing ($) - 2 of 2

3 minutes
Share the link to this page
You need to purchase the class to view this lesson.
One-time Purchase
List Price:  $139.99
You save:  $40
List Price:  د.إ514.21
You save:  د.إ146.92
List Price:  A$189.27
You save:  A$54.08
List Price:  ৳11,874.45
You save:  ৳3,392.94
List Price:  CA$174.19
You save:  CA$49.77
CHF 90.63
List Price:  CHF 126.88
You save:  CHF 36.25
List Price:  kr876.09
You save:  kr250.33
List Price:  €117.79
You save:  €33.65
List Price:  £100.24
You save:  £28.64
List Price:  HK$1,087.91
You save:  HK$310.85
List Price:  ₹10,396.08
You save:  ₹2,970.52
List Price:  RM592.78
You save:  RM169.38
List Price:  ₦57,615.03
You save:  ₦16,462.61
List Price:  kr1,223.76
You save:  kr349.67
List Price:  NZ$199.75
You save:  NZ$57.07
List Price:  ₱7,023.18
You save:  ₱2,006.76
List Price:  ₨22,717.01
You save:  ₨6,491.03
List Price:  S$189.41
You save:  S$54.12
List Price:  ฿4,598.67
You save:  ฿1,314
List Price:  ₺1,183.81
You save:  ₺338.25
List Price:  B$707.83
You save:  B$202.25
List Price:  R2,037.17
You save:  R582.09
List Price:  Лв230.32
You save:  Лв65.81
List Price:  ₩160,005.91
You save:  ₩45,719.24
List Price:  ₪453.71
You save:  ₪129.64
Already have an account? Log In


Now, let me do one thing, let me ungroup this entire column which I had hidden. So this time I'm going back to the same question, but this time with a twist the twist is whatever formula we write in the cell which is blinking it should be copy paste double right and down and it should pick up the values based on 10% discount, 15% discount and 20% discount. Now, let me proceed with the logics equal to equal to what equal to the cell containing invoice. Now, ponder for a moment. How would you want this B six to behave? Do you want that as you copy paste the formula it should move down with it.

Of course, yes, I would want that in the next cell. It should point to be seven, then be eight and so on so forth, which means you want to change this part as it moves down. Will I put dollar before that? Nope, I will not. I will also put $1 before column B Why, because as I copy paste the formula on the right hand side, I don't want the column B to shift to column C, that would be picking up the wrong value to calculate the discount. So I'm going to press f4 once.

But this gives me the combination where both the columns and rows have been fixed. So I press f4, once again, but this is not the thing that I'm looking forward to. I am looking for Dolby six. So if you keep on pressing f4 f4 f4, you get to see different combination, and I'll stop where I get dollar B six, which means this reference of B will never ever shift to C or D or E, it will remain in column B, although six has the ability to move down to seven to eight to nine. Once I've proven that partially, let me put a multiplication sign and choose the cell containing 5%. Now notice, do you want this 5% to move down to the six fourth row, fourth row which is blank?

Of course not. So you would want to fix this third row but mine You If you copy paste this formula, do you want this 5% to shift to 10% for column D, and to column E that is 15%. Yes, of course. So I want a horizontal movement. And if I want that, I will be fixing only three, not column C, I want the column C to move horizontally from C to D, D to E and E to F. Let me press Enter. And once I've been done, so, let me copy paste the formula right?

Either I can drag it or better still, I can simply select the cell press shift right right right and press Ctrl r r for right shift CTRL down. Let me unselect one row that is shift up arrow key and Ctrl V for copy pasting down. Notice the answers are as I wanted 10% of 14,015% or 14,000 randomly checking one mode that is 15% of 5750. So All in all, when you choose a certain cell, there are four combinations one with no dollars, one with both dollars and there are two combinations one with dollar before the row and one with dollar before the column. The more you practice, the more we will have a good grip on it. So more practice exercises coming up.

Till then practice this one

Sign Up


Share with friends, get 20% off
Invite your friends to LearnDesk learning marketplace. For each purchase they make, you get 20% off (upto $10) on your next purchase.