|
|
If you have a worksheet with 20 scores listed, how can you find the average of the best 10 scores? And if there are only 11 scores, can the formula automatically adjust, to average just the top 4 scores?
Someone emailed me for help with this question, so let's start with the simple problem – top 10 of 20 scores. Then we'll make a flexible solution, for the tougher challenge.
Find the Highest or Lowest Scores
One approach to the problem is to create a table, and find the top 10 scores. In this example, the numbers 1-10 are typed in column E.
In cell F2, the SMALL function finds the first smallest score, in the list of scores:
=SMALL($B$2:$B$21,$E2)
To find the largest scores, the LARGE function is used, with this formula in cell G2:
=LARGE($B$2:$B$21,$E2)
Then, the AVERAGE function calculates the average of those top 10
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 AVERAGE
In the olden days, the hardy Excel pioneers had to type those function names, to create a formula. That's why we look so tired! For example, cell F3 has a formula to find the lowest quantity sold:
=MIN(D2:D8)
To find the highest quantity sold, there is a MAX formula in cell G3:
=MAX(D2:D8)
Things are easier now – you can select the basic functions from the AutoSum drop down. Life is good!
Beyond the Basics with MIN IF
In this example, we want to see the MIN and MAX for a specific product. To make it easy to select a product, I created a drop down list of product names, by using data validation.
While MIN and MAX are useful if you want the overall
Continue reading Finding 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.
Check for Existing Names
To prevent the names from being selected twice, I changed the code, to include the COUNTIF function.
After a name is selected, the active column is checked for that name. If the name is found, a warning message is shown, and the name is not added in the current cell.
Here is the revised section of
Continue reading No Duplicates in Multiple Selection Excel Drop Down
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.
Where Do You Ask Excel Questions?
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.
Ask Excel Questions
Maybe that Ask Questions command will be in the next version of Excel. Until then, here are links
Continue reading Make It Easy to Ask Excel Questions
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 multiple columns with sales amounts, rearrange your data into a single column of amounts. Adjacent columns will show the product name and month.
Rearrange the Data With a Pivot Trick
To change the data layout, you can create a Multiple Consolidation Ranges pivot table, as shown in the video below. Usually that type of pivot table is used for combining data on different sheets, but it has the side benefit of changing horizontal data into a vertical layout.
After you create the pivot table, double-click on the Grand Total cell, to extract the source data, with amounts in a single column.
Then, build a new pivot table, from the normalized data.
Or watch on YouTube: Normalize Data for Excel Pivot Table
Download the Sample
Continue reading Normalize Data for Excel Pivot Table
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, who wrote the Excel Special Sort add-in, was intrigued by the find last row challenge. 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... Worksheet Range (selection) Specific column – in a worksheet or range. List/Table Filtered data 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. How to Find the Last Row 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,
Continue reading Find Last Row With Excel VBA
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: What can you change about your Excel habits, to save an hour a day, or even a few minutes? Then, make the change. Doing Steps Manually Every morning, I follow these steps: download some website statistics to Excel, crunch the numbers, and save the raw data file. 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?
Continue reading Save Time When Saving Excel Files
Do you ever use the Excel CONVERT function? Do you avoid it, 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. Drop Down Lists of Units To make the CONVERT function easy to use, I created an Excel file, with lists of the unit categories, codes and the prefixes. First, you'll select a conversion type, from a drop down list of unit categories. Then, enter a quantity. Next, you can select a prefix, if applicable, from a drop down list. And select the unit type, from the next drop down list. This list only shows the items for the Conversion Type that you selected. The instructions for this technique
Continue reading Excel CONVERT Function Made Easy
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. It is possible though, and you can sort in ascending, descending, or custom sort order. Change the Sort Options To sort by row, click the Options button in the Sort dialog box. Then, in the Sort Options, select Sort Left to Right. In the screen shot below, the total row has been sorted, so the month with the highest total is at the left. Excel 2010 Sorting So much has changed in Excel 2010 and Excel 2007 sorting, that I've finally updated the sorting in Excel page on the Contextures website. You can find the detailed instructions for sorting by row, and other sorting tips.
Continue reading Sort a Row in Excel 2010
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. I can get 3 separate columns to each allow multiple selects from their own lists and to allow write in contents...But what I want ...is to make the selected columns (not all) so that write in selections are written to the lists and the lists are sorted. So, in the new example, on the SameCellAddSort worksheet in the sample workbook, that's what happens. You can do a multiple select in columns C and D, but only a single selection in column B. Add New Items New items can be added to the Names and Numbers columns. If a new name is entered in column C, is will be
Continue reading Excel Drop Down Multi-Select Update
|
|
Recent Comments