Excel Beginners

Intro To Formulas: Step 3 To Mastering Spreadsheets

Taking things one at a time

Function Examples - Level 1

Overview

Feel free to test out the functions we have just been exposed to on your own and see how comfortable you can get with them. It is normal that it may take more practice to learn some of these functions over others. In this section we will look at examples of how each function works, and even see how some of them can be used in conjunction with one another!

Here is the data which we will be using for all the examples (note that you can copy the exact table and paste it into cell A1 of a blank Excel file to follow along):

Note: It is possible that the date values in the first column are not compatible with the standard format for your region. Please ensure that these values are valid dates.

Level 1 Examples

The functions will be presented in the same order as they were in the previous section.

• AVERAGE

Let’s find out what the overall average number of units sold for a single product is. To do this, we need to take the mathematical average of all of the Quantity column of information.

=AVERAGE(C2:C18)

The output should be the number 2.41 (additional decimal places are hidden when using the Number format).

• COUNT

What if we wanted to see how many different products were sold on April 28th? To do this, we will select only the cells with that date being displayed to get a count of them within the count function.

=COUNT(A9:A12)

The output should be the number 4.

• MAX

Even though every product originally cost different prices, we want to know which product brought in the most money on any given day. To do this we will select the entire Revenue column of information within the max function.

=MAX(D2:D18)

The output should be the number $29 (when formatted as currency it will display with your currency symbol and two decimal places).

• MEDIAN

Now that we know the highest return from any product, let’s find the median return from our data. We will again be selecting the entire Revenue column, this time with the median function.

=MEDIAN(D2:D18)

The output should be $11 (when formatted as currency it will display with your currency symbol and two decimal places).

• MIN

To finish off our sales analysis, let’s find the lowest return by any product across our dates. For the third time we will be selecting the entire Revenue column for the min function.

=MIN(D2:D18)

The output should be $2.75.

• ROUND

The store decided that it wanted to change all their pricing so that no one needed to carry change anymore. To test this, we will use the round function on the revenue column, at the amount paid when a client purchased one apple on April 28th.

=ROUND(D10,0)

The output should be $3, however, had the store decided to go in increments of 10 cents, we could have ha a result of $2.80.

• ROUNDUP

After reviewing the potential policy, the managers decided they would instead promote clients to donate their change (up to the next dollar value) to a charity. To see how this would look, let’s see what would happen to the value of the transaction where someone bought one carton of milk on the 28th.

=ROUNDUP(D12,0)

The output should be $5. Regardless of how small the decimal value is, the result will always be rounded upwards.

• ROUNDDOWN

While management likes the donation strategy, they also want to apply discounts for seniors. For seniors they will cut the change out of every line item on their bill. To visualize this, we will see what the transaction for three boxes of chicken nuggets on April 30th would look like.

=ROUNDDOWN(D5,0)

The output should be $19. Regardless of how large the decimal value is, the result will always be rounded downwards.

• SUM

Despite all these conversations, we have yet to figure out how much money was generated over these days. We will select the entire Revenue column to see the total of all our sales, thanks to some help from the sum function.

=SUM(D2:D18)

The output should be $199. Which represents the total revenue from all sales.

• AND

Sometimes it is necessary to validate two or more criteria before proceeding with next steps. Let’s say that we want to validate if, when milk was bought, three cartons were purchased. To do this we can use the and function to do this. We will use the milk transaction on April 28th.

=AND(B12=“Milk”,C12=3)

The output should be FALSE as while the first argument is TRUE, the second argument is FALSE.

• FALSE

As the false function does not have any arguments, it will always return the value of FALSE.

=FALSE()

The output will be FALSE as this function cannot generate any other value.

• IF

Let’s say we want to understand which products are only being bought once in a day. But if they are being purchased more than once we want to see the amount of revenue they generated instead of their name. We will use the if function to achieve this for the frozen pizza transaction on April 27th.

=IF(C15=1,B15,D15)

This output should be ‘Frozen Pizza’. Had we taken the cells in the row either above or below it, we would have returned the revenue amount for that line.

• OR

If we wanted to sort products to see on which days a product either sold one unit, or brought in less than $5, to do this we would need the or function. We will take the same frozen pizza transaction from April 27th as in the last example.

=OR(C15=1,D15<5)

The output should be TRUE, as while the product did bring in more than $5, it still only sold one unit that day.

• TRUE

As the true function does not have any arguments, it will always return the value of TRUE.

=TRUE()

The output will be TRUE as this function cannot generate any other value.

• LEFT

The store wants to create their own product codes for different items. To do this they are assigning a random number to each product, followed by the first three characters of the item. We are testing the system out on any of the chocolate bar rows, with the product code 10035.

=10035&LEFT(B2,3)

The output should be ‘10035Cho’, as we joined both segments of the formula together using the ampersand (&).

• MID

Management didn’t like how the codes worked with multiple letters in them, and now want only the second character of a product name to be at the beginning of the product code. We will test this example with any of the milk rows, using the same product code of 10035.

=MID(B8,2,1)&10035

The output should be ‘i10035’, as now we have asked the system to return the second character from the initial string, and appended the product number at the end of the code with the ampersand (&).

• RIGHT

Apparently our management is very indecisive, as now we are asked to test product codes with only the last letter of a product name located at the start of the product code. We will again use the code 10035, this time with any of the chicken nuggets rows.

=RIGHT(B5,1)&10035

The output should be ‘s10035’ as now we are only returning the last character of the string for the product code.

• DATE

Moving away from our example data for this, let’s go under the assumption that we do not know the standard date format for our region. If we wanted to see this format, we can use the date function to know how to input our data. We will use the date 2022-06-19 for this example.

=DATE(2022,6,19)

The output should be the date of 2022-06-19, in your local region format.

• DAY

As the store stays open, we may want to compare trends between similar days or months. To account for the first of these, we have the day function. We will see it’s applications on any of the rows with the date of the 28th.

=DAY(A9)

The output should be 28. As long as the cell is in a valid date format the function will return the day of the month.

• MONTH

Continuing the process of separating the dates into their components, again using any of the rows dated the 28th, we will retrieve the month number from the cell. To do so we will use the aptly named month function.

=MONTH(A9)

The output should be 4. As with the day function, a valid date field is required to return a value.

• NOW

As the now function does not have any arguments, it will always return the properly formatted date/time value representing the system date and time when the worksheet was last updated.

=NOW()

In my case, the output was originally ‘2020-04-19’ but it has since been updated, as I have modified other cells.

• TODAY

Similar to the now function, today will return only the system date in the proper format.

=TODAY()

The original output was ‘2020-04-19’ but it has since been updated as other cells have been modified.

• YEAR

To finalise the trilogy of functions to segment a date value, we will again be pulling from any of rows with the 28th as the date. This time returning the year.

=YEAR(A9)

The output should be 2020. As with the day and month functions, a valid date field is required to return a value.