Learn Excel from Bill Jelen with this daily 2 minute video podcast.
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
