Hi fellow Excel Addict,
If you missed Tuesday's 'Excel in Seconds' newsletter, in it I explained 'What the "Cannot Shift Objects Off Sheet" Message Means' and how to avoid it. You can read all about it here.
Today I am going to show you how to "Sort Numbers Regardless of Their Signs". I hope you find this tip helpful.
Please feel free to share my newsletter with anyone you think could use a little Excel help.
Take care and keep on Excelling,
Francis Hayes (The Excel Addict)
If you missed my last newsletter, you can click here to view it online.
If you have a favourite quote, send it to me and I may post it in my newsletter.
Sort Numbers Regardless of Their Signs
In my former career working in Finance, I found the following technique helpful, so I'm hoping that you may also.
Whenever you sort, in descending order, a list of numbers that includes both positive and negative amounts, the largest positive numbers go to the top and the largest negative numbers go to the bottom. On a short list, this may not be a problem, but on a long list, your largest positive and largest negative numbers will be far apart and difficult to compare.
For example, if you are working with variances in a report, you may want to see all large variances grouped together, whether they are positive or negative. Also, if you work with bank or other reconciliations where you need to match up offsetting debits and credits, this technique may be able to help. Or, maybe you have other situations where you need to sort numbers but ignore their signs.
Using Excel's ABS() function to sort numbers by absolute value (i.e. the number without its sign) can help you accomplish these tasks.
The syntax of the Absolute function is =ABS(number). Number is the number or a reference to a number for which you want the absolute value.
1) In a blank cell, in the column immediately to the right of your data (or you can insert a new column), enter the formula =ABS(E5) where E5 is the first cell in your variance column;
3) Copy that formula down the column to the bottom of your list;
4) Now sort your data in descending order based on this column.
You will now have your data sorted based on their 'absolute values' showing the largest values (positive or negative) at the top of your list.
For a bank reconciliation, this means offsetting debits and credits will be grouped together making it easier to match them up.
|"Spreadsheets Tips From An
Excel Addict" is a weekly publication of TheExcelAddict.com.
Copyright Francis J. Hayes All Rights Reserved.
8 Lexington Place, Conception Bay South, Newfoundland, Canada, A1X 6A2 Phone: 709-834-4630