Look Who it Was Random video

Excel - Formula to Return the Most Recent Change for each Product - Episode 455

882 views · Published 4 June 2009 · 3:00 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial: Formula to Return the Most Recent Change for each Product

Welcome back to the MrExcel netcast where we answer your Excel questions and provide helpful tips and tricks. I'm Bill Jelen and today we have a question from Joe who reached out to us via email. You can also phone in your question or send it through Skype, the number is on the screen. Joe's question is about a common issue many Excel users face - having to constantly change formulas as new data is added. In this case, Joe has a list of spot prices for different products over five days and needs to update the formula for the recent change every time a new spot price is added.

This can be a tedious and error-prone task, especially if there are multiple products and the data is not evenly spaced. One small mistake can lead to incorrect results. To avoid this, we will be using the OFFSET function. This function allows us to dynamically reference cells based on a starting point and a specified number of rows and columns to move.

Let's take a closer look at how the OFFSET function works. We start by specifying the starting point, in this case, cell A2. Then, we specify the number of rows and columns to move. In this example, we want to move down 0 rows and over the number of columns that have data in B2 through F2. To determine this number, we use the COUNT function, which counts the number of values in a range. This way, our formula will always adjust to the number of columns with data, even if it changes. We then subtract another OFFSET function, this time starting at A2 and moving over one less column. This will give us the previous spot price, which we can then subtract from the current spot price to get the recent change.

The beauty of this formula is that it will automatically update as new data is added. So, as Joe fills in the data for Thursday, the formula will adjust to show the recent change between the new spot price and the previous one. This saves time and reduces the risk of errors. Thank you, Joe, for sending in your question. If you have a question, don't hesitate to reach out and we may feature it on a future podcast. Don't forget to like and subscribe for more Excel tips and tricks.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Joe writes in with a question of how to subtract the most second most recent price in the row from the most recent price. The solution involves the relatively obscure OFFSET function. Episode 455 shows you how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Most recent change per product
(00:29) Using the offset function
(00:42) Potential problems with manual formula changes
(00:55) Solution using the offset function
(01:05) Explanation of the offset function
(01:20) Utilizing the COUNT function
(01:44) Demonstrating the results
(02:13) Automatic updates with more data
(02:35) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
$35 value in D
COUNT function
Filling in data
Formula change
Future podcast
Joe's question
Offset function
Spot prices
Two cent change
Updating formula

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152440/

More from this channel