Blog

Determine the highest and lowest stock prices over a year

Monitoring stock prices can be a daunting task, but with the right tools and functions, it becomes significantly easier. This article explains how to determine the highest and lowest stock prices over a year using Excel functions such as MIN, MAX, AVERAGE, and a combination of COUNTIF with MAX and MIN. Below, you'll find an example table and the embedded sheet design to help you automate this task.

Example

Here's an example table showing monthly stock prices for a particular company:

Exercise

Input the stock prices for a different year in the table. Use the following functions to complete the summary row:

• Use the MAX function to find the highest stock price.
• Use the MIN function to find the lowest stock price.
• Use the AVERAGE function to calculate the average stock price.
• Use a combination of the COUNTIF function to count how many months had a stock price above the average.

Solution

When you input the stock prices into the Excel sheet, the formulas in the "Summary" row will automatically calculate the following:

The MAX function in cell C14 will identify the maximum value from the range B2, representing the highest stock price.

=MAX(B2:B13)

The MIN function in cell D14 will determine the minimum value from the same range, representing the lowest stock price.

=MIN(B2:B13)

The AVERAGE function in the cell E14 will calculate the average of the stock prices in the range B2.

=AVERAGE(B2:B13)

The COUNTIF function combined with a condition (">" & B17), will count how many months had a stock price above the average.

=COUNTIF(B2:B13, ">" & B17)