Excel Lesson 2: Part 6 (Using Functions in Formulas)
Please Note: This session builds on, and assumes that you have completed, the previous Excel sessions. If that is not the case, please go back and complete them before returning to this session.
STEP 1: Getting Started
Launch Excel and create a new blank Workbook or open up an existing Workbook. We will use the familiar Car example:
STEP 2: Excel Functions
A function (in any programming language, application or software tool, including Excel) is simply a pre-written block of logic that performs one, well-defined task. Once created, they can be invoked (used) at will to help you solve a particular problem. Excel has lots of functions and the link below lists them all in alphabetical order:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
In this session, we will demonstrate some of the more common and useful functions:
STEP 3: The SUM Function
This is very easy to use and quickly adds up all the values specified in the cell range. Here we add up the total number of items sold on the 15th December 2020 (Row E4 through to E10).
We can now use the previous formula-copying technique to drag this summing formula over to the next two columns. Again, notice how the cell references automatically update.
STEP 4: The COUNT Function
The COUNT function does what it says – it simply counts the number of cells that contain a value. If the cell is empty it will not be counted. See the following examples:
7 rows counted.
Even if a value is changed to zero, it will still be counted:
7 rows counted.
However, if the cell is completely empty, the COUNT function will ignore it.
See overleaf:
6 rows counted.
Again, you can simply copy this formula across to the final two columns:
STEP 5: The AVERAGE Function
This function returns the average (arithmetic mean) of a series of numbers.
It is almost always the case that the average is a decimal number, even if the original numbers being processed are whole numbers (integers) as is the case here with the ‘Quantity’ column.
As before, you can then copy that formula across to the final two columns, with the cell references being automatically updated as normal:
The next screenshot demonstrates how the three functions respond to zero values:
Only COUNT retains the same result. SUM and AVERAGE change.
In the next screenshot we have three empty cells:
This time it is the SUM function that remains unaltered.
STEP 6: The MIN and MAX Functions
It can often be very useful to know the largest and smallest value in a large column of numerical values, as well as the average. The MIN function extracts the smallest value, and the MAX function finds the largest value. Once created, these formulas can be copied over to other cells as before.
STEP 7: When NOT to use these Functions
All of the previous five functions – SUM, COUNT, AVERAGE, MIN and MAX are designed to be used with numerical data. They generally will not work or will give weird results if you try and apply them to dates or text columns – as the following screenshot illustrates:
STEP 8: Further Help
As already noted, there are lots of Excel functions. Check them out here:
https://support.microsoft.com/en-us/office/excel-functions-alphabetical-b3944572-255d-4efb-bb96-c6d90033e188
Nested functions are covered here:
https://support.microsoft.com/en-us/office/use-nested-functions-in-a-formula-9d7c966d-6030-4cd6-a052-478d7d844166
That completes this session.
AssignmentTutorOnline
- Assignment status: Already Solved By Our Experts
- (USA, AUS, UK & CA PhD. Writers)
- CLICK HERE TO GET A PROFESSIONAL WRITER TO WORK ON THIS PAPER AND OTHER SIMILAR PAPERS, GET A NON PLAGIARIZED PAPER FROM OUR EXPERTS
