STATISTICAL IF 

TAX CAL CULATION				TAX SLAB
NAME	ANNUAL INCOME	TAX		FIRST	100000	0%
RAM	1000000	310000		NEXT	100000	10%
SHAM	100000	0		NEXT	100000	20%
LINA	250000	20000		NEXT	100000	30%
MANGESH	350000	45000		NEXT	500000	40%
JOHN	850000	240000		OTHER		50%
ALI		0
IF(B15<=100000,0%,IF(B15<=200000,(B15-100000)*10%,IF(B15<=300000,0+10000+(B15-200000)*20%,IF(B15<=400000,0+10000+20000+(B15-300000)*30%,IF(B15<=900000,0+10000+20000+30000+(B15-400000)*40%,0+10000+20000+30000+200000+(B15-900000)*50%)))))

Intermediate Microsoft^® Excel: Practice 1

RUBRIC

0	3	5	8	10
Less than 25% of items completed correctly.	More than 25% of items completed correctly	More than 50% of items completed correctly	More than 75% of items completed correctly	All items completed correctly

Each step to complete is considered a single item, even if it is part of a larger string of steps. 

Objectives:

The Learner will be able to

1.  Apply Currency formatting to cells in Excel

2.  Use the AutoSum tool to add cells

3.  Use the Function tool to calculate the average of a range of cells

4.  Insert an IF function

5.  Rename spreadsheet

Working with Equations

Enter the labels and format the labels big, bold, and centered

·         In Cell A1 type: First Name

·         In Cell B1 type: Last Name

·         In Cell C1 type: Department

·         In Cell D1 type: Salary

Add the data

Type at least five sample records. For example:

	A	B	C	D
1	First Name	Last Name	Department	Salary
2	Deeter	Poohbah	Training	$34,000

 

Format the Columns

Select column D and use the Currency tool

Creating Equations

Select Cell D7 and use AutoSum to add up the SUM of the Salaries in the D Column.

Select Cell D8 and use Insert Function to calculate the AVERAGE of the Salaries.

Using IF functions

This activity compares the employee’s salary with the Average in Cell D8.

Insert the label "Compare" into cell E1

In E2, insert the IF function.

            In the Wizard, enter the following information

                        Logical Test: D2>D8

                        Value_if_true: "Above"

                        Value_if_false: "Below"

Use the Insert Function wizard to put the correct formula for the remaining cells.

Save the spreadsheet and name it: Excel Intermediate Practice 1

Intermediate Microsoft^® Excel: Practice 2

Objectives:

The Learner will be able to:

1.  Explain what labels are

2.  Sort Excel data by using the labels in the header row

3. Create a Custom Sort

4. Modify the Custom Sort Order

5. Change Page Orientation

6.  Create Custom headers and footers

7.  Save the spreadsheet

Sort Data

Work with Sample Data

Open the sample Excel list, Sales.XLS

When prompted, SAVE to your Documents folder

Review the Data

	A	B	C	D	E	F
1	Month	Client	Category	Service	Class	Date
2	January	Rick Towner	Private	Training	Access	1/12/2004
3	January	Darlene Davis	Private	Training	Access	1/15/2004
4	January	Hometown Community College	Educational	Training	Word	1/15/2004
5	February	Hometown Community College	Educational	Training	PowerPoint	02/05/2004
6	February	Harmony Kitchen And Bath	Corporate	Training	Excel	02/07/2004
7	February	Database Consultants	Corporate	Training	Access	02/10/2004
8	February	Bay County	Government	Training	Outlook	02/12/2004

Sort the Data

Select the entire spreadsheet and Sort the data by Month

            Did the Months sort as expected or did they sort alphabetically?

            Try the Sort again: use the CUSTOM SORT and change the Order to Custom List

Modify the Page Layout

Format the following Page Layout Options:

            Make the orientation "Landscape"

            Create a Custom Header and type a sample company a name in the center

            Create a Custom Footer with the current date on the right

Save the spreadsheet and name it: Excel Intermediate Practice 2

Exercise 2.1

Formatting

You have a number of friends who are going to different countries for their holidays. Each is taking £150 spending money, in the local currency. You have been asked to calculate how much local currency they will get, given the current rate of exchange (you may check the paper or look up the internet for up to date exchange rates if you wish —
try: www.x-rates.com/calculator.html).

