Documents
Resources
Learning Center
Upload
Plans & pricing Sign in
Sign Out

DataSet

VIEWS: 29 PAGES: 22

									Worksheet                              Purpose

  ChartData            Students will use the data to create charts

   DataSet         Demonstrate various database functions in Excel

DeliveryForm         Used for IF & VLOOKUP Functions homework

FunctionsHelp               Examples of common Functions

   Tables                    Used for the VLOOKUP lesson

  TextData      Grouping and separating text, paste special, concatenate
Business Productivity Tools                                 Java Juice Sales Forecast
Prepared by: Your Name
                                   January     February     March      April        May
          Food Sales
          Sandwiches                 $42,000     $42,500     $43,000   $43,500      $44,000
          Soup/Salads                 19,000      20,710      22,574    24,606       26,820
           Desserts                   22,000      23,100      24,255    25,468       26,741
        Total Food Sales             $83,000     $86,310     $89,829   $93,573      $97,561

         Beverage Sales
             Coffee                  $34,000     $35,020     $36,071   $37,153      $38,267
               Tea                    15,000      15,000      15,000    15,000       15,000
           Beer/Wine                  12,000      11,640      11,291    10,952       10,624
             Juice                     5,000       5,750       6,613     7,604        8,745
           Soft Drinks                 8,000       8,080       8,160     8,240        8,320
      Total Beverage Sales           $74,000     $75,490     $77,134   $78,949      $80,956

        Ingredient Costs
           Food Costs                $24,900     $25,893     $26,949   $28,072      $29,268
         Beverage Costs              $11,100     $11,324     $11,570   $11,842      $12,143

          Net Revenue               $121,000    $124,584    $128,444 $132,608 $137,105

           Ratios
   Food Cost as a % of Sales            30%
 Beverage Cost as a % of Sales          15%



Date needed for charts
 All charts use data from the cells in columns A - G in each of the selected rows
  Inclass Chart                    Rows 3, 5, 6, 7
  HW Chart 1 Column Chart          Rows 3, 14
  HW Chart 2 Line Graph            Rows 3, 8, 16, 22
  HW Chart 3 Pie Chart             Rows 3, 19
ales Forecast
         June       Total         %

         $44,500    $259,500      47.0%
          29,234     142,943      25.9%
          28,078     149,642      27.1%
        $101,812    $552,085       100%


         $39,415   $219,926       46.8%
          15,000     90,000       19.2%
          10,305     66,811       14.2%
          10,057     43,769        9.3%
           8,400     49,200       10.5%
         $83,177   $469,706        100%


         $30,544   $165,626
         $12,477    $70,456

        $141,969   $785,710




                               Sample


       Selecting data
          Mouse over the first row of labels (A3:G3)
          Hold down CTRL and keep holding it
          Mouse over additional rows of data and labels
          Release CTRL
                            Demographics                          Monthly Purchases In Dollars
