Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Someone put a bunch of mathematical calculations in column A as text and we want to evaluate those formulas. Mike and Bill provide dueling ways to solve this problem in Episode 1149.
posted by Bill at 9:00 AM
Martin from Sweden sends in a question about doing a range VLOOKUP where the lookup table is not sorted. Mike and Bill duel it out in today's Episode 1144.
posted by Bill at 9:00 AM
Rob from Cambridge needs to divide nine cells by nine other cells and sum them up. In Episode 1141, we look at an array formula to solve this problem.
posted by Bill at 9:00 AM
A cool way to streamline a VBA loop using SpecialCells. Episode 1140 shows you how.
Labels: formula, Go To Special, VBA
posted by Bill at 9:00 AM
Rod from Cincinati asks how to solve the problem of trailing spaces in the lookup table. I had never tried it before, but you can apply a TRIM to the entire lookup table. Episode 1138 shows you how.
posted by Bill at 9:00 AM
Nick asks how to do a pretty lengthy bonus calculation. It might be too complex for the Excel 2003 nested IF limit, so I decided to use the range version of VLOOKUP. But...as a twist, I embed the table right in the formula. Episode 1135 shows you how.
posted by Bill at 9:00 AM
Today's Dueling Excel Podcast is a question sent in by Hassan. Hassan wants to enter a name and a through-month and then add up that person's sales from January through the selected month. Mike and Bill show various methods in Episode 1134.
posted by Bill at 9:00 AM
A dueling podcast...how to find the first week of the month. Mike and Bill show you various ways in Episode 1129.
posted by Bill at 9:00 AM
VLOOKUP is my favorite function in Excel. In Episode 1120, we will take a look at the basics of VLOOKUP.
posted by Bill at 9:00 AM
Michael asks why when you add up three cells with 1/3, you don't get 0.99? Episode 1116 takes a look at how to solve this.
posted by Bill at 9:00 AM
You need $273 in cash. How many of each currency amount will you need? This Dueling Episode 1114 shows a few different ways to go.
Labels: formula
posted by Bill at 9:00 AM
Sam from Vienna sends in today's question. In a database of events, how can he add new blank rows to represent every missing day? Episode 1103 shows you one method.
posted by Bill at 9:00 AM
Dak asks how to take a long cell entry and to split it into 20 character entries going down a column without using wrap text. Episode 1102 shows a couple of ways to solve the problem.
Labels: array formula, formula, MID
posted by Bill at 9:00 AM
Shift-Del sends in some alternative formulas to solve the problem from episode 1043. To see uses of SUBSTITUTE and REPLACE in Excel, check out Episode 1097.
Labels: formula, MID, replace, RIGHT, SUBSTITUTE
posted by Bill at 9:00 AM
In today's dueling Excel podcast, the question is how to delete all the records which contain either ski or swim. This requires multiple wildcards in a filter. Mike and Bill will compare various methods in Episode 1090 .
Labels: Advanced Filter, Filter, formula, ISNUMBER, lookup
posted by Bill at 9:00 AM
Mike and Bill offer five different way to solve a simple bonus calculation. Episode 1085 shows you how.
Labels: formula
posted by Bill at 9:00 AM
Jamie from the UK sends in yet another formula to solve the Last Match formula from Episode 1073. Episode 1084 shows you how.
Labels: array formula, CSE, formula
posted by Bill at 9:00 AM
Daniel from Quebec sends in a wild formula to solve the Last Match problem from Episode 1073. We'll look at using Evaluate Formula to study how the LOOKUP value actually works. Episode 1083 shows you how.
posted by Bill at 9:00 AM
Shaun asks how he can link values in one worksheet to a lookup table in an external workbook. Episode 1082 will show you how.
posted by Bill at 9:00 AM
How to use COUNTIF to count survey answers for a subset of rows. Bill and Mike show many methods in Episode 1065.
Labels: COUNTIF, COUNTIFS, formula, Pivot Table
posted by Bill at 9:00 AM
Julian wants to point to the row above the current row. This formula works fine until he deletes a row then those formulas change to #REF! errors. Julian asks how to point to "the row above" without specifying a row number. Episode 1061 shows you how.
posted by Bill at 9:00 AM
In today's dueling Excel podcast, how to do a VLOOKUP left. In Episode 1060 Bill and Mike show differing methods, from INDEX and MATCH to LOOKUP.
posted by Bill at 9:00 AM
Carrie from NYC asks how to set up links between worksheets. Episode 1056 shows you how.
posted by Bill at 9:00 AM
Faizee from Youtube posts a question about looking for certain words in a cell in order to create a category. While this sounds simple, Bill and Mike manage to come up with a number of possible formulae in Episode 1055!
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
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
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
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
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
A dueling Excel podcast where Mike and Bill take a look at how to sort using a formula. While most people would use RANK and COUNTIF, Episode 1025 shows you how to use LARGE and ROW or ROWS.
posted by Bill at 9:00 AM
Figure out the number of billable days between two dates. Episode 1018 looks at ways to count the number of days, number of workdays, or number of Monday-Wednesday-Friday dates between two dates.
posted by Bill at 9:00 AM
Another Dueling Excel podcast. Bill and Mike discuss formulas to grab the last entry in a row. Episode 995 shows you two methods for solving the problem.
Labels: formula
posted by Bill at 9:00 AM
You may have seen how to create a spearing or 3-D formula such as =SUM(Jan:Dec!B3). In Episode 974, an amazing way to create a 3D reference to non-contiguous sheets.
posted by Bill at 9:00 AM
Paul sends in a few tricks to help deal with the Function Tooltip that appears when you edit a cell. Episode 960 shows you how.
Labels: formula
posted by Bill at 9:00 AM
Someone used Alt+Enter to enter three fields in hundreds of cells. How can this be broken into separate columns. In Episode 959, I discuss MID, CODE, CHAR, and SUBSTITUTE functions to solve the problem.
Labels: formula
posted by Bill at 9:00 AM
Rod has a formula in column B that calculates the earliest month in a data set. He then wants formulas in C, D, E, and so on that show the first of the next month. Although you can right-click the fill handle to do this, Episode 942 will show you how to solve this using the DATE function.
posted by Bill at 9:00 AM
Sergio from Mexico asks how to count unique combinations of two fields. Episode 941 shows three methods for solving this problem.
Labels: COUNTIFS, formula, SUMPRODUCT
posted by Bill at 9:00 AM
The day after Christmas and you are at work? Or just checking out the podcast on your new iPod? Let's do a social networking experiment. Plus...how to autonumber rows. And...a podcast special at the MrExcel store. Watch it all in Episode 916.
Labels: formula
posted by Bill at 9:00 AM
Calculate time difference and show the result as a decimal number of hours. Episode 902 will show you how.
Labels: formula
posted by Bill at 9:00 AM
Kim asks about the minus minus in certain formulas. In Episode 899, I will break one of those formulas down and show exactly why people use the minus minus.
Labels: formula, SUMPRODUCT
posted by Bill at 9:00 AM
An alternative to Episode 889...If you need to match two sorted columns with a VLOOKUP, you can make the table array be dynamically calculated with the OFFSET function. Episode 890 shows you how.
posted by Bill at 9:00 AM
You need to do a VLOOKUP that will look up two values from a table. In Episode 889, I will show a method using a concatenated key field to enable VLOOKUP to work.
posted by Bill at 9:00 AM
Robert asks how he can combine an HLOOKUP and a VLOOKUP together into a single formula. This is a common problem, but you don't use either VLOOKUP or HLOOKUP. Instead, Episode 884 will introduce a function called MATCH. This obscure but versatile function is similar to both VLOOKUP and HLOOKUP. Episode 884 shows you how to solve this problem.
posted by Bill at 9:00 AM
Richard is building a formula outside of the pivot table that needs to divide by the grand total of the pivot table. In Episode 869, I show two solutions, one of which is an incredibly geeky use of the other type of VLOOKUP.
Labels: formula
posted by Bill at 9:00 AM
Dan from Texas asks: is there a way to change the color of cells that are precedents of a particular formula. Use the technique in Episode 862 to quickly complete this task.
Labels: formula
posted by Bill at 9:00 AM
Can the VLOOKUP return a value one row below the matched value? Episode 854 shows you how to achieve this result using two other functions.
posted by Bill at 9:00 AM
You love VLOOKUPs, but you hate the #N/A error that results. In our Where is it Wednesday edition of the MrExcel podcast, I will show you two ways to prevent #N/A as the result of your VLOOKUP formulas. One harder method in Excel 2003 and an easier method in Excel 2007. Episode 853 shows you how.
posted by Bill at 9:00 AM
Seiichi from Japan asks how to fill in blank cells in column A with the value above the blank. Episode 847 shows you how.
Labels: formula, Go To Special
posted by Bill at 9:00 AM
Interesting question from the Southeastern Accounting Show: I want to sum from cell A5 and sum down a certain number of cells. However, the number of cells is stored in cell C5!
posted by Bill at 9:00 AM
Tim asked a question about writing a formula that he could copy across a row which would automatically grab successive values from down a column. (Basically doing a Transpose that is always updating). In Episode 835, I will show you my formula method and also discuss Kathy's easier suggestion that will work in some cases.
Labels: formula, Paste Special
posted by Bill at 9:00 AM
Continuing the discussion from Episode 829, I show how to use the array of dates from a start date to an end date in order to perform a calculation on each date in the array. Episode 830 shows you how.
Labels: array formula, formula
posted by Bill at 7:30 AM
The reason that people create data like in podcast 826 is so that they can get totals by section to carry through to another report. However, if that other report used SUMIF, the problem would be solved. Episode 827 shows you how.
posted by Bill at 9:00 AM
Another way to solve yesterday's problem is to use the =SUBSTITUTE function instead of LEFT and LEN. Episode 792 will show you how.
posted by Bill at 9:00 AM
After adding Subtotals, Excel will append the word " Total" to the field being subtotaled. In Episode 791, we take a look at how to use a formula to remove the totals so that the data can be used as a lookup table.
Labels: formula
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
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
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
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
Dick sends in a cool question today. First, he figured an amazing way to grab the first value from a subtotaled group. But, Episode 712 shows three lines of VBA code to finish the process.
Labels: array formula, formula, Macro
posted by Bill at 9:00 AM
A quick trick showing how you can make a formula point to a new range without typing the range. Episode 575 shows you how.
posted by Bill at 9:00 AM
Van writes in with a suggestion about podcast 539. In that podcast, we were trying to calculate which customers had their birthday coming up soon. Van suggests using conditional formatting and the TODAY() function to flag the customers to whom you need to send birthday cards. Episode 551 shows you how.
Labels: conditional formatting, dates, formula, TODAY
posted by Bill at 9:00 AM
posted by Bill at 9:00 AM
Ricardo sends in a tricky formula question for today’s podcast. How can you calculate a moving average of all sales in the last 90 days, particularly when your data does not include one row for every day? In Episode 512, I discuss the really convoluted solution in Excel 2003 and the only slightly convoluted solution in Excel 2007.
Labels: COUNTIF, formula, moving average, SUMIF
posted by Bill at 9:00 AM
Jonathan from California has a column with date & time information, but would like to strip out just the times to do an analysis by hour. Episode 494 shows you two formulas that can be used to analyze the data by hour.
posted by Bill at 9:00 AM
Dick Kusleika is our guest podcaster today. Dick is the host of the Daily Dose of Excel. He shows how to troubleshoot formulas by evaluating parts of a formula right in the formula bar. Episode 474 shows you how.
Labels: formula
posted by Bill at 9:00 AM
One of the best features of the new Excel 2007 table functionality is the new table formulas that Excel can create. Once you enter a formula in the first row of a dataset, Excel 2007 will automatically copy the formula down to all of the rows. Episode 423 shows you the details.
posted by Bill at 9:00 AM
It is a real pain to try to trace precedents that are on other worksheets or in other workbooks. Episode 400 shows some formula auditing tricks, including a trick to see all of the precedents on other worksheets. This is our 300th episode!
Labels: formula, trace precedents, Workbooks, Worksheet
posted by Bill at 9:00 AM
Sometimes when you have a difficult formula like the one in Episode 386, you end up creating the formula in several steps. Once you have the formula working, but spread out over several columns, use the technique in this episode to bring everything back together into one megaformula. Episode 387 shows you how.
Labels: formula
posted by Bill at 9:00 AM
Ken from Chicago calls with a tough question - how can Excel calculate the 2nd Tuesday of November for a series of years. The solution involves a series of obscure Excel functions. Episode 386 shows you how. If you have a question for the Netcast, call 1-866-581-0221 and leave your message on the recording.
Labels: formula
posted by Bill at 9:00 AM
In Excel 2002+, try to create a formula next to your pivot table. When you copy this formula down to other cells, the formula keeps pointing at the original cells! Is this a bug? No, it's a feature! Well, whatever it is, in Episode 377 I will show you how to stop the functionality so that things continue to work like they did back in Excel 2000.
Labels: formula, GetPivotData, Pivot Table
posted by Bill at 9:00 AM
Rather than static text in the AutoShape, use a formula to add dynamic text to an AutoShape. Episode 370 shows you how to create a shape that shows off the total of all sales made today.
Labels: autoshapes, Dynamic, formula, Text
posted by Bill at 9:00 AM
Calculate Sales over Quota using either the IF or MAX functions. Episode 348 shows you how.
posted by Bill at 9:00 AM
