MrExcel Excel Videos

Rob asks for tips on entering Nested IF functions in Excel. Today's episode shows how to use the Function Arguments dialog box to assist. Check out this link to a video that shows how to use the Name box for the second IF:

Haizen has to arrange transport for N employees where N can be 1 to 500. There are three types of vehicles available with different capacity and different cost. He wants a formula for the lowest cost combination of vehicles for any number N passengers. This long episode walks through exploring a brute-force method, then moving to Solver, then using VBA with Solver to solve for all 476 non-trivial cases. Since the video is fairly long, jump to any part of the video: Table of Contents (0:00) Welcome to Use Solver with VBA in Excel (0:15) Defining the Problem - Transport N employees using lowest cost combination of cars, vans, and bus (0:50) Filling numbers from 1 to 500 using Fill Series Dialog (1:38) Using formulas to figure out total...

It was the Saturday before Thanksgiving in 1998 when I opened Notepad and launched the first tip of the week at MrExcel.com. Using the Wayback Machine, see how the tip for converting daily dates to months holds up 15 years later.

Seckin set up a worksheet for his co-workers with great conditional formatting. Unfortunately, the co-worker uses Ctrl+V instead of Paste Special Values, which destroys the conditional formatting and/or data validation. In this episode, record a simple macro that changes Ctrl+V to Paste Special Values.

Back to the Microsoft Ad: can you select a cell and not have the value appear in the formula bar? Yes. Also, a Microsoft PR guy claims the ad is *correct*, that they have not yet pressed Enter, hence the total hasn't updated. But you will see, frame-by-frame, that the chart updates as quickly as the AutoSum. Bottom line: It is a bad formula. Or Photoshop. Never do math with Photoshop! Links in the video: David Yanofsky http://qz.com/143541/microsoft-botched-the-excel-spreadsheet-in-its-surface-tablet-ad/#143541/microsoft-botched-the-excel-spreadsheet-in-its-surface-tablet-ad/ Brad Sams Alex Wilhelm at TechCrunch http://techcrunch.com/2013/11/11/microsoft-didnt-get-its-math-wrong-in-that-excel-billboard/ David Shaw Twitter...

Mark G has an AutoFilter outside the print range and wants to show what is selected in the AutoFilter inside the print range. Excel 2013 Table Slicers could do this, but would take up a lot of space. Episode 1827 shows the VBA Macro used to return items selected from the AutoFilter to cells in Excel.

Two similar questions: (1) Can I count how many rows contain certain text in any column? (2) Can you filter to only rows that have the selected text in any of 5 columns. Similar solutions, although the second requires a bit of VBA macro code.

An addendum to episode 1825 - several easier ways to add a line at the Mean of a bell curve: Drawing object, Error Bar, High-Low Line, Dropline.

Several comments on episode 1663 asked how to add a mean to the bell curve chart. This podcast shows how to create a 2x2 range to represent a single XY line and how to Paste Special that series to the chart. To watch the original video that shows how to set up the formulas for the bell curve, watch episode 1663: