Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Chart a moving average in Excel. Episode 770 will show you how.
Labels: charting, forecasting, moving average, trendline
posted by Bill at 9:00 AM
Episode 769 looks at the difficult variations on yesterday's podcast; how do you set up an IF statement that will only be true if two conditions are true? This episode will look at using OR, AND, NOT within the logical test of the IF function.
posted by Bill at 9:00 AM
How do you deal with many levels in an IF statement? Episode 768 discusses nesting IF statements.
posted by Bill at 9:00 AM
A question asks how to avoid #VALUE errors when a formula refers to a cell with a blank text value. In Episode 767, I will take a look at how these blank cells come to be, how to work around the #VALUE error and even a different approach to prevent the root cause of the problem.
Labels: formulas, IF, ISBLANK, Number Format, Zeroes
posted by Bill at 9:00 AM
It is Memorial Day in the U.S. Since most people are out at a picnic instead of at work today, I tackle an incredibly obscure problem. How can we make sure that Hexadecimal numbers have leading zeroes? Episode 766 will take a look at the HEX2DEC, TEXT, and RIGHT functions.
posted by Bill at 9:00 AM
You might already know these, but I recently was shown a few cool shortcuts for inserting rows, columns, or cells. Episode 765 will show you how.
Labels: shortcuts
posted by Bill at 9:00 AM
The Freeze Panes command allows you to always see certain rows at the top of your worksheet. In Episode 764, I will show you how to use Freeze Panes to always see certain columns as well.
Labels: Freeze Panes
posted by Bill at 9:00 AM
When you use Tools, Protect Worksheet, all of the cells become protected. How can you protect only a portion of the worksheet? In Episode 763, we take a look at this problem and protect only the formulas in a worksheet.
Labels: Protect, Protection
posted by Bill at 9:00 AM
A combination of six cool tricks allow you to take detailed transactional data and produce a chart of the top five customers in about 12 clicks. Episode 762 shows you how.
Labels: Data wrangling, Subtotals, visible cells
posted by Bill at 9:00 AM
In Episode 761, a look at three cool custom icons you can add to your toolbar in Excel 2007 or 2003. See how easy it is to use Select Current Region, Select Visible Cells, and Set Print Area.
Labels: Customizing, Formatting
posted by Bill at 9:00 AM
How can you calculate the MIN or MAX value ignoring one outlier? Or, how can you figure out the MIN or MAX throwing out 5% of the outliers? Episode 760 takes a look at SMALL, LARGE, and PERCENTILE functions.
posted by Bill at 9:00 AM
There is a way to set the default border color that is used when you use the Borders dropdown. Episode 759 shows you how. Also, a look at the new Podcast DVD.
Labels: color, Formatting, gridline
posted by Bill at 9:00 AM
Can you use a pivot table to measure how many unique combinations of customer and product appear in a data set? Well, you can't do it natively, but in Episode 758, we take a look at a formula such as =IF(SUMPRODUCT(($B$2:$B2=B2)*($D$2:$D2=D2))>1,0,1) which will allow the calculation to work. Also - a look at the Amazon Kindle.
Labels: Pivot Table, unique
posted by Bill at 9:00 AM
Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.
Labels: commission, formulas, VLOOKUP
posted by Bill at 9:00 AM
A question from a recent seminar involved calculating how many unused meals occurred during a month. The person had to rewrite several formulas every month depending on the total number of days in the month. In Episode 756, we'll take a look at some changes to allow that formula to work for every month.
posted by Bill at 9:00 AM
You need to sort a detailed data set so that the records for the largest customer are at the top. Episode 755 will show you a quick way to accomplish this task.
Labels: Sorting
posted by Bill at 9:00 AM
Rene from Norway asks how to modify the code from podcast 730 in order to create a new workbook for every department. Episode 754 shows you how.
posted by Bill at 9:00 AM
When Romas sent in the question for podcast 751, I couldn't tell from the screenshot if his data actually was date/time or text that looked like a date/time. In Episode 753, I take a look at how to tell if the cell contains a date or something that looks like a date. You will also see how to use the TEXT, LEFT, MID, DATE functions to convert the text date/time to a real date.
posted by Bill at 9:00 AM
In today's podcast, we take a look at breaking Romas' date/time column into a date column using the INT function. Episode 752 shows you how.
posted by Bill at 9:00 AM
Romas has data with both date and time in column A...but needs to summarize the data by date. In Episode 751, we take a look at using a pivot table to solve this problem.
Labels: dates, Pivot Table
posted by Bill at 9:00 AM
Do you need to transfer custom lists and other settings from one Office installation to another computer? The Save My Settings wizard will make this a snap. Episode 750 will show you how.
Labels: Configuration
posted by Bill at 9:00 AM
Rob has a spreadsheet showing install dates for several batteries. He needs to sum all of the batteries that are overdue for being replaced. This requires a tricky variation of the SUMIF formula. Episode 749 shows you how.
posted by Bill at 9:00 AM