ID #    Last Name   First Name       Major     Class     M/F Gasoline Phone Music Clothes Food
111    Arredondo    Jonathan   Accounting    Sophomore   M     68      37    95       163   376
126    Bravo        Wanling    Accounting    Sophomore   M     61      21    94        27   390
127    Brown        Yesenia    Accounting    Junior      F     67      28    76       143   421
136    Chavez       Xiumei     Accounting    Senior      F     65      49    85       109   471
141    Chin         Zacil      Accounting    Sophomore   M     89      0     18       184   452
143    Chu          Joelson    Accounting    Junior      M     48      23    74       170   324
179    Elias        Luke       Accounting    Sophomore   M     10      31    59         0   403
188    Figone       Oscar      Accounting    Freshman    M     13      99    48        60   467
189    Fine         Adina      Accounting    Freshman    F     98      0     83       165   477
190    Flint        Paige      Accounting    Freshman    F     13      0     43        67   375
193    Forks        Leroy      Accounting    Junior      M     88      46    87       163   331
201    Gragg        Marian     Accounting    Junior      F     40      35    61        17   288
203    Gutierrez    Li         Accounting    Junior      F     91      80    75        37   287
218    Ho           Ainguyen   Accounting    Senior      M     96      0      0       128   400
222    Hu           Lesly      Accounting    Sophomore   F     88      10    80       162   332
255    Le           Zaneta     Accounting    Sophomore   M     63      47     0        64   390
267    Livingston   Ashley     Accounting    Senior      F     38      73    52       163   376
268    Lopez        Ray        Accounting    Freshman    M     56      89    65       136   461
271    Luong        Shanti     Accounting    Senior      F     45      93    14       107   350
275    Macias       Kris       Accounting    Freshman    F     21      99    18        20   453
277    Maddox       Adam       Accounting    Senior      M     61      19    75        23   466
284    McQueen      Brian      Accounting    Junior      M     85      12    88       106   272
288    Mir          David      Accounting    Sophomore   M     60      36    22         0   323
301    Nguyen       Keith      Accounting    Senior      M     57      0     63       143   355
304    Nguyen       Phillip    Accounting    Sophomore   M     38      0     13       177   423
314    Pala         Julia      Accounting    Junior      F     50      84    94         0   444
322    Phan         Romel      Accounting    Junior      M     98      35    62       177   293
331    Resendez     Varinder   Accounting    Junior      M     50      67    87         0   384
342    Safi         Juan       Accounting    Junior      M     36      35    49       136   271
354    Smith        Jason      Accounting    Sophomore   M     55      58     0       108   413
359    Tan          Sei        Accounting    Sophomore   F     26      97    63       135   353
360    Tate         Carina     Accounting    Junior      F     15      12    69       107   267
361    Ton          Kevin      Accounting    Junior      M     90      70    43        15   321
363    Tran         John       Accounting    Junior      M     66      91    60       123   416
116   Basati         Shaun        Accounting Information   Senior      M   48   58   10   168   406
139   Cheung         Jessica      Accounting Information   Junior      F   42   17   44    57   392
151   Craig          Olga         Accounting Information   Sophomore   F   89   30   35   187   335
174   Duong          Eric         Accounting Information   Sophomore   M   70   11   59     0   407
199   Golkowski      Eric         Accounting Information   Junior      M   67   24   61   153   475
206   Haas           Gustavo      Accounting Information   Freshman    M   16   21   38   127   374
229   Jiang          Genesis      Accounting Information   Sophomore   M   91   92   99    25   453
231   Jones          Glen         Accounting Information   Sophomore   M   91   98   12    37   415
250   La             Aabedah      Accounting Information   Junior      F   73   12    0    67   257
321   Pham           Ronil        Accounting Information   Junior      F   67   31    0   148   354
333   Robinson       Huong        Accounting Information   Senior      F   87   72   25   140   275
345   Schenkhuizen   Debra        Accounting Information   Sophomore   F   64   88   67    95   421
352   Singh          Carolyn      Accounting Information   Junior      F   42   34   40    54   446
163   Dicicco        Andrew       Corporate Finance        Senior      M   44   82   52    83   276
198   Gaw            Julieta      Corporate Finance        Junior      F   63   88   79    75   436
219   Holl           Aline        Corporate Finance        Freshman    F   43   0    57    60   468
243   Khuzaie        Justin       Corporate Finance        Junior      M   52   11   88    44   424
248   Kwong          Annchristy   Corporate Finance        Freshman    F   76   0     5   131   279
294   Munzelong      Patrick      Corporate Finance        Sophomore   M   86   55   59   123   278
307   Nguyen         Hao          Corporate Finance        Freshman    F   66   94    0   128   400
335   Rodarte        John         Corporate Finance        Senior      M   61   95   64    30   258
371   Tu             Jason        Corporate Finance        Sophomore   M   49   88    0   195   418
376   Vaughan        Rohit        Corporate Finance        Sophomore   M   96   60   84   133   280
103   Alvarez        Wendy        Finance                  Sophomore   F   63   81   48    60   384
106   Andaya         Vinh         Finance                  Freshman    F   54   98    4    97   422
121   Beltran        Angela       Finance                  Junior      F   55   56   52   105   337
132   Cao            Jessica      Finance                  Junior      F   42   0    92    45   470
155   Cunningham     Thomas       Finance                  Senior      M   99   83   71   185   387
159   Defriese       Annette      Finance                  Senior      F   83   57   76    67   373
176   Durso          Omar         Finance                  Freshman    M   80   24    0    13   437
182   Fan            Rahul        Finance                  Junior      M   46   62   27   137   453
183   Farley         Aaron        Finance                  Sophomore   M   56   62   44   137   284
184   Ferla          Cardenas     Finance                  Sophomore   M   11   65   61    25   288
194   Gamez          Soanne       Finance                  Senior      F   88   46   48   162   316
212   Henderson      Bich         Finance                  Freshman    F   56   17    0   137   389
216   Ho             Arman        Finance                  Sophomore   M   48   25    0   174   253
225   Huynh          Aron         Finance                  Junior      M   33   40    0    69   301
226   Jacobo      Dan          Finance            Sophomore   M   55   70   17   114   333
228   Jandaur     Steven       Finance            Junior      M   24   79   91    95   456
254   Lau         Joseph       Finance            Junior      M   24   24   35    93   487
259   Lehnhardt   Samantha     Finance            Sophomore   F   63   55    0    62   253
263   Liang       Julia        Finance            Freshman    F   43   66   28    67   308
266   Liu         Kayte        Finance            Freshman    F   46   70   21   135   283
270   Lopez       Daniel       Finance            Freshman    F   42   85   78    49   283
269   Lopez       Ana          Finance            Sophomore   F   53   80    3    77   287
281   Marshall    Hector       Finance            Senior      M   51   88   71    35   441
289   Mirzaei     Cheryl       Finance            Sophomore   F   75   37   26   102   390
292   Morris      Amirissa     Finance            Freshman    F   35   50    0   110   256
293   Munoz       Derek        Finance            Senior      M   64   57   80    86   393
295   Navab       Eric         Finance            Freshman    M   71   62   88    36   274
297   Ngo         Erik         Finance            Junior      M   81   71   44    37   430
298   Ngo         Anh          Finance            Junior      F   56   69   19   133   459
303   Nguyen      Alex         Finance            Freshman    M   11   86    0    31   316
299   Nguyen      Shalin       Finance            Junior      F   36   21   89   126   437
311   Norton      Justin       Finance            Junior      M   62   41   73    50   363
334   Robinson    Sandeep      Finance            Junior      M   10   76   62     0   343
340   Ryan        John         Finance            Junior      M   60   32   14    11   250
348   Semedo      Caleb        Finance            Sophomore   M   10   0    15     0   263
353   Sizer       Ronald       Finance            Junior      M   99   40   47   189   273
383   Wan         Larry        Finance            Sophomore   M   47   74   96   144   460
387   Wray        Teressa      Finance            Junior      F   94   57   51    87   381
390   Wright      Jason        Finance            Senior      M   57   69    0   152   461
395   Yu          Douglas      Finance            Junior      M   31   37   67    23   371
135   Charan      Jessica      Hospitality Mgmt   Senior      F   45   46   39   103   318
220   Hopf        Randy        Hospitality Mgmt   Junior      M   55   20   47   105   277
227   Jamila      Alicia       Hospitality Mgmt   Sophomore   F   41   71   91    37   367
278   Mahasin     Michael      Hospitality Mgmt   Sophomore   M   33   33   13    73   345
305   Nguyen      Kathy        Hospitality Mgmt   Junior      F   42   98   79    57   439
316   Parenti     Audrey       Hospitality Mgmt   Junior      F   98   0    64   168   410
115   Bala        Joel         Human Resource     Senior      M   47   56    0   144   300
119   Begines     Ashley       Human Resource     Junior      F   32   81   22    41   396
164   Dickey      Branden      Human Resource     Senior      M   54   24   94    93   442
180   Enriquez    Madhumathi   Human Resource     Junior      M   21   37   52    23   386
245   Konye       Linda        Human Resource     Sophomore   F   96   91   15   123   411
273   Ly           Adam          Human Resource           Sophomore   M   61   93   91    27   315
337   Roh          Diane         Human Resource           Sophomore   F   86   18   34   120   436
356   Standberry   Brittany      Human Resource           Sophomore   F   97   62   87   157   263
109   Arellano     John          International Business   Senior      M   62   33   92    53   320
117   Bauer        Jose          International Business   Sophomore   M   70   75   86     0   277
146   Cole         Arishma       International Business   Sophomore   F   37   66    0   146   301
158   Davis        Clifford      International Business   Senior      M   36   44   69   137   465
173   Dove         Arthur        International Business   Senior      M   25   63   89   100   385
187   Fiato        Luis          International Business   Sophomore   M   84   96    0    92   313
196   Garcia       Mai           International Business   Senior      F   35   61   10   114   364
197   Garcia       Steven        International Business   Sophomore   M   97   78    0   153   439
234   Kahl         Brittany      International Business   Sophomore   F   99   0    46   191   257
238   Kapenga      Kimberley     International Business   Junior      F   75   84    0   106   482
241   Kemp         Margaret      International Business   Freshman    F   83   0    46    69   412
247   Kurtich      Monica        International Business   Sophomore   F   38   0    61   165   271
256   Lee          Amin          International Business   Sophomore   M   94   47   44    85   390
262   Li           Armand        International Business   Sophomore   M   18   62    0   176   461
264   Lin          Justin        International Business   Freshman    M   91   69   41    32   476
272   Luong        Sunhee        International Business   Senior      F   83   93   99    67   305
318   Pavlov       Kenny         International Business   Senior      M   89   21    0   187   421
324   Phung        Marios        International Business   Senior      M   62   39   52    47   486
391   Wu           Nicole        International Business   Junior      F   72   58   75    49   417
107   Anderson     Vu            Management               Junior      M   60   27   55     0   359
110   Armanino     Elana         Management               Sophomore   F   59   35   57   196   268
125   Bourgouin    Vincent       Management               Senior      M   51   18   17    20   443
128   Buhagiar     Myngoc        Management               Junior      F   47   35   70   157   286
134   Ceolla       John          Management               Senior      M   52   31   63    49   357
140   Chey         Nicholas      Management               Junior      M   65   90   73   100   265
144   Chung        Shannon       Management               Sophomore   F   38   49   89   168   491
147   Cole         Steven        Management               Sophomore   M   40   81   86     0   406
150   Cornejo      Susan         Management               Junior      F   49   33   14   193   356
153   Cross        Alison        Management               Freshman    F   47   98   97   156   438
154   Cummins      Christopher   Management               Junior      M   77   0     0   142   436
157   Dang         Anthony       Management               Junior      M   98   15   93   173   266
160   Deguzman     Ly            Management               Junior      F   17   74   56   144   420
162   Delapena     Duong         Management               Junior      M   52   50   95    51   301
165   Diep         Bien          Management               Senior      F   43   64   66    62   430
168   Do           Ross          Management   Freshman    M   26   91    4   123   396
171   Dolmatch     Sean          Management   Junior      M   16   0    62   135   392
175   Duong        Christopher   Management   Senior      M   64   0     0    90   251
177   Edmonson     Chris         Management   Junior      M   84   29   99    85   454
178   Eisenbarth   Adam          Management   Sophomore   M   75   30   95   107   301
181   Esterkin     Quynh         Management   Junior      F   45   54   91   101   417
204   Gutierrez    Taylor        Management   Sophomore   F   92   84    0    46   407
205   Ha           Annie         Management   Sophomore   F   90   93   30     0   334
208   Hannibal     Darcy         Management   Senior      M   52   64   88    42   472
211   Hayilu       Christine     Management   Sophomore   F   90   99   41     0   476
215   Hightower    Kristine      Management   Senior      F   38   77   44   171   393
230   Johnson      Patrick       Management   Sophomore   M   29   96   41   192   326
232   Jozaki       Neda          Management   Senior      F   62   0    38    47   324
235   Kambou       Sheryl        Management   Freshman    F   59   87   16   192   385
236   Kamman       David         Management   Freshman    M   63   58   84    69   415
237   Kaneyuki     Gregory       Management   Freshman    M   32   57    0    46   353
251   Landaverde   Olivia        Management   Junior      F   93   20   43    65   323
253   Lau          Kevin         Management   Junior      M   95   25   94   115   270
257   Lee          Hajrudin      Management   Senior      M   18   52   52   175   272
258   Lee          Magdalena     Management   Sophomore   F   22   53   40    56   340
260   Leone        Amber         Management   Sophomore   F   70   56   68     0   325
265   Lipsius      Cody          Management   Freshman    M   38   70   94   175   294
279   Mahmoudi     Philip        Management   Freshman    M   14   34   75    95   431
291   Morris       Kimberly      Management   Freshman    F   76   58    0   128   401
296   Nealon       Daniel        Management   Freshman    M   18   66   97   166   339
300   Nguyen       Adam          Management   Sophomore   M   95   78   89   112   441
312   Oh           Derek         Management   Senior      M   71   42   32    32   250
317   Parian       Matthew       Management   Sophomore   M   24   0     0    92   302
332   Richardson   James         Management   Senior      M   95   71    0   117   412
338   Rono         Mark          Management   Sophomore   M   86   23    0   130   382
339   Rupakula     Ryan          Management   Sophomore   M   41   27    0    20   358
344   Scales       Melanie       Management   Senior      F   52   79    0    55   471
346   Schwartz     Jeff          Management   Sophomore   M   11   84   60    27   466
350   Shue         Camille       Management   Sophomore   F   10   0    45    13   301
362   Tran         Yvonne-Yen    Management   Junior      F   83   0    18    66   304
364   Tran         Dominic       Management   Junior      M   95   0    31   109   443
367   Tran         Charles       Management   Junior      M   57   80    0   157   290
369   Tsang        Truong      Management   Junior      M   39   29   94   185   473
368   Tsang        James       Management   Sophomore   M   95   0    73   114   357
372   Ungari       Robert      Management   Junior      M   96   90   77   121   305
373   Vahdatinia   Jeffrey     Management   Junior      M   14   61   14    95   404
374   Vargas       Michael     Management   Junior      M   88   64    0   163   457
375   Vargas       Tony        Management   Senior      M   28   38    0   164   408
382   Vu           Eric        Management   Junior      M   67   41   93   151   383
384   Wells        Sarah       Management   Sophomore   F   12   0    50    54   375
389   Wright       Mario       Management   Sophomore   M   45   0    76   101   290
393   Yap          Sherry      Management   Sophomore   F   48   0    77   173   326
394   Yip          Mary        Management   Junior      F   93   32   47    71   475
396   Yu           James       Management   Senior      M   99   42   89   192   281
398   Yuan         Navpreet    Management   Sophomore   F   61   83   70    25   376
400   Zmolek       Mark        Management   Sophomore   M   87   96   50   152   326
101   Ablao        Ann         Marketing    Junior      F   67   11   32   144   400
102   Ahmari       Velia       Marketing    Senior      F   51   51   45    33   355
104   Alvarez      Vivian      Marketing    Sophomore   F   60   51   47    13   373
108   Andrews      Elliott     Marketing    Senior      M   62   33   62    53   382
114   Baird        Catherine   Marketing    Junior      F   46   51   64   132   364
118   Bautista     Nicholas    Marketing    Sophomore   M   65   78   40   112   420
122   Benjamin     Jeremiah    Marketing    Junior      M   42   98   17    56   441
124   Bianco       Ogana       Marketing    Junior      F   83   0    57    70   269
130   Bumgarner    Nancy       Marketing    Senior      F   52   78   75    53   419
131   Camarena     Joel        Marketing    Junior      M   46   90   71   121   337
137   Chen         Tony        Marketing    Sophomore   M   37   53    0   157   337
138   Cheng        Jimmy       Marketing    Junior      M   45   56   78   105   333
148   Condino      Blanca      Marketing    Sophomore   F   51   0     0    25   494
149   Cooper       Amir        Marketing    Freshman    M   95   17   54   117   351
152   Craig        Belkisa     Marketing    Junior      M   52   37   40    42   371
156   Cutshaw      Luis        Marketing    Senior      M   58   21    5   167   403
166   Dinsmore     Hong        Marketing    Sophomore   M   86   71    0   137   416
169   Do           Collin      Marketing    Junior      M   42   28   13    43   424
170   Dolci        Brian       Marketing    Junior      M   83   0    14    65   256
172   Dorso        Marc        Marketing    Junior      M   53   36   19    61   309
185   Fernandez    Dominic     Marketing    Junior      M   62   90    0    40   330
186   Fernando     Raymond     Marketing    Sophomore   M   67   93   63   147   301
191   Flores       Adam        Marketing    Freshman    M   92   21   33    46   417
192   Florez        Crystal      Marketing   Freshman    F   19   38    0   184   400
195   Garcia        Christiane   Marketing   Senior      F   88   57   82   166   355
200   Gonzales      Arthur       Marketing   Junior      M   25   27    0   100   392
202   Gray          Chelsea      Marketing   Junior      F   64   38   65    84   413
207   Hall          Alyssa       Marketing   Freshman    F   64   71   92    97   374
209   Harrington    Kimika       Marketing   Senior      F   95   16    0   114   301
210   Hayes         Samson       Marketing   Sophomore   M   68   78    0   172   413
214   Herrera       Anthony      Marketing   Junior      M   91   16   94    35   320
217   Ho            Brandon      Marketing   Freshman    M   55   40   95   109   303
224   Hung          Paiman       Marketing   Freshman    M   13   31   52    69   420
233   Jung          Brandon      Marketing   Senior      M   64   0    34    82   424
240   Kelley        Catherine    Marketing   Sophomore   F   48   82   44   163   286
242   Kewal         Helen        Marketing   Freshman    F   55   45   81   100   253
244   Kolodziejak   Binh         Marketing   Senior      M   43   69   68    73   477
246   Kruschke      Anthony      Marketing   Senior      M   97   0    42   142   410
261   Leung         Brenda       Marketing   Junior      F   67   62   98   156   286
276   Madar         Kristen      Marketing   Junior      F   36   13   94   129   346
280   Mancuso       Chris        Marketing   Junior      M   83   58   78    69   431
282   McDonough     Marcus       Marketing   Freshman    M   10   0    47     0   371
283   Mcgaffey      Paula        Marketing   Freshman    F   91   0    93    23   383
285   Mendonca      Kirk         Marketing   Junior      M   11   18    0    20   402
286   Mikhailova    Andrew       Marketing   Junior      M   32   27   49    40   371
287   Mina          Andrew       Marketing   Senior      M   50   34   76    15   442
306   Nguyen        Justin       Marketing   Junior      M   42   97   19    55   356
308   Nguyen        Renee        Marketing   Junior      F   85   16   26   114   342
310   Nguyen        Kosuke       Marketing   Senior      F   18   0    21   165   481
302   Nguyen        Frances      Marketing   Sophomore   F   84   92   26    84   286
313   Ou            Andrew       Marketing   Senior      M   47   60   19   153   263
319   Penava        Harinder     Marketing   Senior      M   40   22   28     0   464
320   Perez         Natalie      Marketing   Freshman    F   55   31   46   109   362
326   Posada        Thanh        Marketing   Sophomore   M   96   46   58   122   322
336   Rogers        Jeff         Marketing   Sophomore   M   88   12    0   166   456
347   Selig         Bryan        Marketing   Sophomore   M   22   90    0    41   283
349   Sharland      Thong        Marketing   Sophomore   F   99   0    74   184   472
355   Smith         Tan          Marketing   Sophomore   F   89   58   72   188   402
366   Tran          Dwight       Marketing   Senior      M   53   39   16    66   486
365   Tran          Jinny        Marketing   Sophomore   F   46   87   24   132   382
370   Tsigaris      Elaine     Marketing           Sophomore   F   57   42   98   152   282
377   Vo            Max        Marketing           Sophomore   M   44   51    0    92   355
378   Vogel         Danielle   Marketing           Sophomore   F   49   71   73   196   365
379   Vong          Tanya      Marketing           Junior      F   89   92   18   184   452
380   Vu            Michael    Marketing           Junior      M   56   43    0   135   342
385   Wondolowski   Theresa    Marketing           Sophomore   F   96   13   59   129   353
142   Chu           Xiawan     Mgmt Info Systems   Junior      F   64   44    0    96   401
161   Dela          Belinda    Mgmt Info Systems   Senior      F   39   79   34   194   487
213   Hernandez     Gary       Mgmt Info Systems   Freshman    M   89   31   61   189   421
223   Huang         Lily       Mgmt Info Systems   Freshman    F   94   23   88    90   372
239   Kelez         Rayna      Mgmt Info Systems   Senior      F   84   70   64    94   266
249   Kyu           Cody       Mgmt Info Systems   Freshman    M   40   0    77    15   377
274   Ly            Marthe     Mgmt Info Systems   Freshman    F   88   98   22   177   423
290   Mitsunaga     Cory       Mgmt Info Systems   Freshman    M   77   45   30   141   266
309   Nguyen        Manuel     Mgmt Info Systems   Sophomore   M   17   0    62   153   343
315   Pang          Kayla      Mgmt Info Systems   Junior      F   45   84   96   106   462
323   Phillips      James      Mgmt Info Systems   Junior      M   17   37    0   142   354
325   Posada        Jaime      Mgmt Info Systems   Senior      M   95   45   83   101   264
328   Prasad        Robert     Mgmt Info Systems   Junior      M   96   52   88   134   276
329   Preciado      Dung       Mgmt Info Systems   Junior      M   41   53   80    36   384
341   Sabalburo     Vanessa    Mgmt Info Systems   Junior      F   42   34   21    55   433
358   Su            Roseanna   Mgmt Info Systems   Sophomore   F   83   72    0    60   454
392   Yamamoto      Matthew    Mgmt Info Systems   Senior      M   49   86    0   190   303
399   Zhu           Karla      Mgmt Info Systems   Sophomore   F   39   85   12   189   265
105   Amador        Zhichun    Undeclared          Sophomore   F   11   92   93    25   485
112   Avram         Tuan       Undeclared          Junior      M   42   46   87    43   331
113   Badillo       Tony       Undeclared          Junior      M   39   46   40   182   323
120   Bella         Jenny      Undeclared          Junior      F   42   92   54    45   452
123   Bhandari      Neda       Undeclared          Senior      F   64   0    62    87   384
129   Bui           Ziyin      Undeclared          Senior      M   29   69   57   192   466
133   Castro        Nam        Undeclared          Junior      M   51   15   92    33   262
145   Chung         Susan      Undeclared          Junior      F   62   65   84    45   265
167   Dizon         Justin     Undeclared          Freshman    M   91   87   19    33   357
221   House         Kelly      Undeclared          Senior      F   64   0    24    82   412
252   Langley       Hooveral   Undeclared          Sophomore   M   86   22   44   129   284
327   Prasad        Robert     Undeclared          Sophomore   M   96   49   84   129   467
330   Remulla       Jasmine    Undeclared          Senior      F   45   60    0   112   330
343   Sahi        Marvin     Undeclared   Junior      M   88   74   43   165   477
351   Silvester   Matthew    Undeclared   Freshman    M   26   24    0   133   437
357   Stuart      Tina       Undeclared   Sophomore   F   38   72    0   161   494
381   Vu          Taiwuit    Undeclared   Sophomore   F   88   70   33   174   317
386   Wong        Harinder   Undeclared   Sophomore   M   43   34   57    74   433
388   Wright      Maylin     Undeclared   Junior      F   51   18    0    21   419
397   Yu          John       Undeclared   Sophomore   M   66   75   25   126   289
                            Java Juice Café
                            Delivery Order - Invoice
                                                                 Date:
Customer Name
Address
City
Zip Code
County
Phone Number
Email Address

    Quantity                       Item                       Price      Extension
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                                         $     -
                                                  Subtotal               $     -
                     Surcharge - $10 is the Minimum Order
                                           Taxable Amount                $    -
                                            Sales Tax Rate               $    -
                Delivery Fee (Waived if order exceeds $50)
                                              Invoice Total              $    -

                                   Expected Travel Time:

Notes:
                                       ADVANCED FORMULAS AND FUNCTIONS

Formulas using cells in other tabs.
                         Type the name of the worksheet tab, followed by an exclamation mark, in front of the cell address.
                         Example: =Invoice!B8 will show the contents of cell B8 in the invoice worksheet.

                                                            FUNCTIONS
Sum                         Shows the sum of a range of numbers                    SUM(D2:G5)

Average                     Shows the average of a range of numbers                AVERAGE(D2:G5)

Round                       13/7 has an endless string of decimal places. = 1.857142857142
                            Round(13/7,2) tells Excel to delete all of the decimal places except for the first 2 = 1.86
                            Round(13/7,0) tells Excel to delete all of the decimal places. = 2

                                                               FORMS
When working with Forms, you often have to show different data depending on what information is entered into the form.
If there are two possible choices to display, use an IF Function.
If there are more than two possible choises to display, use a VLookUp (or HLookUp) Table.

              IF
