0402 Cell Referencing ($) - 2 of 2

Advanced Excel Crash Course Section 4: Formula Pre-Requisite
3 minutes
Share the link to this page
Copied
  Completed
You need to have access to the item to view this lesson.
One-time Fee
$99.99
List Price:  $139.99
You save:  $40
€92.26
List Price:  €129.16
You save:  €36.90
£78.90
List Price:  £110.47
You save:  £31.56
CA$135.76
List Price:  CA$190.06
You save:  CA$54.30
A$153.66
List Price:  A$215.14
You save:  A$61.47
S$134.30
List Price:  S$188.03
You save:  S$53.72
HK$782.11
List Price:  HK$1,094.99
You save:  HK$312.87
CHF 88.79
List Price:  CHF 124.31
You save:  CHF 35.52
NOK kr1,070.17
List Price:  NOK kr1,498.28
You save:  NOK kr428.11
DKK kr688.01
List Price:  DKK kr963.24
You save:  DKK kr275.23
NZ$165.58
List Price:  NZ$231.83
You save:  NZ$66.24
د.إ367.20
List Price:  د.إ514.10
You save:  د.إ146.89
৳11,008.59
List Price:  ৳15,412.47
You save:  ৳4,403.87
₹8,303.29
List Price:  ₹11,624.95
You save:  ₹3,321.65
RM473.19
List Price:  RM662.48
You save:  RM189.29
₦156,506.34
List Price:  ₦219,115.14
You save:  ₦62,608.80
₨28,049.28
List Price:  ₨39,270.12
You save:  ₨11,220.83
฿3,608.38
List Price:  ฿5,051.88
You save:  ฿1,443.50
₺3,233.78
List Price:  ₺4,527.43
You save:  ₺1,293.64
B$503.03
List Price:  B$704.27
You save:  B$201.23
R1,898.90
List Price:  R2,658.54
You save:  R759.63
Лв180.15
List Price:  Лв252.22
You save:  Лв72.06
₩133,999.91
List Price:  ₩187,605.24
You save:  ₩53,605.32
₪366.35
List Price:  ₪512.91
You save:  ₪146.55
₱5,593.09
List Price:  ₱7,830.55
You save:  ₱2,237.46
¥15,066.66
List Price:  ¥21,093.92
You save:  ¥6,027.26
MX$1,690.23
List Price:  MX$2,366.39
You save:  MX$676.16
QR365.81
List Price:  QR512.15
You save:  QR146.33
P1,369.30
List Price:  P1,917.08
You save:  P547.77
KSh13,273.67
List Price:  KSh18,583.67
You save:  KSh5,310
E£4,710.13
List Price:  E£6,594.37
You save:  E£1,884.24
ብር5,696.11
List Price:  ብር7,974.78
You save:  ብር2,278.67
Kz83,441.65
List Price:  Kz116,821.65
You save:  Kz33,380
CLP$94,734.02
List Price:  CLP$132,631.42
You save:  CLP$37,897.40
CN¥719.86
List Price:  CN¥1,007.84
You save:  CN¥287.97
RD$5,933.04
List Price:  RD$8,306.49
You save:  RD$2,373.45
DA13,480.42
List Price:  DA18,873.13
You save:  DA5,392.71
FJ$226.79
List Price:  FJ$317.51
You save:  FJ$90.72
Q783.08
List Price:  Q1,096.35
You save:  Q313.26
GY$20,985.05
List Price:  GY$29,379.92
You save:  GY$8,394.86
ISK kr13,738.62
List Price:  ISK kr19,234.62
You save:  ISK kr5,496
DH1,007.92
List Price:  DH1,411.13
You save:  DH403.21
L1,768.33
List Price:  L2,475.73
You save:  L707.40
ден5,685.15
List Price:  ден7,959.43
You save:  ден2,274.28
MOP$807.89
List Price:  MOP$1,131.08
You save:  MOP$323.19
N$1,891.51
List Price:  N$2,648.20
You save:  N$756.68
C$3,691.90
List Price:  C$5,168.80
You save:  C$1,476.90
रु13,305.26
List Price:  रु18,627.89
You save:  रु5,322.63
S/369.78
List Price:  S/517.70
You save:  S/147.92
K378.11
List Price:  K529.37
You save:  K151.26
SAR374.99
List Price:  SAR525
You save:  SAR150.01
ZK2,562.18
List Price:  ZK3,587.15
You save:  ZK1,024.97
L458.93
List Price:  L642.52
You save:  L183.59
Kč2,328.03
List Price:  Kč3,259.33
You save:  Kč931.30
Ft36,496.67
List Price:  Ft51,096.80
You save:  Ft14,600.13
SEK kr1,049.30
List Price:  SEK kr1,469.07
You save:  SEK kr419.76
ARS$85,200.41
List Price:  ARS$119,283.99
You save:  ARS$34,083.57
Bs693.12
List Price:  Bs970.40
You save:  Bs277.27
COP$390,541.29
List Price:  COP$546,773.43
You save:  COP$156,232.14
₡51,032.06
List Price:  ₡71,446.93
You save:  ₡20,414.86
L2,476.89
List Price:  L3,467.75
You save:  L990.85
₲731,475.55
List Price:  ₲1,024,095.03
You save:  ₲292,619.48
$U3,846.42
List Price:  $U5,385.15
You save:  $U1,538.72
zł398.58
List Price:  zł558.03
You save:  zł159.45
Already have an account? Log In

Transcript

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

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.