Get documents about "YouTubersLoveExcel97 100Finished
Document Sample
If you are entering a formula into a cell by clicking into another cell,
you can ruin your formula because by clicking in another cell you may
be entering a cell reference into your formula inadvertently (when
1) you are in "Point mode")
3) If you are entering a formula into a cell
1) If you are entering a formula into a cell and and you want your cursor to go to the right,
you want your cursor to go down, hit Enter hit Tab
1 1 1 3 Sales Total Average
Tax Rate 0.088 0.26 738 2730 682.5
730
2) If you are entering a formula into a cell and
you want your cursor to go up, hit Shift + Enter 667
Sales Total Sales 595
Jan Feb 4937
4) If you are entering a formula into a cell
and you want your cursor to go to the Left,
200 104 hit Shift + Tab
15 166 Sales Total Average
30 183 531 2292 573
68 197 608
188 110 572
42 39 581
118 36
5) If you are entering a formula into a cell
and you want your cursor to remain in the
23 59 active cell, hit Ctrl + Enter
66 104 Jan Feb Mar Total
139 195 46.61 6.57 10.41 63.59
171 60 87.38 7.11 37.84 132.33
167 122 64.25 81.17 57.76 203.18
49 82 48.33 79.59 93.15 221.07
173 21 38.61 84.58 84.31 207.5
27 86 21.89 27.04 65.99 114.92
145 78
29 140
73 137
87 195
115 166
101 127
166 63
196 79
The ADDRESS function will build a cell address as text. This can be used in a
1) concatenated label.
The Ampersand, &, is the join symbol and can be used to combine multiple "text"
2) items, functions or cell references into one text string in one cell
The ROW and COLUMN functions tell you what Row or Column is represented by
3) a cell reference
A formula like ="Click in cell "&ADDRESS(ROW(D8),COLUMN(D8),4)&" Enter Your
Name" or ="Enter the values in the range
"&ADDRESS(ROW(E11),COLUMN(E11),4)&":"&ADDRESS(ROW(E17),COLUMN(E17
4) ),4) will work
Please Enter Name in cell D9 Click in cell D9 Enter Your
Enter Name
Please enter values in
the range F13:F19 Values Enter the values in the ran
Click in cell D9 Enter Your Name
Enter the values in the range F13:F19
1) Array Formula for the number e
2) The formula =EXP(1) will also give you e
2.718281828 mu = µ =
2.718281828 sd
X1 =
z score =
(µ - X1)/s
=round((B
3-
N 1/n! =SUM(1/n!) B1)/B2,2)
0 1 2.718281828
1 1
2 0.5
1
3 0.166667 P( x )
4 0.041667 2
5 0.008333
6 0.001389
7 0.000198
8 2.48E-05
9 2.76E-06
10 2.76E-07
11 2.51E-08
12 2.09E-09
13 1.61E-10
14 1.15E-11
15 7.65E-13
16 4.78E-14
17 2.81E-15
18 1.56E-16
19 8.22E-18
20 4.11E-19
21 1.96E-20
22 8.9E-22
23 3.87E-23
24 1.61E-24
25 6.45E-26
26 2.48E-27
27 9.18E-29
28 3.28E-30
29 1.13E-31
30 3.77E-33
31 1.22E-34
32 3.8E-36
33 1.15E-37
34 3.39E-39
35 9.68E-41
36 2.69E-42
37 7.27E-44
38 1.91E-45
39 4.9E-47
40 1.23E-48
41 2.99E-50
42 7.12E-52
43 1.66E-53
44 3.76E-55
45 8.36E-57
46 1.82E-58
47 3.87E-60
48 8.06E-62
49 1.64E-63
50 3.29E-65
51 6.45E-67
52 1.24E-68
53 2.34E-70
54 4.33E-72
55 7.88E-74
56 1.41E-75
57 2.47E-77
58 4.25E-79
59 7.21E-81
60 1.2E-82
61 1.97E-84
62 3.18E-86
63 5.04E-88
64 7.88E-90
65 1.21E-91
66 1.84E-93
67 2.74E-95
68 4.03E-97
69 5.8E-99
70 8.3E-101
71 1.2E-102
72 1.6E-104
73 2.2E-106
74 3E-108
75 4E-110
76 5.3E-112
77 6.9E-114
78 8.8E-116
79 1.1E-117
80 1.4E-119
81 1.7E-121
82 2.1E-123
83 2.5E-125
84 3E-127
85 3.5E-129
86 4.1E-131
87 4.7E-133
88 5.4E-135
89 6.1E-137
90 6.7E-139
91 7.4E-141
92 8E-143
93 8.6E-145
94 9.2E-147
95 9.7E-149
96 1E-150
97 1E-152
98 1.1E-154
99 1.1E-156
100 1.1E-158
101 1.1E-160
102 1E-162
103 1E-164
104 9.7E-167
105 9.2E-169
106 8.7E-171
107 8.2E-173
108 7.5E-175
109 6.9E-177
110 6.3E-179
111 5.7E-181
112 5.1E-183
113 4.5E-185
114 3.9E-187
115 3.4E-189
116 2.9E-191
117 2.5E-193
118 2.1E-195
119 1.8E-197
120 1.5E-199
121 1.2E-201
122 1E-203
123 8.2E-206
124 6.6E-208
125 5.3E-210
126 4.2E-212
127 3.3E-214
128 2.6E-216
129 2E-218
130 1.5E-220
131 1.2E-222
132 8.9E-225
133 6.7E-227
134 5E-229
135 3.7E-231
136 2.7E-233
137 2E-235
138 1.4E-237
139 1E-239
140 7.4E-242
141 5.3E-244
142 3.7E-246
143 2.6E-248
144 1.8E-250
145 1.2E-252
146 8.5E-255
147 5.8E-257
148 3.9E-259
149 2.6E-261
150 1.8E-263
151 1.2E-265
152 7.6E-268
153 5E-270
154 3.2E-272
155 2.1E-274
156 1.3E-276
157 8.5E-279
158 5.4E-281
159 3.4E-283
160 2.1E-285
161 1.3E-287
162 8.1E-290
163 5E-292
164 3E-294
165 1.8E-296
166 1.1E-298
167 6.7E-301
168 4E-303
169 2.3E-305
$3,000.00
$300.00
$3,500.00
1.666666667
0.000332 0.000332
( x )2
1
P( x ) e 2 2
2
The goal is to find all customers from a transactional database that has
multiple entries for each customer who have not made a purchase in the
1) last 6 months
The formula =TODAY()-EDATE(TODAY(),-6) is an exact method to find 6
months in the past starting from today's date. If you want to learn more
2) about the EDATE function watch this video: Excel Magic Trick #88: EDATE function Invoicin
The formula =IF(TODAY()-
E$18>MAX((D22=$D$22:$D$45)*$C$22:$C$45),D22,"Current") is an
array formula that must be entered with the keyboard shortcut Ctrl +
Shift + Enter. If you want to learn more about array formulas, watch the
3) Array Formula Series videos at this link ==> Playlist: 08 Excel Series: Array Formulas 1-14
This part of the formula, MAX((D22=$D$22:$D$45)*$C$22:$C$45), finds
4) the most current date amongst all the dates for that one customer
Months In Past
6
Days into Past
184
Has Customer odered in
Order Date Customer the last 6 months? YTLE95.2
4/26/2007 Cust01 Cust01 1/1/2008
8/7/2007 Cust02 Cust02
2/21/2008 Cust03 Cust03
7/24/2008 Cust04 Cust04
4/11/2008 Cust05 Cust05
6/2/2007 Cust06 Cust06
5/6/2008 Cust07 Cust07
2/24/2008 Cust08 Cust08
1/1/2008 Cust01 Cust01
2/15/2007 Cust02 Cust02
12/17/2007 Cust03 Cust03
9/2/2007 Cust04 Cust04
6/20/2008 Cust05 Cust05
10/31/2007 Cust06 Cust06
1/23/2007 Cust07 Cust07
6/25/2007 Cust08 Cust08
4/25/2007 Cust01 Cust01
4/27/2007 Cust02 Cust02
1/1/2008 Cust03 Cust03
6/10/2008 Cust04 Cust04
2/15/2008 Cust05 Cust05
7/18/2007 Cust06 Cust06
7/26/2008 Cust07 Cust07
9/29/2007 Cust08 Cust08
EDATE function Invoicing & Loans
s: Array Formulas 1-14