1.      Enter the following data into your spreadsheet.

2.      Use formulas as appropriate to calculate the value of spending money in the local currencies.

3.      Row 1 – set text to size 16, bold.

4.      Row 3 – set text alignment to vertical, format font to size 14, bold and red.

5.      Column C – format figures to appropriate currency.

6.      Rows 4–7 – set text to size 14.

7.      Use custom borders and shading to further enhance as you wish.

8.      Insert an appropriate clipart image to the right of the data — format as you wish.

9.      Apply any other formatting as desired.

10.  Save your workbook with a suitable name.

Exercise 2.2

Absolute/Relative Cell referencing

Answer the following questions:

		True or False?
By default, all cell addresses are	Relative
The keyboard shortcut used to change F4 to $F$4 in a formula is	Shift [F4]
$B7 in a formula in C7 copied to cells C8:C10 will become	B8:B10
A$9 in a formula in F9, copied to cells G9:I9 becomes	B$9:D$9

Exercise 2.3

Review of Absolute/Relative cell addressing and statistical functions

Enter the following data into a new worksheet, using the following guidelines:

Cell range C6:C9 — formulas

Cell range D6:D9 — formulas

Cell range C11:C15 — functions

Format all figures appropriately (currency, percentage, integer etc)

1.      Enhance the layout by formatting as required.

2.      Use an appropriate function to display today’s date in D2

3.      Rename the worksheet Furniture Sale.

4.      Put your name and today’s date in the worksheet footer.

5.      Save your worksheet.

6.      Take a print out showing formulas, with column and row headings and gridlines displayed (adjust column widths to display complete formula if necessary).

7.      Take a print out showing data.

Exercise 2.4

Cell formatting

On a new worksheet, create a worksheet to calculate the December wages for your temporary staff data, following the guidelines given:

Data entry: Row 1:4; A5:B14; all other cells contain formulas/functions.

Format the worksheet as you wish, but ensure that:

¨       Row 3 — has the date and time formatted as shown.

¨       Column headings in row 4 are formatted to text wrap within cells.

¨       Monetary values formatted to currency with two decimal places.

¨       Borders used effectively to add emphasis.

¨       Font formatting applied and shading used.

¨       ClipArt and/or drawing tools used to add seasonal theme.

It should look similar to the one below:

EXCEL PRACTICAL ASSIGNMENT FOR RSCIT STUDENTS

EXCEL PRACTICAL ASSIGNMENT FOR RSCIT STUDENTS

Assignment 1

Type the following data in excel worksheet and save it as first.xls
Type the following data in excel worksheet and save it as first.xls


A                     B                 C                  D                  E
513
501
504
513
511
516
532
504
432
501
510
517
479
494
498
Do the following
(a) Highlight column A and copy it to column C
(b) Sort the data in column C in ascending order
(c) What is the lowest number in the list (use a function)
(d) Copy the data in column A to column E and sort it in descending order
(e) What is the highest number in the list (use a function)
(f) How many numbers in this list are bigger than 500 (use a database function)
(g) How many numbers in column A are between 520 and 540 inclusive
(use a database function)
Assignment 2.
Type the following data in excel worksheet and save it as second.xls.
A                                            B                                     C                                 D
People per physician                Life Expectancy
X                                            Y                                      X * Y
370                                         70.5
6166                                       53.5
684                                         65
449                                         76.5
643                                          70
1551                                        71
616                                         60.5
403                                         51.5
Do the following
(a) Complete column C for finding product x * y
(b) Find sum of x column at the end of data
(c) Find sum of y column at the end of data
(d) Find sum of x * y column at the end of data
(e) Find sum of x^2
(f) Find sum of y^2
Assignment 3.
Enter the following data and save it in grade .xls
Name       Marks1        Marks2            Marks3       Total              Percentage         Grade
Amit         80                70                     80
Renu        70                60                      90
Rajeev     60                50                      80
Manish     50               30                      90
Sanjeev    40               40                      80
Anita         70               70                     90
Do the following
(a) Compute the total marks and percentage of each student by entering appropriate formula.
(b) Compute the grades based on following criteria
If percentage >= 90 then grade = A
If percentage >= 80 and <90 then grade = B
If percentage >= 70 and <80 then grade = C
If percentage >= 60 and <70 then grade = D
If percentage < 60 then grade = E
Assignment 4.
Using grade.xls to perform the following formatting operations
(a) Draw a border around the worksheet
(b) Change the font size of heading to 14 points and underline it and hide column c
(c) Increase the width of column A to 15 characters
(d) Right Align the values in column B, C, F
Assignment 5.
A university maintains a year wise result for four courses and then generates an average report as given below
Sr no.      Year            Course1         Course2         Course3           Course4             Average
1             2002            356                300                300                  400
2             2003            200                400                200                  450
3             2004            256                500                400                  600
4             2005            400                600                500                  550
5             2006            456                450                550                  450
6             Total
(a) Complete the report to calculate the course wise average in row 6
(b) Provide formula to calculate year wise average in column G
(c) Generate a column chart to compare data
Assignment 6.
A person wants to start a business and he has four schemes to invest money according to profit and years. Find out which scheme is the most profitable.
Investment Amount               Percentage for Profit              No of years
20000                                  10%                                      6 years
40000                                  20%                                      5 years
14000                                  30%                                     4 years
12000                                  15%                                     5 years
Assignment 7.
A company records the details of total sales (in Rs. ) sector wise and month wise in the following format
                          Jan             Feb              March                       April
