Excel Beginners

Intro To Formulas: Step 3 To Mastering Spreadsheets

mix and match to your hearts content

Function Examples - Level 2

Now we will work on some basic formulas which utilise two or more functions to improve their effectiveness. All examples will be using the same data as in the previous section, and all formulas are written using the second row (April 30th - Chocolate Bar - 4 - $14)

In these scenarios we will be looking at how to change the output of a formula based on a subjective value. We will be making use of fixed and variable references.

To see how the formulas react under different circumstances, you can place them in the columns next to the data, and flash fill them down the entire range.

With these formulas you can already begin to see some potential applications for how functions can be used in creative ways to achieve results quickly and accurately. I would always recommend trying to create formulas that can help solve your problems. This behaviour promotes creative thinking alongside logic and puzzle solving skills, whose benefits extend outside of the realm of Excel.

  • IF and AVERAGE

If the revenue for a row is less than the average revenue of the data, display the product being purchased, otherwise display the quantity purchased.

=IF($D2<AVERAGE($D$2:$D$18),$B2,$C2)

The output should be the number 4, as the value of $14 is greater than the average.

  • AND and MAX

In this scenario we want to know if a row has both the highest quantity of units sold, as well as the highest revenue.

=AND($C2=MAX($C$2:$C$18),$D2=MAX($D$2:$D$18))

The output should be FALSE, as while four is the largest quantity of units on any transaction, the value of $14 is not the largest of all the revenue values.

  • DAY and LEFT

Here we are returning to try to find a unique way to analyze our sales information. We will be taking a page out of the examples we used with left/right/mid previously. To create custom fields, we are going to take the day of the month followed by a single dash and then the first three characters of the product name to create our identifier.

=DAY($A2)&”-“&LEFT($B2,3)

The output should be ’30-Cho’, and we can see that it creates a unique text for each row this way.