Excel Sorting Gone Wrong
Mar 1, 2016
See how to count unique items, chart tips, and more, in this week's Excel news.
- Debra - ddalgleish @ contextures.com
Visit my Excel website for many more tutorials and videos.
Be careful how you click, when building a formula, or you could end up selling things for the wrong price. To show you what happened, I made a sample order form, and used VLOOKUP to get the product prces. At first, all the data was correct. But then I sorted the list, and suddenly all the prices were wrong. Yikes!
What had caused things to go so horribly wrong? Well, while I was creating the formula, Excel had "helpfully" added unnecessary sheet names to the cell references, and that was causing the problem. Watch this short video to see what went wrong, and how to make the simple fix.
Do you ever use the EOMONTH (End of Month) function? It's perfect for calculating the first or last day of a month. For example, get the first day of next month: =EOMONTH(TODAY(),0)+1
Just for fun, I used EOMONTH to create a list of future Leap Year Days. The starting date is 2/29/2016 in cell B1. In cell B2, the EOMONTH formula refers to that date, and adds 4 years (48 months): =EOMONTH(B1,48)
I copied my formula down a few rows, and formatted the dates to show the weekday. You can see the results in the screen shot below, and there are more EOMONTH examples on my website.
Here are a couple of Excel articles I read recently, that you might find useful.
Unique Count -- Roger Govier explains how to set up a pivot table in Excel 2013 or later, so you can show a Distinct Count. For example, if there were 100 orders, how many distinct (unique) products were sold?. (Level - Intermediate)
Chart Tips -- Sometimes the data needs a bit of help. Ann K. Emery shows four examples of how to use a storytelling approach in a chart. (Level - Intermediate)
Thanks for your well wishes, and I'm feeling much better this week. But, in a strange coincidence, I found a yearbook that my Grade 5 class made (we won't say how long ago). Apparently there was an outbreak of mumps that February, and 11 of my classmates were out sick. Not me though -- I only got sick during the holidays. Sigh.
Two things I noticed -- the purple ink of the ditto machine copy (do you remember that smell?), and the large class size. It was a split grade 4/5, so that teacher had her hands full!
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser:
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Last updated: May 9, 2018 8:58 AM