An IF function compares a variable to a constant, using an equation.
If the equation makes sense, the first option is displayed in the cell containing the IF Function.
If not, the second option is displayed in the cell containing the IF Function

An IF function can also compare strings of text.
If the text strings match, the first option is displayed in the cell containing the IF Function.
If not, the second option is displayed in the cell containing the IF Function

                             Constant                                 Variable           First Choice
      Speed Limit               65              Your speed                            Ticket and Fine
       Voting Age               18                Your age                               Can Vote
      Drinking Age              21                Your Age                               Can Drink
     Passing Score             81%               Your Score                                Credit
   Inventory Needed             50            Actual Inventory                          Do Nothing
    Miinimum Order             $25              Actual Order                        Charge Order Amount
  MBA: Required GPA            3.00              Your GPA                                You're in!
  Blood Alcohol Level          0.08          Breathalyzer Score                         Night in Jail
  Overtime Pay Starts             40           Hours Worked                         Some Overtime Hours

         VLookUp
Compares a variable to the cells in the first column of a table.                   Variable (A Cell)
If a match is found, it displays the information from a column in that row.                   B8
The information from only one column is displayed
The first column in the table must be sorted A-Z
mark, in front of the cell address.
oice worksheet.




ept for the first 2 = 1.86




ation is entered into the form.




               Second Choice                       Formula
                No Ticket or Fine               IF(D31>65,"ticket", 0)
                   Can't Vote             IF(D32>B32,"Vote", "No Vote")
                  Can't Drink               IF(D33>21,"Drunk","Sober")
                   No Credit                   IF(D34>B34,"CR","NC")
                  Order More                       IF(D35>B35,0,50)
                  Charge $25                     IF(D36>B36,D32,25)
                   Forget it!          IF(D37>B37,"Admitted", "Rejected")
                    No fine                 IF(D38>0.08,"Jail","No Jail")
               No Overtime Hours                 IF(D39>40,D39-40,0)


                 Table (a Range)            Show data from Column
                     A5:D10                       2, 3, or 4

                     Example          VLOOKUP(B8,A5:D10,3)
   VLOOKUP Tables                                           VLOOKUP FUNCTION ARGUMENTS
                                                            The first argument is a cell reference. This c
                                                            The second argument refers to the range wh
                 County Data Table                          The third argument refers to which column in
                                  Delivery      Travel
      County         Tax Rate     Charge        Time
  Contra Costa         8.25%       $10.00         40           The tax rate for
    Monterey           7.25%       $15.00         80
  San Francisco        8.50%       $15.00         60          The travel time to
   San Mateo           8.25%       $10.00         40
   Santa Clara         8.25%        $5.00         20        The delivery charge to
   Santa Cruz          8.00%       $10.00         50

                                                            Change the name of the county in cell G6 to
    Menu Items Table                                        Notice how the numbers in I6, I8, and I10 ch
       Item            Price
       Beer              $3.00                                 The cost to order
      Coffee             $1.75
       Juice             $1.50
      Salad              $4.25                              Change the text in cell G16 to order somethi
     Sandwich            $4.95                              Notice how the price changes in cell I16.
       Soda              $1.50                              Try ordering something that is not on the me
       Soup              $2.95                              To avoid false data use a validation list for th
        Tea              $1.50                              Click on Cell G16 and create a validation list
       Wine              $4.00




                     Presidents
                       Took                                 Fix the function in cell I29: =VLOOKUP(G2
    Presidents         Office         Years   Noted For       The # of years that
     Kennedy           1961             3       Killed
     Johnson           1963             5      VietNam      Fix the Presidents table
      Nixon            1969             6     Watergate     The first column of a Lookup Table must be
       Ford            1974             2     Appointed     Select any cell in the range A29:A34. Click th
      Carter           1976             4      Inflation
     Reagan            1981             8     Berlin Wall   Create a Data Validation List in cell G28. U




                                                                                Ajax Steak and Eggs
Create validation lists in column G                                  Item
Create validation lists in column H   Steaks
Enter VLOOKUP functions in column I   Eggs
                                      Omelet
                                      Beverage




                                                 Steaks Table
                                               Steaks
                                      New York
                                      Rib Eye
                                      Sirloin

                                                         Omelets Table
                                               Omelets
                                      Cheese
                                      Cordon Bleu
                                      Ham
                                      Western

                                               Beverages Table
                                           Beverages
                                      Coffee
                                      Juice
                                      Milk
NCTION ARGUMENTS
ument is a cell reference. This cell will contain the search term.
argument refers to the range where the table and data are stored.
 ument refers to which column in the table to use.


            Santa Cruz  County is  8.00%
                       VLOOKUP(G6,A6:D11,2)
            Santa Cruz  County is    50
                       VLOOKUP(G8,A6:D11,3)
            Santa Cruz  County is $10.00
                       VLOOKUP(G10,A6:D11,4)

name of the county in cell G6 to Monterey
he numbers in I6, I8, and I10 change.

               Juice          is      $1.50
                          VLOOKUP(G16,A16:B24,2)

text in cell G16 to order something else from the menu.
he price changes in cell I16.
 something that is not on the menu. Cell I16 still shows a price.
 e data use a validation list for the input cell (G16).
 G16 and create a validation list. Use cells A16 to A24




tion in cell I29: =VLOOKUP(G29,A29:D34,3)
                 Ford   served was   #N/A

 idents table
umn of a Lookup Table must be in A-Z order.
ell in the range A29:A34. Click the A-Z icon.

ta Validation List in cell G28. Use cells A30 to A35.




      Ajax Steak and Eggs
                Type       Preparation      Price
        Validation List   Validation List   VLOOKUP
        w/hash browns     Validation List    $3.95
        Validation List                     VLOOKUP
        Validation List                     VLOOKUP
                               Total         $3.95



teaks Table
              Price
              $17.50
              $16.50
              $13.95

        Omelets Table
              Price               Description
              $4.45                Cheese
              $5.75       Chicken Ham Swiss-Cheese
              $4.95              Ham Cheese
              $5.45       Peppers Onion Ham Cheese

verages Table
              Price
              $0.99
              $1.19
              $0.99
                                                         Working With Text
                    Task          Combine Columns A & B
                How to do it     CONCATENATE(A5,",",B5)
  Last Name       First Name
Brown          Adina
Chavez         Ainguyen
Chu            Brian
Figone         Carina
Fine           Joelson
Forks          John
Gragg          Juan
Gutierrez      Julia
Ho             Kevin
Lopez          Kris
Macias         Leroy
McQueen        Li
Pala           Marian
Phan           Oscar
Resendez       Ray
Safi           Romel
Tate           Varinder
Ton            Xiumei
Tran           Yesenia


Macros
                 Create a macro to move the cursor into cell A1in this worksheet.
How to do it   Click the pulldown arrow below the macro icon on the View Tab. Choose "Record Macro.
               Name the Macro “TextData”.
               Describe the macro: “Moves the cursor to the TextData Worksheet.” Click Ok. The record
               Click on the TextData tab.
               Click on cell A2. Press Enter
               Press the CTRL and Home keys.
               Click the pulldown arrow below the macro icon on the View Tab. Choose "Stop Recording

                   Create a button on the Contents worksheet to run the macro
How to do it   Position the cursor in cell C7 of The Contents Worksheet
               Draw a textbox covering the entire cell
               Edit the text on the button to say “TextData”
               Right click on the textbox and choose "Assign Macro."
               The Assign Macro dialog box should open.
               Click on the name of your macro. Click OK.
Test the button by clicking on it.
   Working With Text
           Paste Column C Values        Convert Column D back into 2 Columns
            Paste Special - Values    Data - Text to Columns




ursor into cell A1in this worksheet.
ro icon on the View Tab. Choose "Record Macro."

to the TextData Worksheet.” Click Ok. The record macro toolbar will appear.



ro icon on the View Tab. Choose "Stop Recording."

ents worksheet to run the macro
ntents Worksheet


Assign Macro."

								
To top