ADM Calculation - 1133 - Learn Excel from MrExcel Podcast

Patricia called the other day with a problem which will be familiar to everyone who works in a public school district. The state pays the school for every student who is enrolled on the magic date of October 1. Given a start date and end date, how can you tell if the date is in that range? Episode 1133 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Duel: Rejoin WordWrapped Cells - 1119 - Learn Excel MrExcel Podcast

External data pasted into Excel shows up with 1 row in columns A & C, but multiple rows in B. Today's dueling Excel Episode 1119 shows how to deal with this data.


This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

WIIW: Natural Language Formulas - 1117 - Learn Excel from MrExcel Podcast

Michael wonders what happened to the old Excel 2003 Natural Language Formulas feature. It is out of Excel 2007, but replaced with something similar. Episode 1117 shows you now.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

#VALUE in Totals - 1112 - Learn Excel from MrExcel Podcast

David asks why his total formulas are getting #VALUE errors. Episode 1112 shows this common problem and a solution.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Dueling : CountIf Range - 1100 - Learn Excel from MrExcel Podcast

DRG asks how to use COUNTIF to find records that match a range. Unfortunately, it can not be done with COUNTIF. In Episode 1100, Bill and Mike compare six solutions.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

First:Last! - 1099 - Learn Excel from MrExcel Podcast

Pablo sends in a cool tip for using Spearing Formulas. Episode 1099 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Tracing Error - 1049 - Learn Excel from MrExcel Podcast

You have a large formula which is generating an error. To find which reference in the formula is causing the problem, use the Evaluate Formula tool to step through the formula. Episode 1049 will show you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Dueling: Frequency Distribution - 1040 - Learn Excel from MrExcel Podcast

In this dueling Excel podcast, Bill and Mike look at ways to do a frequency distribution. Episode 1040 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Dueling: Invert a Range - 1015 - Learn Excel from MrExcel Podcast

In today's dueling podcast, how to turn a range upside down. Episode #1015 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

MrExcel's Learn Excel #966 - Reduce by 35%

Another dual podcast as Bill Jelen and Mike Gel Girvin show you multiple ways to reduce a range by 35%. Shot in Seattle, Washington. Episode 966 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

MrExcel's Learn Excel #965 - Two-Way Lookup

It is a game of Dueling Excel as Bill Jelen and Mike Gel Girvin bring you their own ways for doing two-way VLOOKUPS. Episode 965 shows you how.

This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 900 - SUMPRODUCT SUMIFS

Continuing yesterday's discussion about minus minus, we'll take a look at using SUMPRODUCT vs the new Excel 2007 SUMIFS to solve the multiple conditional sum problem. Episode 900 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 867 - Fill Across Sheets

If you need to make an exact copy of formulas, you can use the method from episode 866 or this Fill Across Worksheets solution. Episode 867 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 866 - Exact Formula Copy

Markus from Germany sends in this tip. Say that you have to make an exact copy of a range of formulas. It is like you need to do a CUT and PASTE, but also leave the original cells where they were. Normally, a Copy and Paste will rewrite the formulas. Using this trick from Markus, you can achieve the desired result. Episode 866 shows you how.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 829 - Coercing Dates

Column A contains a start date. Column B contains an end date. You need to calculate how many days occur in each month of the program. In Episode 829, we learn how to coerce an array of dates from those two cells.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 769 - IF AND OR NOT

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 767 - #VALUE Errors

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 757 - Sliding Commission

Michael asks how to calculate a sliding commission rate. Episode 757 shows two approaches, both of which use the sorted version of VLOOKUP.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 756 - Meal Rollover

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 747 - Who is Largest

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 736 - Index Match

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 735 - Win Streak

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 727 - Building Formulas with Text

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 726 - Random Drawing

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 715 - PI Day

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 703 - Filling Outline Blanks

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 702 - 2-Field Column

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 675 - Named Ranges

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 672 - OffSheet References

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.

This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!

Episode 518 - Copy Across

Richard sends in todays question: How do you copy a formula horizontally when it’s reading from a vertical list? Episode 518 shows the easy, but unintuitive solution.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Episode 465 - Formulating the Future

As the NCAA games start today, the commissioner needs to track how many games you’ve won, but also to see in the future to see how many games you are guaranteed to lose in future rounds. Nothing is worse (for you) than having a Cinderella team knock out your winner in the whole tournament. The Excel-based NCAA bracket takes a look at not just what games you have won, but also what games where your winning team has already been knocked out of the bracket. Episode 465 shows how.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!