Sector 30          12000        17000          14000                       15000
Sector 22          14000       18000           15000                       16000
Sector 23          15000       19000           16000                       17000
Sector 15          16000       12000           17000                       18000
(a) Enter the data in a worksheet and save it as sector.xls
(b) Using appropriate formula, calculate total sale for each sector
(c) Create a 3-D column chart to show sector wise data for all four months
(d) Create a 3-D pie chart to show sales in Jan in all sectors

practical

In column E, you have the average result for each students for an exam. In column F, write a function which returns if students Pass or Fail if the average mark is greater or equal than 10.

Change your function to display

·        Fail (when the average is below 10)

·        Pass (when the average is between 10 and 12)

·        Good (when the average is between 12 and 14)

·        Very Good (when the average is between 14 and 16)

·        Excellent (when the average is beyond 16)

Student	Course 1	Course 2	Course 3	Average	Result
Luc	8	15	9	10.67	Fill your formula here
Estelle	4	15	16	11.67
Laurent	11	6	8	8.33
Paul	17	16	3	12.00
Léa	17	18	10	15.00
Murielle	6	5	13	8.00
Thierry	18	19	15	17.33

Exercise : Management of the stocks

You manage inventory and orders of a company. You can not honor orders if you do not have the full amount requested.

So you create an IF function that will check that you have the quantity in your stock. If your stock is lower, you can not deliver the amount you have.

For example you can write the following function:

=IF(C3<B3,C3,B3)

For the second part of the exercise, you initiate an order with your suppliers if you could not fulfill the entire order. You can write the formula in different ways for an equivalent result

=IF(D4<B4,B4-D4,"")

or

=IF(D4=C4, B4<D4,"")

Order
Item	Qty order	Qty in stock	Qty delivered	Qty to delivered(ans)
Smartphone	50	45		45
Ipad	37	51		37
USB Stick 4Go	12	15		12
USB Stick 8Go	75	47		47
USB Stick 16Go	8	10		8

Exercise : Equity portfolio

You have a stock portfolio. In column C you have the purchase price and column D the last price. Write the result in column E if you win money or in column F if you are losing. The result should also take into account the number of shares you own.

Then you make the sum of columns E and F and then in cell E9, you make the difference in cell E8 and F8 to see if you gain or loss.

(Solution in the other tab in the workbook)

Portfolio
Stock	Volume	Buy	Last	Gain	Lose
AAA	25	35.4	45.8
BBB	53	42.8	37.5
CCC	50	86.1	88
DDD	75	75	69
EEE	100	8	10
			Total
solution
Portfolio
Stock	Volume	Buy	Last	Gain	Lose
AAA	25	35.4	45.8	260	0
BBB	53	42.8	37.5	0	280.9
CCC	50	86.1	88	95	0
DDD	75	75	69	0	450
EEE	100	8	10	200	0
				555	730.9
			Total	-175.9