Some Cleanup

• Some movement.
  • Selecting ranges
    • click in a cell (say A1 of the raw data)
    • Press and hold ctrl and shift
    • Press the down arrow
    • Press the right arrow.
  • Movement is accomplished by ctrl - arrow.
  • You can always move to a cell or range by typing the cell into the name box on the formula bar.
• Dimensions of a worksheet
  • Find the highest row (1,048,756)
  • Take log₂ of this, just for fun.
  • Find the highest column
  • Figure out how many columns there are available
    • Count A-Z
    • Count AA-ZZ
    • Count AAA-WZZ
    • Count XAA-ZEZ
    • Count XFA-XFD
    • Add these all up. (16,384)
  • Take the log base 2 of this.
  • Assume a 32 bit version
    • You can find this by File -> Account ->About Excel.
  • So a cell contains 32 bits, nice to know.
  • A or 32/8 or 4 bits per byte.
  • So a row has 4 * 1,048,756 bytes
  • Or 4MB of data.
  • While this probably is not killer, it is probably much bigger than many computations.
  • I would be careful, especially when we start working with larger data sets when selecting entire rows or entire columns.
  • You might want to pop up the task manager and watch what is going on.
  • 
• I discovered (John said try this, so that claim is false) something cool and awful designing the last homework
  • Grab a new worksheet, call it basic stats.
  • Copy the headers from the raw data and paste them in column A2:A11
  • Fix the names to match the range names
    • duration (seconds) should become duration__seconds
    • duration (hours/min) should become duration__hours_min
    • date posted should become date_posted
  • 
  • Add the following column headers,
  • 
  • In B2 enter =count(indirect(a1))
    • Not bad
  • Copy this down.
    • Keep an eye on the task manager.
    • Once things start to slow down you can stop.
    • Keep an eye on the lower right hand corner of the worksheet
      • This is called the status bar.
      • Eventually you will see something like
      • 
  • Continue with the count a and blank counts.
  • Remember the sum(1/count(range,range)) function has a problem if there are blanks in the range.
    • So I used =SUM(IF(NOT(ISBLANK(INDIRECT(A2))),1/COUNTIF(INDIRECT(A2),INDIRECT(A2)),""))
    • Copy this down, things should start to slow down now.
    • Notice the error in duration__hours_min
      • This is due to strange characters in the text.
      • We can ignore this for now
  • By hand, classify each field as {Date, Text, Numeric} in column F
  • Find the five number summary
    • 
  • If you made it this far, force a recalculate.
  • I managed to get my excel to use 14% of the CPU, but I have a really good machine in my office.
  • Not the highest % of cpu, but look at memory (compared to 25mb for a clean copy).
  • 
• Indirect
  • I found this blog post.
    • indirect calculations can not be cached.
    • Indirect is sequential
  • We will not look at his solutions right now (we will eventually) we need to know about this problem.
• Remeber, last time we tried to build this using indirects.
  • 
  • 
• So what is happening here.
  • Let's build a model in excel.
  • Grab a new worksheet, or even a new workbook.
  • Assumptions
    • Let's choose a 3GHz machine.
    • 1GHz = 1x10⁹ cycles per second.
    • Let's assume that we have 8 cores.
    • We will assume that the machine can execute an instruction every cycle.
    • We will assume that we have 3000 entries in a column
    • And 9 columns.
    • A simple operation takes 10 instructions per cell
      • Think {=sum(field1,field2)}

      • sum = 0
        for i = 0, i < field1.size(), i++
            sum = sum +  field1[i] + field2[i]
        

      • Excel can vectorize these so there may be some optimizations that make this faster.
      • See Kory Kilburn's numerical classes for more information
      • Or talk to an applied mathematician
      • Or a cs faculty member who is really good at architecture, compilers, or algorithms.
    • A complex operation takes 100 instructions per cell
      • Think {=sum(if(not(isblank(field3))),min(sum(field1,field2))," ")}

      • for i = 0, i < field1.size(), i++
            sum[i] =  field1[i] + field2[i]
        
        sum = 0;
        for i = 0, i < field1.size(), i++
            if (not isblank(field3[i] ) 
               sum += sum[i]
        

    • A hard operation takes the number of row entries * complex operation per cell
      • Think {=sum(1/countif(field1,field1))}

      • for i = 0, i < field1.size(), i++
            count[i] = 0
        
        for i = 0, i < field1.size(), i++
            for j = 0, j < field1.size(), j++
                 if (field1[i] == field1[j]
        	    count[i] ++;
        
        for i = 0, i < field1.size(), i++
            sum = sum + 1/count[i]
        

    • We will probably do 3*rows simple computations (count, counta, ...)
    • We will probably do 5*rows complex computations (max, q1, ...)
    • We will probably do 1*rows complex (unique count)
  • Set up the problem
    1. Add assumptions and constants
    2. Compute the time based on these assumptions for each class
    3. Assume perfect parallelization and compute the final time.
  • Set up the problem.
    • I will make labels bold.
    • I will make input areas blue
    • 
    • The only computation here is b2, which is b1*e1
  • Set up the computation.
    • Headers
    • 
    • In Column C, multiply the number of rows (b6) by the instructions for the operation type
      • Use a vlookup to do this.
      • 
    • In column D, multiply the number of columns (b5) by the times used for each instruction type.
      • Use a vlookup to do this.
      • 
    • In column E multiply c15*d15
      • 
    • Finally, in column f, compute the time for each class.
      • Divide each total instructions by instructions per second (b2
      • 
  • Totals and experiment
    • Compute the total time by summing the individual times.
    • Compute the theoretical best by dividing the total time by cores.
    • 
    • 
  • Let's add a table to classify the results.
    • 0 ≤ time < 0.5 is Good
    • 0.5 ≤ time < 1 is OK
    • 1 ≤ time < 2 is Poor
    • 2 ≤ time aweful.
    • 
    • Add a cell that looks up the parallel time based on the table
    • 
    • 
  • What could make this model incorrect?
    • All of the assumptions
    • The estimate of time used by excel to execute functions ( I bet a factor of 10 at least)
  • By the way, here is a Microsoft article on how to improve excel performance.
  • And Another.
• Vlookup, hlookup
  • He introduces these starting on page 330
  • In general:
    • The thing you are looking for
    • The table you want to find it in
    • The column/row number you want back.
      • columns/rows start at 1
      • And this is usually a hard coded number.
  • The above assumes a ranged lookup
    • The minimum value is the first cell in the lookup range.
    • Values increas in order
    • [min, next cell), [next_cell, ...) [last_value, infinity)
    • There is an error if
      • The lookup value is less than the minimum
      • The values are not in increasing order
  • For an exact lookup
    • A fourth parameter is set to false.
    • The first column/row does not need to be ascending order
    • But the value must match exactly.