Excel tips and tutorials
If you have a worksheet with 20 scores listed, how can you calculate an average top 10 scores? And if there are only 11 scores, can the formula automatically adjust, to average just the top 4 scores?
Continue reading “Excel Function Friday: Average Top 10 Scores”
When you were first learning how to use Excel, you quickly discovered the basic Excel functions, like SUM, COUNT, MIN, MAX, and AVERAGE. Now you’re ready for advanced calculations, like how to find MIN IF or MAX IF in Excel.
Thanks to an email question from Leslie, I’ve done another variation on the Data Validation Multiple Selection sample.
Leslie is scheduling training courses, and has a table with dates across the top, and course names down the side. One or more of the instructor names can be selected for each training session.
However, Leslie wants to prevent an instructor’s name from being selected twice on the same day. For example, on Date 01, in the screen shot above, Bob Smith and Mary Jones are each booked for 2 courses.
To prevent the names from being selected twice, I changed the code, to include the COUNTIF function.
Here is the revised section of the code, with the COUNTIF function:
With the revised code, if I try to select a trainer who is already booked, I see this message, and the name is not added.
If you’d like to test the Block Duplicates code, you can download the No Duplicates in Multiple Selection Excel Drop Down sample file.
The file is in Excel 2007 format, and is zipped. When you open the file, enable macros, if you want to test the block duplicates feature.
_____________
While working with PowerPivot in Excel 2010, I noticed the Ask Questions command on the Ribbon.
When you click that command, your web browser opens, and takes you to the PowerPivot Help forums on the Microsoft website.
That’s a nice feature, if you’re struggling with PowerPivot.
Why doesn’t Excel have an Ask Questions command? We have questions too! (A moment of silence for the old Excel newsgroups.)
The closest thing that I could find is a Contact Us command, which leads to the Microsoft website.
At the bottom of that page is a link that leads to the general Answers form – not to an Excel related forum. It takes a couple more clicks to get to the Excel section.
Maybe that Ask Questions command will be in the next version of Excel. Until then, here are links to the Excel help forums on the Microsoft site.
Answers – http://answers.microsoft.com/en-us/office/forum/excel
Technet — http://social.technet.microsoft.com/Forums/en/excel/threads
MSDN (Excel Developers) — http://social.msdn.microsoft.com/Forums/en-US/exceldev/threads
MSDN (VBA) — http://social.msdn.microsoft.com/Forums/en-US/isvvba/threads
There are links to other Excel forums in the following article that I wrote:
If your Excel data is in monthly columns, like the worksheet shown below, you’ll have trouble setting up a flexible pivot table. Instead of leaving the data like this, see how to normalize data for Excel pivot table setup.
Recently, I complained about having trouble getting Excel VBA to correctly find the last row with data in a column, when using Excel tables. Thanks for the suggestions on solving that problem!
Jim Cone was intrigued by the find last row challenge, and from previous Excel adventures, Jim knew that Excel tables aren’t the only obstacle to finding the last row.
Jim decided to write a universal LastRow function for a…
For example, the LastRow function shows that row 40 has the last value in column E, even though that row is hidden by a filter.
Covered with dust, and bits of Excel VBA code, Jim has finally emerged from his basement workshop. He sent me his Find Last Row sample workbook, which you can download at the link below.
Here’s what Jim had to say about writing the code:
And Jim added more details:
Thanks Jim!
To see Jim Cone’s Excel VBA code, you can download the Find Last Row in a Column sample workbook. The file is in Excel 2003 format, and zipped. Enable macros when opening the file, if you want to test the code.
Jim has added a few Excel tables to the worksheet, which you’ll see in Excel 2007 and Excel 2010. In Excel 2003, those will appear as lists.
Click the button on the worksheet, to find the last row with data in the current selection.
Or, you can modify the “DoesItWork” sub, so it will find the last row on a worksheet or specific column.
________________
Last week, Seth Godin recommended hiring a geek to help you save an hour a day.
Well, you’re a geek, so you don’t have to hire one! Just pay attention as you run through your morning office routine, and answer this question:
Then, make the change.
Every morning, I follow these steps:
Fortunately, I have a macro that does the first 2 steps. For some reason, I was doing the last step manually. Don’t ask me why.
Excel is a bit slow when opening the Save As dialog box, so that final step was taking 30-60 seconds. Not a huge productivity drain, but why do something manually, if it can be easily automated?
To get rid of that manual step, I added a few lines of code to the existing macro. The Excel VBA code saves a copy of the active workbook, into the Backup folder, and adds the previous day’s date to the file name.
It only took me a couple of minutes to add the code, so the time invested was quickly repaid. Now I just have another 59 minutes to trim!
'------ Sub SaveDailyData() Dim wbData As Workbook Dim strDir As String Dim strName As String Dim strExt As String Set wbData = ActiveWorkbook strDir = "C:\Backups" strName = "DailyData_" & Format(Date - 1, "yyyymmdd") strExt = ".xls" wbData.SaveCopyAs strDir & strName & strExt wbData.Close SaveChanges:=False End Sub
___________
Do you ever use the Excel CONVERT function? Or, do you avoid that function, because you can’t remember all the measurement unit codes?
For example, the formula =CONVERT(10,”klt”,”gal”) will convert 10 kilolitres to 2,641.7205 gallons – if you get those codes right.
You might be able to remember lt and gal, but probably not many of the other codes.
Most of the time when you’re sorting in Excel, you sort based on the values in one or more columns.
If your workbooks are like mine, it’s rare that you sort horizontally, based on the values in a row. But if you need to do that, here’s how to sort a row in Excel.
I’ve done another update to the Data Validation Multiple Selection sample, thanks to a question from Pat, in a blog comment. He has 3 columns with different drop down lists, and wants to add new items to the applicable lists.
