Excel MODE Function

Mode denotes the most frequently occurring value in a given set of values. Compared to the other values in the set, it has the highest probability of occurrence. In financial modeling, the MODE function is most useful when they are a lot of repeated values. Take for example, for a retail store you might want to calculate the mode of the number of units sold to determine the most frequently sold quantity. This information can then be used to make decisions about inventory levels and product offering.

In FP&A, Corporate Finance, Investment Banking and others, you would be preparing a lot of forecast models and the MODE function can help you select the best indicator of performance as discussed above. In this post, we would run you through all there is to know about the MODE Function and how you can implement in financial modeling. First, let’s take a look at the syntax and argument of the function.

Syntax & Argument

The syntax for the mode function is as follows:

=MODE(number1, [number2], …)

The mode function needs only one argument, and we would see why below:

  • number1: This could refer to a cell or a range of cells containing numerical values. The latter is specifically the reason why one argument is sufficient for this function. If you select a range as number1 (take for example B3:B10), and at least 2 of the cells within that range have the same number value, then the MODE function would work fine. If on the other hand you select a range and there is no number value occurring more than once, then the MODE function would return a #N/A error.
  • number2 (optional): Number or cell reference that refers to numerical values.

Usage Notes

The following are things to note when using the MODE Function:

  • The MODE Function can process both hardcoded numbers and cells or range of cells containing numerical values.
  • If the cells or range selected do not contain any duplicate numbers, the MODE Function returns a #N/A error.
  • The MODE function ignores cells that do not hold a numerical value. This means that empty cells and cells containing text values and logical values TRUE and FALSE are ignored.
  • Maximum number of arguments accepted are 255 arguments.

Now let’s see some examples of the MODE Function in our next section.

Excel MODE Function Examples

The MODE Function can be used with only hardcoded numbers inputted within the formula

=MODE(1,3,5,1,2,5,7,2,2,9) // Returns 2
=MODE(3,5,7,1,4,7) // Returns 7

Now let us put the numbers above in some cells. As already stated in the syntax & argument section, we can refer to a range of cells as our number1 argument:

=MODE(B3:K3) // Returns 2
=MODE(B4:K4) // Returns 7
MODE Function 1

The Mode function returns a #N/A error if there are no duplicates found in the range

=MODE(1,3,5,7,9) // Returns #N/A
=MODE(2,4,6,8) // Returns #N/A

Integrating the MODE Function in Financial Modeling

In the introduction of this post, we discussed how this function could be used for inventory planning and management. Now, let’s treat it in detail in this section. We would be using the MODE function to forecast our reorder quantity for 3 products. What is reorder quantity you may ask? Well, for the non-accounting readers, reorder quantity is the level of inventory that should trigger an action for a firm’s stock to be reordered. This means that it should be the minimum amount of an item which a firm holds in stock and when stock falls to this level, the item must be reordered to avoid stockouts.

In our example, we have 3 products with disparate range of volumes sold, and we would be using the MODE Function to determine the most frequently sold quantity and forecast what our reorder quantity should be.

MODE Function 2

To the right of the image above, you would see an Average lead time row, this represents the number of days between placing an order of stock and receiving the goods. With that being said, the reorder quantity is calculated by multiplying our Forecast daily usage and Average lead time.

For our average daily usage, we chose to select the most frequently sold quantity. This is because using an alternative like an average may not give a true picture, as outliers tend to skew results. To complete this task, we would input the MODE Function in our Fcst Daily Usage row. Let’s see the formula to calculate Fcst Daily Usage for product 1 below:

=MODE(C4:C33)
MODE Function 3

Now we only need to copy and paste the formula to the adjacent cells for product 2 and product 3 and we are done.

MODE Function 4

Download Excel File

Leave a Comment