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!
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!
Transcript of the video:
Welcome back to the MrExcel netcast. I'm Bill Jelen.
Have a question today from Joe. Joe Sends us question in the email.
You're welcome to phone your question in Via voicemail or skype or just drop me an email and the numbers on the screen.
Joe's question is pretty interesting he has a situation where he has several lines and the spot prices for those lines for each of five days and as he gets a new spot price for example, as he fills in the data for thursday, he has to go through and change this formula showing the recent change.
Now I'm sure in Joe's data he probably has more products than this and they're probably not evenly spaced and you can imagine it's a relative hassel to go through and change that formula instead of D2 - C2 to now point to E2 - D2.
one day you are going to forget.
so we're going to try to report the wrong change so the interesting solution to this uses a function called the offset function.
On the offset function. Let's build one from scratch here the offset function says start from this particular spot, so in this case I'm going to start from the first column A2 and it says to go down a number of rows.
well I don't want to go up or down any rows, so I'm going to put a 0 for the number of rows.
but I do want to move over a certain number of columns.
In this case the number of columns happens to be the number of columns that have data in B2 through F2, so there's a great function for that The COUNT function I'm going to say count the number of values that I have today In B2 through F2. In this case that's going to be 3 so this function is going to say start at A2, move down 0 rows and move right three rows.
Let's stop there and see what we get.
you'll see that we get the $35 That's the most recent value in D.
Now from that i want to subtract another offset function.
This time I want to start at A2 again go 0 rows down and do the exact same count B2 to F2.
but subtract one from it so this time instead of moving over three columns.
I want to move over two columns And you'll see that from a $35 to a $33 is a two cent change.
I'll copy that formula down and the beautiful thing about this formula now as I get more data for example if I fill in the thursday data, formula automatically update.
Now subtracting the $35 in D from the $39 in E, as I continue to fill in more data going down the page, the formula will automatically update to show the new results.
Pretty select using the offset function.
Joe thanks for sending in your question.
If you have a question please feel free to drop me a note and we'll get you on a future podcast.
Have a question today from Joe. Joe Sends us question in the email.
You're welcome to phone your question in Via voicemail or skype or just drop me an email and the numbers on the screen.
Joe's question is pretty interesting he has a situation where he has several lines and the spot prices for those lines for each of five days and as he gets a new spot price for example, as he fills in the data for thursday, he has to go through and change this formula showing the recent change.
Now I'm sure in Joe's data he probably has more products than this and they're probably not evenly spaced and you can imagine it's a relative hassel to go through and change that formula instead of D2 - C2 to now point to E2 - D2.
one day you are going to forget.
so we're going to try to report the wrong change so the interesting solution to this uses a function called the offset function.
On the offset function. Let's build one from scratch here the offset function says start from this particular spot, so in this case I'm going to start from the first column A2 and it says to go down a number of rows.
well I don't want to go up or down any rows, so I'm going to put a 0 for the number of rows.
but I do want to move over a certain number of columns.
In this case the number of columns happens to be the number of columns that have data in B2 through F2, so there's a great function for that The COUNT function I'm going to say count the number of values that I have today In B2 through F2. In this case that's going to be 3 so this function is going to say start at A2, move down 0 rows and move right three rows.
Let's stop there and see what we get.
you'll see that we get the $35 That's the most recent value in D.
Now from that i want to subtract another offset function.
This time I want to start at A2 again go 0 rows down and do the exact same count B2 to F2.
but subtract one from it so this time instead of moving over three columns.
I want to move over two columns And you'll see that from a $35 to a $33 is a two cent change.
I'll copy that formula down and the beautiful thing about this formula now as I get more data for example if I fill in the thursday data, formula automatically update.
Now subtracting the $35 in D from the $39 in E, as I continue to fill in more data going down the page, the formula will automatically update to show the new results.
Pretty select using the offset function.
Joe thanks for sending in your question.
If you have a question please feel free to drop me a note and we'll get you on a future podcast.
