Learn Excel from Bill Jelen with this daily 2 minute video podcast.
Andy wonders why the COUNT function doesn't count text entries. Episode 955 will show you how to overcome this with COUNTA.
posted by Bill at 9:00 AM
Mario sends in today's question. He has to do a SUMIF on a non-contiguous section of data. SUMIF can not handle this, but SUMPRODUCT can. Episode 886 shows you how.
Labels: Functions, SUMPRODUCT
posted by Bill at 9:00 AM
Can you use a function in an Excel calculated field? Well, Bryony from the U.K. says yes you can, and sure enough...it works. Episode 878 shows an example of using a function to create a new field in a pivot cache. Fairly cool.
Labels: calculation, Calculations, Functions, Pivot Table
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
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
Have you ever had an Excel calculation show up with a tiny fraction like .0000000002 at the end of the number? This prevents Excel from seeing if two numbers are equal or not. Episode 564 will show you two methods for dealing with this problem.
Labels: calculation, Functions, Round
posted by Bill at 9:00 AM
How can you figure out a date that is a certain number or workdays BEFORE a date? On the last episode of The Lab with Leo Laporte, I showed how to calculate a date 45 workdays after a certain date. Today’s question is how to calculate the date 5 days before the end of the month. Episode 560 shows you how to calculate this value.
posted by Bill at 9:00 AM
Daan Sprunken (who was a runner up in one of the last challenges) writes in to mention that there is a better way to find the last Saturday of the month, as we discussed in podcast Episode 524. Daan points out that the MOD function would have made that formula easier – and he is right. Not enough people think of MOD, so in Episode 541, I take a look at several different MOD examples.
posted by Bill at 9:00 AM
I love the DATE function. While some Excel functions are rather picky, the DATE function can handle just about anything that you throw at it. Want to know the 35th day of January? No problem. Want to know the 17th month of 2008? Easy. Want to know why this matters? Well, watch Episode 540 for a cool trick with the DATE function.
posted by Bill at 9:00 AM
Rupal asks how to return the worksheet name as the result of a formula. Episode 452 shows the obscure function which can be adapted to solve this problem.
Labels: Functions
posted by Bill at 9:00 AM
Learn how to use a simple English-language query in order to discover new functions. Episode 409 will show you how to find a function to calculate a monthly car payment. You can also use the Goal Seek function to immediately back into an answer.
posted by Bill at 9:00 AM
