Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Do you need to determine where you rank in a list? Use the tip in this podcast to rank values in a list. Episode 179 will show you how.
posted by Bill at 9:00 AM
How to add numbers to a list so that the numbers will recalculate when you insert or delete new list members in the middle of the list. This podcast actually turns into a cool discussion of using a combination of mixed and relative references in a single count function. It is a technique that comes up once in a while and is very handy to master. Episode 178 shows you how.
posted by Bill at 9:00 AM
Why does Excel say I have two cells when any seven year old can see it is 9 cells? Because the COUNT function doesn't count like you do. Learn why =COUNT() doesn't always work and the obscure function to work around this problem. Episode 177 will show you how.
posted by Bill at 9:00 AM
Yes - you can use the AutoSum icon to find averages, min, max and counts. Episode 176 will show you how.
posted by Bill at 9:00 AM
Why the AutoSum icon doesn't always work. Learn the amazing trick to have AutoSum work all of the time in the 75th installment of the MrExcel podcast. Episode 175 will show you how.
posted by Bill at 9:00 AM
Use the AutoSum button to quickly total columns of numbers. Episode 174 will show you how.
posted by Bill at 9:00 AM
There are THREE popular ways for entering formulas. Like a midway carnival worker, MrExcel can predict your age based on which method you use most frequently. Watch Episode 173 to learn all three methods for entering formulas. (Plus - I bet that after you see which way MrExcel recommends, you too can guess his age!!!)
posted by Bill at 9:00 AM
Confused when your manager asks you to do some strange calculation in Excel? Use the fx button to easily discover new functions from amongst Excel's 455 built-in functions. Episode 172 will show you how.
posted by Bill at 9:00 AM
Instant help with function arguments: Do you remember the function name but not the order of the arguments? Rather than rely on Excel help, just start typing the function name and then hit a secret keystroke to have Excel guide you in building the function. Episode 171 will show you how.
posted by Bill at 9:00 AM
Want to see calculations of a loan payment if different interest rates, number of months or loan amount changes? Copy formula to several cells to create "what if" scenarios. Episode 170 will show you how.
posted by Bill at 9:00 AM
Buying a cool new car or a house? Use Excel to calculate the loan payment. Episode 169 will show you how to calculate a car loan, plus details on using the function wizard with any function.
posted by Bill at 9:00 AM
Are your numeric entries sorting in the wrong place? Maybe you have a mix of text and numbers.
posted by Bill at 9:00 AM
Not only does the Rand Function create/sort random numbers, but it also creates random letters. Episode 167 will show you how.
posted by Bill at 9:00 AM
Use the random number generator to create dice in Excel. Episode 166 will show you how.
posted by Bill at 9:00 AM
Another great feature of the Rand Function is sorting a list into a random sequence. Tired of choosing alphabetically? Random Sort Order is useful for assigning the sequence for oral book reports in class. Episode 165 will show you how.
posted by Bill at 9:00 AM
Sometimes you need to create random numbers for a sales forecast or simulation. Use Excel’s random number generator. Episode 164 will show you how using the Rand Function.
posted by Bill at 9:00 AM
Have you ever deleted a column, only to find out that all of the formulas in another column change to REF! errors? Sometimes, you enter a formula, but then need to convert the formula from a formula that will calculate to static values (so that you can delete the columns upon which the formula is based). This is possible, using the Values option on the PasteSpecial dialog.
posted by Bill at 9:00 AM
Use the TextToColumns Wizard to eliminate the need for LEFT, MID, FIND. This technique works when your original data is delimited by a character such as a dash or comma. Episode 162 will show you how.
posted by Bill at 9:00 AM
Another twist today - how to get characters that occur after the second dash? You will have to use the optional 3rd parameter in the FIND function. Episode 161 will show you how.
posted by Bill at 9:00 AM
Yesterday's podcast showed how to use LEFT and FIND to get all of the characters before a dash. What if you need all of the characters after the Dash? There are a couple of options. The first option discussed in the episode is to combined MID, FIND, and LEN to get the characters. Alternatively, you can use TRIM and leave off the LEN function. Or, in Excel 2003, just leave off the LEN function. Episode 160 will show you how.
posted by Bill at 9:00 AM
Episode 159 - Get characters up to the Dash
posted by Bill at 9:00 AM
If you need to extract characters from the middle of a product code, use the MID function. Episode 158 will show you how. This episode also discusses the RIGHT function to extract characters from the right side of a text value.
posted by Bill at 8:15 AM
