Formula and functions
Sum Functions
Average Functions
Max Functions
Min Functions
Count Functions
Exercise 1
Create a spreadsheet using the following information. You are the owner of a fruit store, you have owned the fruit store for one complete year. Use the data below to construct a spreadsheet to display the sales figures for the first year of operation of your fruit store.
1) Enter the raw data below, applying as many presentation Features (Font, Font Size, Font Colour, Number Formats and Colour, Cell Shading, Text Rotation, etc) to it as you wish.
2) Apply appropriate number formats to your numbers.
3) Center your spreadsheet horizontally on the page
4) Give your spreadsheet an appropriate title and center it across your spreadsheet.
5) Select the best page orientation for your spreadsheet.
6) Adjust the column width and row height to suit the layout you have selected.
Note: for numbers 7 to use functions (sum, max, min, average and count)
7) Create formula's to calculate totals for each month.
8) Create formula's to calculate totals for each fruit item.
9) Create formula's to display the month with a smallest amount of fruits.
10) Create formula's to display the month with the largest amount of fruits.
11) Create a formula's that will display the average amount fruits per item.
12.) Create a formula's that will display the average amount of fruits per month.
Apples========Bananas
January 2298======== January 6899.21
February 3512.56======== February 6755.33
March 4929.67 ========March 6541
April 5883======== April 6032.79
May 6237.77 ========May 5822.72
June 6566.78======== June 5968
July 6213.88======== July 6333.33
August 6001======== August 6544.11
September 5799.69 ========September 6845.45
October 5527======== October 7000.01
November 3914.55======== November 7216.27
December 2564.99======== December 7283
Oranges========Kiwifruit
January 4923.88======== January 3349.12
February 4444.99======== February 3899.87
March 3851======== March 4336
April 3399.88======== April 4892.01
May 3020.03======== May 5217.97
June 3411.89======== June 4764.10
July 3567.09======== July 4523.22
August 3999.91======== August 4501
September 4255.88======== September 4219.91
October 4873======== October 3877.49
November 5214.95 ========November 3712.12
December 5521.17 ========December 3555.56
Pears ======== Grapes
January 3310 ========January 4847.23
February 4524.65 ========February 5361.58
March 5992.76======== March 5899.24
April 6961.44======== April 6853.01
May 7447 ========May 5471.34
June 7583.87======== June 4534.22
July 7393 ========July 4222.83
August 7110.10 ========August 4137
September 6637.96======== September 3998
October 6275======== October 3556.36
November 4841.71======== November 3111.44
December 3456.11 ========December 2789.74
Peaches======== Nectarines
January 5902.44 ========January 5310
February 7234.12======== February 7772.71
March 5110======== March 8219
April 3521.87 ========April 6989.33
May 1276.34======== May 4535.52
June 1227.30 ========June 1873.38
July 1199.99 ========July 1198
August 1242.09======== August 1241.63
September 1189.73======== September 1187.57
October 1195.42======== October 1222.21
November 1213.14 ========November 1432.43
December 1887.49 ========December 2137.78
If functions
Exercise 2: IF statements
The IF Function
1. On a blank workbook, format the cells of column C to currency using the £ symbol.
2. In cell B1 enter the text Calculating Interest.
3. In cell B3 enter the text Balance.
4. In cell B4 enter the text Interest.
5. Enter any figure in cell C3 for your bank balance.
6. The interest will depend on whether your balance is over or under £500. Use the Insert Function to enter the following function in C4: =IF(C3<500,>
7. The result will depend on the balance you entered in cell C3. Change this balance to £100. The interest should be £5. Change the balance to 1000. The balance should be £70.
8. Save as Exercise3.xls and close the workbook.
Nested If Functions
Exercise 3: Nested IFs
1. On a blank workbook key in the following column heading in cells A1 - D1:Student Name, Mark, Rating, Target Mark
2. Make columns A and D wider to fit text.
3. Key in student names in A2 - A6:Pamela Smith, Jane Peters, Steve Brown, Jackie Moss, Ron Gold
4. In B2 - B6 key in their exam results: 60, 45, 89, 67, 80
5. In D2 key in the Pass mark of 60
6. In D3 key in the Credit mark of 80
7. In C2 key in the formula to calculate whether they have passed, failed, or gained a credit. Try this for yourself first, but ask if you need help.
Hints: Suggest you find who failed first, ie, less than 60, then those with Credit, else Pass.Don't forget to use absolute cell references for D2 and D3. Use, more than or equal to, for the Credit mark.
8. Use AutoFill to copy the formula to cells C3-C6. Check the results. =IF(B2<$D$2, “Fail”, IF(B2>=$D$3, “Credit”, “Pass”))
9. Save as Exercise4.xls and close the workbook. (You will need this workbook for the next exercise.)
Exercise 4: VLookup
Vlookup Function
1. Open exer1.xls and
2. Create a VLookup for the exercise 1.
0 Comments