MrExcel Excel Videos

How to calculate a Golf Handicap in Excel You have your recent golf scores in Excel The handicap is the average of the lowest 10 scores in the last 20 rounds Getting the last 20 is easy thanks to Excel Getting the smallest using MIN But… to get the 2nd smallest through 10th smallest, use SMALL() function. Slow but understandable: =SMALL(,1)+SMALL(,2)+SMALL(,3)…+SMALL(,10) divided by 10 Easier to enter: Array constant {1;2;3;4;5;6;7;8;9;10} as the second argument of SMALL This will return all 10 values at once Send that in to the AVERGE function: =AVERAGE(SMALL(A1:A20,{1;2;3;4;5;6;7;8;9;10})) My fast way for typing the array constant: Type a 1 in a cell. Ctrl+Drag the fill handle from the 1 down to get 2 through 10. Point a formula at...

Question from Jon: Do a SUMIFS that only adds the visible cells Bill's first try: Pass an array into the AGGREGATE function - but this fails Mike's awesome solution: SUBTOTAL or AGGREGATE can not accept an array But you can use OFFSET to process an array and send the results to SUBTOTAL. Use SUMPRODUCT to figure out if the row is YES or not. Bill's second try is like checking to see if the refrigerator light goes out when the door closes: Add a helper column with AGGREGATE of a single cell in each row Point the SUMIFS at that column

You need to copy formulas but not screw up the formatting in cells My Method: Ctrl+C to copy, Paste Special, Formulas, OK Adam in Raleigh: Right-drag the formulas, choose Fill without Formatting

A question from a seminar in Raleigh: Need to fill the first and 15th of each month Or, need to fill the 15th and the last of each month Enter the first 2 dates. Select both dates. Right-drag the fill handle When you finish dragging, choose Fill Months

If we make a lump-sum payment against our mortgage, how will the number of payments change? First step: plug in current values from your mortage statement. Then: Use NPER function to figure out how many payments are left Use a one-variable Data Table to see the impact on future payments. Using the DATE function with a number of months Using DateDIF function to figure out the number of months between two dates

Steve has to sum numbers that have been entered in a text column There are multiple lines in each cell, separated by Alt Enter Need to split those lines to rows, then parse the dollar amount from the middle of each cell Summarize by Cost Center Build a lookup table Get totals from the lookup table, using IFNA to ignore the errors in the blank row Bonus: Add an Event macro to update the worksheet when they change a cell.

Check a column to see if there are any digits in the code Bill Method 1: Flash Fill Mike Method: Use the SUBSTITUTE function with an Array Constant. You will not have to use Ctrl+Shift+Enter because it is an array constant This will remove one digit at a time Use the AND function to see if every item in the resultant array is equal to the original item Bill Method 3: Use a VBA Function to check for digits

Janet wants to insert two rows below each customer's records How to insert multiple rows in one command in Excel Use an IF OR formula to figure out if this is two rows below. Use "A" or 1 Go To Special to select formulas that result in numeric Issue one Insert Row command

How can two people edit the same Excel workbook at the same time? This feature has been introduced in 2017 and is available to anyone using Office 365, Excel online, or Excel on the iPad. In order to co-author, the workbook has to be stored on the OneDrive or SharePoint. For regular features, like entering data and formulas, the co-authoring experience should be great. A colored box shows which cells are being updated. These boxes update quickly. Changes to other endpoints are shown in a few seconds. There are ways to break it - using Pens on the Draw tab of the Ribbon is one way. Excel might ask you to refresh the workbook on each endpoint if you hit one of these errors. The message telling you that the workbook could not be uploaded...