Learn Excel from Bill Jelen with this daily 2 minute video podcast.

## Tuesday, June 30, 2009

### Rounding Correctly - 1047 - Learn Excel from MrExcel Podcast

After yesterday’s podcast about ASTM E29 rounding, I produce a function in VBA that will correctly do the bankers rounding algorithm in Excel. Episode 1047 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Monday, June 29, 2009

### ASTM E29 Rounding - 1046 - Learn Excel from MrExcel Podcast

If you have to round 0.5, do you think it should round to 0 or 1? I bet that you learned in school that it should round to 1. However, this will introduce errors. Episode 1046 talks about the errors introduced by this rounding.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Friday, June 26, 2009

### Dueling: Percentage Scrollbar - 1045 - Learn Excel from MrExcel Podcast

Izhak asks how to add a scrollbar to a worksheet for selecting a percentage. In this dueling Excel podcast Episode 1045, Bill and Mike will show you two ways to solve the problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Thursday, June 25, 2009

### Multi-Column Find - 1044 - Learn Excel from MrExcel Podcast

Isabel asks how to use Ctrl+F to find a row where three criteria are true. Rather than use Ctrl+F, Episode 1044 suggests using a Filter.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Wednesday, June 24, 2009

### Parsing Variable Length - 1043 - Learn Excel from MrExcel Podcast

Dawn's file has name in A and address in B. Except: the address field in column B contains the name again. Episode 1043 shows how to parse the variable-length name out from column B.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, June 23, 2009

### Indian Pay Rounding - 1042 - Learn Excel from MrExcel

Another question from Gopal. New India Pay Commission guidelines suggests that pay should be rounded to increments of 10. If the amount over the last 10 is less than or equal to 0.99, then round down. Otherwise round up. While Excel’s built-in functions don’t handle this, Episode 1042 shows you how to handle this using three functions in Excel.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Monday, June 22, 2009

### Future IF - 1041 - Learn Excel from MrExcel Podcast

Mario has built a great nested IF formula for assigning points to soccer games. However, the formula is calculating 1 point for both teams for all unplayed games. In Episode 1041, I take a look at how to modify the original formula to calculate nothing until a score has been entered.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Friday, June 19, 2009

### Dueling: Frequency Distribution - 1040 - Learn Excel from MrExcel Podcast

In this dueling Excel podcast, Bill and Mike look at ways to do a frequency distribution. Episode 1040 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , , ,

## Thursday, June 18, 2009

### Round & Round - 1039 - Learn Excel from MrExcel Podcast

Gopal asks how to round a number up to the next whole number. Episode 1039 takes a look at several different functions available for rounding numbers in Excel.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Wednesday, June 17, 2009

### Multiple Scatter - 1038 - Learn Excel from MrExcel Podcast

In Episode 1038 - a better way to create the multiple scatter charts from Episode 1031 and 1032. Thanks to everyone who wrote in with this better way to set up the data.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, June 16, 2009

### 2nd Smallest - 1037 - Learn Excel from MrExcel Podcast

If you need to find the smallest value in a range, you would use MIN. Episode 1037 will show you how to find the second smallest value, third largest value and so on. Also in this episode, sort with a formula.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Monday, June 15, 2009

### Better 1016 - #1036 - Learn Excel from MrExcel Podcast

Tom and Thor send in better ways to force the area chart under a portion of a line chart to drop to zero. Episode 1036 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Friday, June 12, 2009

### Dueling: Up/Down Symbols - 1035 - Learn Excel from MrExcel Podcast

Jonathan asks how to insert an Up/Down arrow as the result of an IF statement. In Dueling Excel Episode 1035, Bill and Mike show you a few ways how to do this.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Thursday, June 11, 2009

### Count Visible Rows - 1034 - Learn Excel from MrExcel Podcast

Isabel asks how to count only the visible entries in a range. =COUNTA() is returning all of the values. Episode 1034 will show you how to count or sum the visible rows in a range.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Wednesday, June 10, 2009

### Fiscal Quarter UDF - 1033 - Learn Excel from MrExcel Podcast

If your fiscal year does not end on December 31, then all of the cool date grouping functions available in pivot tables will not work for you. In Episode 1033, you will see how to add a couple of user-defined functions to your personal macro workbook to simplify the conversion of date to Fiscal Quarter or Fiscal Year.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, June 09, 2009

### Multiple XY II - 1032 - Learn Excel from MrExcel Podcast

Yesterday's podcast showed a tedious method for creating a chart with 5 XY series. Today, Episode 1032 shows a method for streamlining the creation of this chart.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Monday, June 08, 2009

### Multiple XY Series - 1031 - Learn Excel from MrExcel Podcast

Pierre wants to create a chart with multiple XY series on the same chart. While this is possible, it is not extremely easy. Episode 1031 shows you one method for doing this.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Friday, June 05, 2009

### Dueling: Average Non-Zeroes - 1030 - Learn Excel from MrExcel Podcast

Question from YouTube is how to average all the non-zero values in a range. In Episode 1030, Bill and Mike show several methods for solving the problem.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Thursday, June 04, 2009

### British Cattle (Really) - 1029 - Learn Excel from MrExcel Podcast

Yes - a podcast of use to 10,000 British Cattle Farmers but of no use to anyone else. Actually...it is a fun challenge. Play this one to see the problem then see if you can come up with a formula to extend the fine numbering system used for British Cattle! Episode 1029 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels:

## Wednesday, June 03, 2009

### Generalize Recorded Macro - 1028 - Learn Excel from MrExcel Podcast

While yesterday's podcast was created with the macro recorder, you can switch over to the VBA editor to change 4 lines of the macro. This will create a macro that will work without requiring you to rename the pivot table. Episode 1028 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: ,

## Tuesday, June 02, 2009

### Format Pivot Macro - 1027 - Learn Excel from MrExcel Podcast

Cheryl asks how to format all new pivot tables that she creates. The macro recorder can handle this, if you change the name of the pivot table before recording the macro. Episode 1027 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: , ,

## Monday, June 01, 2009

### 1016 Redux - 1026 - Learn Excel from MrExcel Podcast

Mario sends in an UGLY solution to episode 1016. Episode 1026 will show you how to highlight one segment of a line chart in Excel 2003. This episode wins an award for the ugliest workaround.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Labels: