MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.
Learn Excel - Golf Handicap - Podcast 2165
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...
Dueling Excel - SUMIF Visible Cells - Duel 187
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
Learn Excel - Windfall to Pre-Pay Mortgage?  - Podcast2161
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
Learn Excel - Sum Data Alt-Entered - Podcast 2160
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.
Dueling Excel - Find Any Digits - Duel 186
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
Learn Excel - Multiple People Editing Workbook - Podcast 2157
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...

Forum statistics

Threads
1,226,729
Messages
6,192,696
Members
453,747
Latest member
tylerhyatt04

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top