A question from my Columbus Indiana virtual seminar: How can we sort a data set by Absolute Value? They have columns with Forecast, Actual, and Variance. A large variance is bad, whether it is positive or negative. My solution today involves using a Dynamic Array and the SORTBY function. Also: using a helper column for people who don't have dynamic arrays.
Transcript of the video:
Learn Excel from MrExcel Excel Podcast episode 2404.
Sort variances by their absolute value.
Welcome back to MrExcel netcast.
I am Bill Jelen. A huge shout-out to the folks up in Columbus Indiana who had me back for not a live seminar but a virtual seminar.
You know, like over Zoom or something like that.
It was awesome.
A couple of questions that came in that haven't been on the podcast before.
Someone had a data set and they wanted to be able to sort that data set by absolute value.
And at first I didn't understand why they wanted to do that.
But they explained that they had a forecast.
And then the actuals that came in.
And they want to find the biggest variance, whether it was a swing up or down.
So, for example, they forecasted 1488.
Manufacturing bought all the material.
We only sold 17.
So now we have 1381 extra sets of material and that's bad.
But also equally bad is they said we were not going to sell any of these and then all of a sudden, (the sales rep must have been sandbagging), they got an order for 1300 and then had to go buy material off the grey market and pay double or triple or something like that.
So we want both of those to sort to the top.
And of course, if you're back in an old version of Excel, you could do this by adding a column with the absolute value.
But in Microsoft 365 we can use this great new function called SORTBY.
It is not going to bring the headings over.
So we choose our data, comma, and then we're going to sort by, well, normally I would specify column D, but instead I'm going to specify the absolute value of column D.
And then what order?
Negative one for Descending so the largest variances are at the top.
And Bam!
There we go.
Negative 1381 and 1341, the largest absolute values sort to the top.
Now, just in case you don't have the latest version of Excel and unfortunately the solution here is going to be to add a temporary column with =ABS() of that cell to the left of us.
Double-click to copy that down and then sort by that column with Data, Z to A and that will work as well.
Check out my new book MrExcel 2021, Unmasking Excel.
Click that I in the top right-hand corner for more information about that.
If you like these videos please, down below, Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Again, thanks for the folks in Columbus, Indiana for inviting me up for a virtual seminar with them.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
Sort variances by their absolute value.
Welcome back to MrExcel netcast.
I am Bill Jelen. A huge shout-out to the folks up in Columbus Indiana who had me back for not a live seminar but a virtual seminar.
You know, like over Zoom or something like that.
It was awesome.
A couple of questions that came in that haven't been on the podcast before.
Someone had a data set and they wanted to be able to sort that data set by absolute value.
And at first I didn't understand why they wanted to do that.
But they explained that they had a forecast.
And then the actuals that came in.
And they want to find the biggest variance, whether it was a swing up or down.
So, for example, they forecasted 1488.
Manufacturing bought all the material.
We only sold 17.
So now we have 1381 extra sets of material and that's bad.
But also equally bad is they said we were not going to sell any of these and then all of a sudden, (the sales rep must have been sandbagging), they got an order for 1300 and then had to go buy material off the grey market and pay double or triple or something like that.
So we want both of those to sort to the top.
And of course, if you're back in an old version of Excel, you could do this by adding a column with the absolute value.
But in Microsoft 365 we can use this great new function called SORTBY.
It is not going to bring the headings over.
So we choose our data, comma, and then we're going to sort by, well, normally I would specify column D, but instead I'm going to specify the absolute value of column D.
And then what order?
Negative one for Descending so the largest variances are at the top.
And Bam!
There we go.
Negative 1381 and 1341, the largest absolute values sort to the top.
Now, just in case you don't have the latest version of Excel and unfortunately the solution here is going to be to add a temporary column with =ABS() of that cell to the left of us.
Double-click to copy that down and then sort by that column with Data, Z to A and that will work as well.
Check out my new book MrExcel 2021, Unmasking Excel.
Click that I in the top right-hand corner for more information about that.
If you like these videos please, down below, Like, Subscribe and Ring that Bell.
Feel free to post any questions or comments down in the YouTube comments below.
Again, thanks for the folks in Columbus, Indiana for inviting me up for a virtual seminar with them.
And thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
