Learn Excel from Bill Jelen with this daily 2 minute video 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.
posted by Bill at 9:00 AM
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.
Labels: Rounding
posted by Bill at 9:00 AM
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.
Labels: ActiveX, forms control, VBA
posted by Bill at 9:00 AM
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.
Labels: Filter
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: formula
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
In this dueling Excel podcast, Bill and Mike look at ways to do a frequency distribution. Episode 1040 shows you how.
Labels: COUNTIFS, formulas, frequency, SUMPRODUCT
posted by Bill at 9:00 AM
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.
Labels: formula
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: charting
posted by Bill at 9:00 AM
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.
Labels: conditional formatting
posted by Bill at 9:00 AM
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.
Labels: formula
posted by Bill at 9:00 AM
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.
posted by Bill at 9:00 AM
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.
Labels: charting
posted by Bill at 9:00 AM
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.
Labels: charting
posted by Bill at 9:00 AM
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.
Labels: array formula, average
posted by Bill at 9:00 AM
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.
Labels: formula
posted by Bill at 9:00 AM
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.
Labels: Pivot Table, VBA
posted by Bill at 9:00 AM
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.
Labels: Macro, Pivot Table, VBA
posted by Bill at 9:00 AM
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.
Labels: charting
posted by Bill at 9:00 AM
