Learn Excel from Bill Jelen with this daily 2 minute video podcast.
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
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
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
Lucy sends in a question. She has built a table of sales by rep by day. She used the MAX and MIN function to find the largest and smallest value. However, her boss then wants her to identify who had the largest and smallest value. This requires the use of INDEX, MATCH, and a couple of hidden columns. Episode 747 will show you how.
posted by Bill at 9:00 AM
Jon has a follow-up question about his baseball spreadsheet. After calculating the win and loss streaks, he would like to find the longest streak, including the start and end dates. This seemingly simple question requires two completely useless functions. Once these functions are used in conjunction, they become very powerful. Episode 736 shows you how.
posted by Bill at 9:00 AM
Jon asks how to calculate a winning or losing streak from baseball data. Episode 735 will show you how to build the formulas to calculate a win streak. Also, how to build a column showing Home or Away based on finding an @ sign in the opponent column.
posted by Bill at 9:00 AM
Bill has a worksheet where he's building text references and then hopes to evaluate the resulting SUM function. Instead, I suggest using INDIRECT to add up the three individual cells. Episode 727 shows you how.
Labels: Concatenation, formulas
posted by Bill at 9:00 AM
Today an idea from Tim in NH. While I frequently use RANDBETWEEN() to do a door prize drawing, Tim adds some bells and whistles to create a nice spreadsheet used to award the prize. Episode 726 shows you how.
Labels: Door prizes, formulas, Random
posted by Bill at 9:00 AM
March 14 (3-14) is Pi day and we will take a look at some calculations with the PI and SQRTPI functions in Episode 715.
posted by Bill at 9:00 AM
Continuing the process started in podcast 702, I will take a look at how to fill in all the blanks in the "outline view" of Column B. Episode 703 will show you how.
posted by Bill at 9:00 AM
In today's podcast, we start with a data set where column A contains both Region and Model information. In Episode 702, I'll use formulas to split that data into two columns.
posted by Bill at 9:00 AM
Today we revisit Episode 672. Rather than using the difficult syntax from that episode, Paul from Darby suggests using a named range. Episode 675 shows you how.
Labels: formulas, Named Ranges
posted by Bill at 9:00 AM
How can you do a VLOOKUP from one worksheet to another worksheet? It is simple to do, but hard to remember the syntax. In Episode 672, I'll show you the easy way to enter these formulas.
Labels: formulas
posted by Bill at 9:00 AM
