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 location.
8) Create formula's to calculate totals for each fruit item.
9) Create formula's to display the place and items with a smallest amount of fruits.
10) Create formula's to display the place and items 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 location.
Apples
Panabo==2298
Davao==3512.5
6Sta. Cruz==4929.67
Digos 5883
General Santos 6237.77
Kabacan 6566.78
Butuan 6213.88
San Francisco 6001
Mati 5799.69
Makilala 5527
AMAS 3914.55
Cabantian 2564.99
Oranges
Panabo 4923.88
Davao 4444.99
Sta. Cruz 3851
Digos 3399.88
General Santos 3020.03
Kabacan 3411.89
Butuan 3567.09
San Francisco 3999.91
Mati 4255.88
Makilala 4873
AMAS 5214.95
Cabantian 5521.17
Pears
Panabo 3310
Davao 4524.65
Sta. Cruz 5992.76
Digos 6961.44
General Santos 7447
Kabacan 7583.87
Butuan 7393
San Francisco 7110.10
Mati 6637.96
Makilala 6275
AMAS 4841.71
Cabantian 3456.11
Peaches
Panabo 5902.44
Davao 7234.12
Sta. Cruz 5110
Digos 3521.87
General Santos 1276.34
Kabacan 1227.30
Butuan 1199.99
San Francisco 1242.09
Mati 1189.73
Makilala 1195.42
AMAS 1213.14
Cabantian 1887.49
Bananas
Panabo 6899.21
Davao 6755.33
Sta. Cruz 6541
Digos 6032.79
General Santos 5822.72
Kabacan 5968
Butuan 6333.33
San Francisco 6544.11
Mati 6845.45
Makilala 7000.01
AMAS 7216.27
Cabantian 7283
Kiwifruit
Panabo 3349.12
Davao 3899.87
Sta. Cruz 4336
Digos 4892.01
General Santos 5217.97
Kabacan 4764.10
Butuan 4523.22
San Francisco 4501
Mati 4219.91
Makilala 3877.49
AMAS 3712.12
Cabantian 3555.56
Grapes
Panabo 4847.23
Davao 5361.58
Sta. Cruz 5899.24
Digos 6853.01
General Santos 5471.34
Kabacan 4534.22
Butuan 4222.83
San Francisco 4137
Mati 3998
Makilala 3556.36
AMAS 3111.44
Cabantian 2789.74
Nectarines
Panabo 5310
Davao 7772.71
Sta. Cruz 8219
Digos 6989.33
General Santos 4535.52
Kabacan 1873.38
Butuan 1198
San Francisco 1241.63
Mati 1187.57
Makilala 1222.21
AMAS 1432.43
Cabantian 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: 70, 65, 89, 77, 80
5. In D2 key in the Pass mark of 75
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