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:
| Month | Stock Price |
|---|---|
| January | 150 |
| February | 155 |
| March | 160 |
| April | 148 |
| May | 165 |
| June | 170 |
| July | 175 |
| August | 172 |
| September | 168 |
| October | 180 |
| November | 185 |
| December | 190 |
Exercise
Input the stock prices for a different year in the table. Use the following functions to complete the summary row:
- Use the
MAXfunction to find the highest stock price. - Use the
MINfunction to find the lowest stock price. - Use the
AVERAGEfunction to calculate the average stock price. - Use a combination of the
COUNTIFfunction 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)