Ultimate Excel Course #1  Excel Formulas Made Easy: Get Up to Speed with Excel Formulas Fast
Alan Murray, IT Trainer, Coffee Lover and Lifelong learner


1. Introduction
2:44 
2. Introduction to Formulas  Writing your First Excel Formulas
7:50 
3. The Order of Calculation  BODMAS
3:13 
4. Formulas for Calculating Percentages
7:54 
5. Unleash the Power of Excel Functions
8:43 
6. Referencing other Sheets and Workbooks
11:45 
7. Calculating Date Difference including Working Days Only
8:09 
8. Understanding Absolute Cell Addresses
6:22 
9. The COUNTIF Function  An Incredibly Useful Function
13:12 
10. Thank you
0:41

Project Description
For this class project I have multiple exercises for you to work through. Download the projectfiles folder to get access to the files used in the exercises.
Exercise 1  Basic Formulas
 Open the formulasexercise1 spreadsheet.
 Enter a formula in cell F4 to return the total in E4 minus a 10% discount. Copy the formula down to cell F17.
 Enter a formula in cell H4 to return the number of days until the date due. Copy the formula down to cell H17.
 Enter a SUM function in cell H1 to total the values in range F4:F17.
Exercise 2  Using Excel Functions
 Open the formulasexercise2 file.
 Enter a function in cell H5 to return the maximum Maths exam score from the table. Copy the formula across for English, History and Geography.
 Enter a function in cell H6 to return the minimum Maths exam score from the table and copy it across the other subjects.
 Enter a function in cell H7 to return the average Maths exam score and copy it across.
 Enter a COUNTIF function in cell H8 to return the number of pupils to pass Maths (pass mark is 70 or higher). Copy the formula across.
 Enter a COUNTIF function in cell H9 to return the number of pupils who failed Maths (score is less than 70). Copy the formula across.
Exercise 3  Working with Absolute Cell References
 Open the absolutereferencesexercise file.
 Enter a formula in cell D3 to calculate 20% of the total in C3. Do this by referencing the 20% in cell D1, not by typing 20% in the formula. Reference D1 in a way that when you copy the formula to cell D7 it still works correctly.
 Enter a formula in E3 to then add the VAT in D3 to the total in C3. Copy down to E7.
 Click on the Mixed worksheet of the absolutereferencesexercise file.
 In cell B2, Enter a formula to multiply the value in B1 by A2. Do this in a way so that if you copy the formula down to B11 and then across to column J, it continues to work correctly.
Exercise 4  Calculating percentage increase/decrease
 Open the storesales files.
 In cell E6, enter a formula to calculate the percentage increase or decrease between the prior year in column C and the current year in column D. Copy the formula down to cell E20.
 In cell G6, use the COUNTIF function to count how many stores met or exceeded the £15,000 sales target for the current year.
 In cell G9, use the COUNTIF function to count how many stores increased sales from last year.