Lab 6: Advanced Excel Features

There are a lot of details which you'll need to look, in this assignment. I will be providing hints for the most important ones. There are 4 parts in this assignment. (You can revise the basics of spreadsheet here - Spreadsheet basics) 

#1. Download Lab-6-start.txt from ICON. This will be your starting point.
#2. Convert your data as shown below into your excel file.

#3. PART - 1

• Format Cells: Follow this link to learn about formatting cells. Format Cells.
• Merge Cells:

           If we want to merge the cells L3,M3,N3 and rows L4,M4,N4, select the cells - click Home on the ribbon and click "Merge & Center".

• Now, you need to modify the excel sheet according to the following rules: (Assuming the data is positioned beginning in Cell A1, we need to move down to make room for some title headings.)

1. Add three rows at the top of worksheet. Merge and center a title "Estimated Total Quarterly Sales, Retail and E-Commerce" that spans columns A through I of row 1. Change the font to be larger and bold.
2. Merge cells C3 and D3 and enter the title Retail Sales.
3. Merge E3 and E4, with the title E-Commerce % Total Sales. 
4. Merge F3 and G3. Enter the title Quarter to Quarter % Change.
5. Place Total Sales in F4 and E-Commerce in G4.
6. Merge H3 and I3. Enter the title Year to Year % Change.
7. Place Total Sales in H4 and E-Commerce in I4.
8. Modify all of the column widths as necessary so these headings fit. 

 In the end, you should have something like this

#4. PART - 2 (Formula)

• Now, we calculate values of the columns by using excel formula. For all the formula, once you have finished it for one cell, you can use the formula for the entire column by dragging the 'small plus sign' (that appears when you hover over small box on the right bottom of the cell) to the cell, until which you want to calculate. 

1. E-Commerce % Total Sales = Total E-Commerce Sales/Total Sales. The corresponding excel formula for row 5 will be =E5/D5. Now, drag it all the way down to fill the entire column.
2. Calculate quarter to quarter changes in cells F5 and G5. The formula is the difference between the current quarter (i.e the corresponding retail sales of the current cell you are in) and previous quarter’s sales divided by previous quarter’s sales.
3. Starting with 2013 1st quarter, enter the formulas to calculate year to year changes for both total sales and e-commerce in cells H5 and I5. This formula is similar to the formulas for part 2 above, except that to get a yearly difference for the same quarter. Check for Q1 in 2013 and Q1 in 2012.

#5. PART - 3 (Formatting) 

• It's time to format the values we have and make it more presentable.

1. Format the values in columns C and D as money, with a dollar sign, commas and no decimal places.
2. Format the remaining values as percentages with 2 decimal places.
3. Add appropriate borders and colors to separate the columns whose values you calculated.
4. Add conditional formatting to display all negative percentage values in columns F and G s in red. Drag the formatting to the entire column using the 'small plus sign' as mentioned above. You can use conditional formatting as shown below: 
5. Conditional formatting for column H and I: when a current value decreases from the corresponding value in the previous quarter, you display those values in bold. Place the conditional format in cells H5 and I5, and then copy it down. Hint: make a new rule. The formula for H5 is = H5 < H6.

#5. PART - 4 (Charts)

1. Create a new worksheet named Graphs on which to draw the chart. Use the string function Concatenate (Use this link to learn about this function concatenate function) to create label values in cell C5 (in a new column). Then copy the formula down the rest of the column using the ‘small plus sign’. After this, your excel should look like this: 
   
2. Create a bar graph based on the new column (C5) and Quarterly Percentage Changes for both Total sales and E-Commerce Sales. Format the value axis numbers as percentages and show the corresponding quarter beneath each pair of sales values for that quarter.

          

#6. Almost done!!

1. Rename the tab of each worksheet Like: ‘Data’ for the sales details and ‘Chart’ for the bar graph. The footer on the left, the date in the middle, and the page number on the right hand side. Hint: Insert, Custom Footer. 
2. Format each page so that when printed, each page shows Prepared by: Your Name in
3. Set the page to Landscape mode. 
4. Use Wrap Text on the Home menu to make all text in a column heading visible.
5. Experiment with Print Preview to determine which pages are better formatted as portrait or landscape when printed. Fit each worksheet into one page!

#7. When you are done, turn in your excel sheet to the dropbox.
#8. Grading (Total-20 points)

      Based on Formula

• Concatenation: 1 point
• % Total: 2 points
• Quarter to Quarter % change: 2.5 points
• Year to year % change: 2.5 points

      Based on Formatting

• Negative values - red, Descending values - bold: 2.5 points each (5 points)
• Rest of formatting (colors, cells): 2 points

      Based on charts and others

• Chart: 3 points
• Fit into one page, landscape, footer: 2 points
• Extra rules (conditional formatting): -1 point

     

Lab 5: Learn HTML canvas

What is a canvas?
  A canvas, is a drawing surface! As simple as that. 

How is it used in HTML5?
The HTML5 Canvas element is an HTML tag similar to the <div>, <a>, or <table> tag, with the exception that its contents are rendered with JavaScript.

What is Javascript?

JavaScript is the programming language of the Web. All modern HTML pages are using JavaScript.

Let's get started!
Initial set up

1. You have a file, 'Lab-5-initial-page.zip' in your ICON. Download that. 
2. Remember to extract the file. Right click on the .zip file and click 'Extract all'. Now, you will have a folder named 'Lab-5-initial-page'.
3. Go to the folder 'Lab-5-initial-page'. 
4. To view the design: Right click on 'Lab-5-initial.html' - click 'Open with' - click 'Google chrome'.
5. To view the code: Right click on 'Lab-5-initial.html' and click 'Edit with Notepad++'. Now you will see the HTML/Javascript code. 
6. Now, you can edit in two ways:
1. Edit the Notepad++ and view the changes on browser by refreshing it
2. Or, copy all the code in your Notepad++ and paste them here http://codepen.io/pen/ and view the changes interactively. 

Know what you have inside 'Lab-5-initial.html'

1. This is how you initialize a canvas. This means that, we are introducing a canvas, with id="myCanvas" and it has the given height and width. We initialize a canvas, inside the <body> tag or <head> tag.

<canvas id="myCanvas" width="500" height="600">

</canvas>

2. Start a script. The canvas element is the actual DOM node that's embedded in the HTML page.  The canvas context is an object with properties and methods that you can use to render graphics inside the canvas element.  The context can be 2d or webgl (3d).

Each canvas element can only have one context.  If we use the getContext() method multiple times, it will return a reference to the same context object. 

Don't worry if you did not understand any of this. Just remember that, you need to define canvas element and context.

<script>

    var c=document.getElementById("myCanvas");

    var ctx=c.getContext("2d");

</script>

3. The cool part! Inside the script, you write functions to draw the shape.

  ctx.beginPath(); // start new shape       

  ctx.fillStyle = "green"; // fill the color of the shape with green

  ctx.lineWidth = 10;       // border thickness

  ctx.strokeStyle = "red"; // border color

  // Draw the circle.

 // (110,120) is the center point(x,y) - 80 is the radius of the circle - 0 is the start angle and  //2*Math.PI is the ending angle (in radians)

  ctx.arc(110, 120, 80, 0, 2*Math.PI); 

  ctx.fill();   // fill it with the color specified above

  ctx.stroke(); // draw!

4. From here, use this websites to help you understand canvas functions.

 HTML5 Canvas - Tutorial 1

 HTML5 Canvas - Tutorial 2 (w3schools)

HTML5 Colors

Using those websites, accomplish the following tasks for Lab-5

1. Colored shapes must be uniformly distributed on the canvas – shapes are not colliding with each other. Make sure it’s well aligned. (2 points)
2. Draw a border to the canvas. Provide a message in case the browser does not support the canvas element. (1 point)
3. Draw a circle – with a border, using the arc( ) method. (1 point)
4. Draw another circle – filled with a gradient that has several color stops. (4 points)
5. Write a message on the canvas using strokeText -- use a different gradient with several color stops as strokeStyle. (3 points)
6. Draw a colored rectangle with borders using fillRect and strokeRect. (2 points)
7. Draw at least 3 small circles of different colors on top of the rectangle. Note: No borders on these circles. (3 points)

After you finish, your final HTML page, should look something like this.

What to submit? (4 points)

If you did it using this - http://codepen.io/pen/ website, copy your code and paste it into your 'Lab-5-initial.html'.

Once you have your assignment ready, upload it to your MyWeb account and turn in the link and the .html file to your dropbox. 

Refer: 

Webpage as hyperlink on ICON

HTML/CSS/JS: Very useful website

I found some of the students, using this interactive website to code in HTML and CSS. You can also use it with Javascript, which you'll be working in future lab assignments. Try it out.

http://codepen.io/pen/

A screenshot is attached to help you start with it. 

Lab 4: Basic Spreadsheet

For this lab, you'll work on some basic concepts and function in spreadsheets. It is a pretty straightforward lab and it is fun to play with spreadsheet charts.

1. What are spreadsheets?

• A spreadsheet is a table of values arranged in rows and columns. Each value may have a predefined relationship to the other values. If you change one value, therefore, you may need to change other values as well.
• Spreadsheet applications (sometimes referred to simply as spreadsheets) are computer programs that let you create and manipulate spreadsheets electronically.
• One of the most widely used Spreadsheet application is, Microsoft Excel.

2. Some basic elements of spreadsheet:

Formula Bar
 In addition to the Ribbon Excel also has a Formula Bar. The Formula Bar is located above of the workbook window and allows you to enter or edit values or formulas in cells or charts. The Formula Bar also displays the value or formula used in the active cell.

3. Learn about various formatting you can do in Excel: http://www.excel-easy.com/basics/format-cells.html

4. Learn about various function/formula which can be used for easy calculation and results. Below are some helpful links about SUM formula. Figure out, how to use AVERAGE formula.

• http://www.wikihow.com/Use-the-Sum-Function-in-Microsoft-Excel
• http://www.excel-easy.com/functions.html
• http://www.excel-easy.com/introduction/formulas-functions.html

5. Some helpful links to learn about creating charts using Excel. Learn about various chart elements like Legends, Chart Title, Data Label,

• http://www.excel-easy.com/data-analysis/charts.html
• http://www.excel-easy.com/examples/pie-chart.html
• http://office.microsoft.com/en-us/excel-help/create-a-chart-HP001233728.aspx#BM5

6. Try it yourself: Sort the columns in your Excel.

7. In the end, you should have a spreadsheet, that looks like this:

Note: The SUM formula is used to calculate the 'Total'. 

8. Submit the Excel file you created in the dropbox.

Lab 3: HTML5 & CSS3

Before you start with Lab-3, I suggest that you go to these links and make yourself familiar with the CSS format.

• HTML-CSS
• Begin CSS here
• Learn about selectors

1. Download the .zip files on ICON. It has two files viz., css_sheet.css and lab-3-start.html. As you already know, lab-3-start.html has the content of the page and css_sheet.css has the styles using which the html's content will be displayed.
2. Modify the files, to obtain the following styles:

• Use rounded corners and borders on all of the blocking elements. (Rounded border)
• Use 2 different linear gradients as background and change background for every blocking element. (Gradients)
• 3 different hover links. Links should change color when you hover. (Hover)
• Use text shadows on at least 2 pieces of text. (TextShadowGenerator, Text Shadow)
• Change the title (inside the title tag) to your name, change most of the original text.
• Use different colors for a good design. (Colors)
• Transfer the page to MyWeb using Filezilla.
• Extra: (Not mandatory) Insert a video inside a new article on the page. The video should play on the “OnMouseover” event, stop on the “onmouseout” event and resize when the page re-sizes. 

Start CSS here..

To begin writing a CSS, follow the same way as you created a HTML file (Click here). In the end, 'Save as.." , '.css' instead of '.html' .

CSS Syntax

• The selector points to the HTML element you want to style.
• The declaration block contains one or more declarations separated by semicolons.
• Each declaration includes a property name and a value, separated by a colon.

Example

myStyle.css

body {
    background-color: lightblue;
}

p {  color: red;  text-align: center;  }

Now let's try to use this with HTML file. The text in bold, tells you how to link a CSS file inside HTML.

myHTML.html

<html>

<head>

 <link rel="stylesheet" type="text/css" href="myStyle.css">

</head>

<body>

 <h1>This is a heading</h1>

 <p>This is a paragraph using CSS style.</p>

</body>

</html>

Now, if you want to modify the tag <p>'s color to 'green', then you can directly go to 'myStyle.css' file and change, ' color: green; '. Try it yourself and see the difference.. Can you see the advantages of CSS now? 

• Modifying styles is easy. Go to .css file directly and change it there!
• Re-usable: Use the same CSS styles for different HTML file.
• Not very complex to learn.
• Fun to play with styles :)

Once you're familiar with the format, go to this link and start learning about changing background, text, fonts etc. It'll be fun! :)

HTML and CSS

Okay! Let's define the HTML- CSS relationship. A simple HTML file looks like this. 

What if we wanna add some styles and make it goofy? Like this?

Or, scary (Halloween-ish)?

And, we'd like to give different styles to the same HTML file? Or perhaps, same style to different HTML files? That's when we need CSS! Let's define CSS now.. 

• Cascading Style Sheets (CSS) is a style sheet language.

• Styles define how to display HTML elements.

• Styles are normally saved in external (separate) .css files. 

• External style sheets enable you to change the appearance and layout of all the pages in a Web site, just by editing one single file!