If your compliance efforts need to be a little more random, watch Excel Video 194. I’ll start with a quick reminder that I’ve already covered some statistical functions relating to forecasting in Excel Videos 100-103. I won’t cover those function again in this series...

SUMPRODUCT gives you two functions for the price of one in Excel Video 193. If you have two columns you want to multiply together and then get a total, you can always add a third column to multiply the first two columns and then sum the third column. If you need to...

When you see =INT(A16/60)&":"&IF(LEN(MOD(A16,60))=1,"0"&MOD(A16,60),MOD(A16,60)) in Excel Video 192, you’ll be glad you watched Excel Video 191! That big, long formula converts the minutes in cell A16 to an hours:minutes format. You can always just copy that formula...

Invest 4 minutes to learn about 3 functions by watching Excel Video 191. The payoff will be next time when we use all three to convert minutes to an hours:minutes format. INT simply rounds a number down to the nearest integer. LEN calculates the length, or number of...

ROUNDUP every medical practice manager you know to watch Excel Video 190. Many of you may be familiar with the ROUND function in Excel. The syntax is simply =ROUND(number you want to round, number of digits to round to). If the number of digits is 0, Excel rounds to...

COUNT on Excel Video 189 to keep you out of trouble with Excel’s count functions. Today I’ll spend a few minutes reviewing three different counting functions in Excel. COUNT does just what you’d expect; it counts the number of cells in a range. The catch is that COUNT...

Have you ever brought a report to a physician who said, “That’s great. Now that you can do x, can you do y?” Excel Video 188 does the same thing. Now that we can do an average with one condition, we’ll take it to the next level and calculate averages with multiple...

You can go immediately to being an above-AVERAGE Excel user by learning AVERAGEIF in Excel Video 187. The structure of AVERAGEIF is very similar to SUMIF from Excel Video 184. We’ll apply AVERAGEIF to a list of OR start times to calculate the average start time, the...

You can’t COUNT all of the ways COUNTIF can help you in your practice. Excel Video 186 reviews both COUNTIF and COUNTIFS. These functions are easier to use than SUM and SUMIF since the counting functions don’t have a separate criteria range and sum range. You simply...

If you liked Excel Video 184 and want to have multiple criteria in your SUMIF formula, watch Excel Video 185. The function is called SUMIFS and it’s new in Excel 2007. SUMIFS lets you have up to 127 criteria in a SUMIF function. If you need anywhere near that many...

Just because Excel Video 184 starts into what Excel calls Math & Trig functions, don’t skip these videos. I promise not to get into cotangents and logarithms. There are several powerful features in this section of formulas that can make a medical practice manager’s...

Excel Video 183 uses INDEX and MATCH to look up which payer has the highest and lowest reimbursement for a given CPT code. You’ll see in Excel Video 183 that I have a variety of statistical information about how different payers reimburse for a given CPT code. By...

There are two tricks to watch for in Excel Video 182. First, we’ll use the fourth section of the INDEX function so we can look in multiple areas in our spreadsheet for data. To look in multiple areas, first define each area in the first parameter of the INDEX...

Excel Video 181 takes our INDEX and MATCH discussion one step further. Last time we used the MATCH function to calculate the row for INDEX to use. In this video, I’ll demonstrate how to use MATCH to provide both the row and column in an INDEX function. By using MATCH...

Excel Video 180 combines INDEX and MATCH to create a powerful, flexible lookup tool. Now that we’ve seen both INDEX and MATCH in the last two Excel Videos, the trick is to combine the function to create a lookup tool. Remember INDEX asks for the area to look in and...

Watch Excel Video 179 to get started with MATCH, a flexible Excel function that can add power to other functions. The syntax of MATCH is: =MATCH(what to look for, where to find it, match type). What to look for (lookup value) and the range to find it are very similar...

Excel Video 178 introduces INDEX, a more flexible lookup function than VLOOKUP or HLOOKUP. One of the shortcomings of VLOOKUP, for example, is that the data you’re looking for has to be in the far left column of the range you’re looking in. (No, you can’t enter a...

Excel Video 177 demonstrates an easy way to add a drop-down selection box to your worksheet. All of the formulas on this tab are based on having one of the five mutual funds in cell B8. To help my users put the right value in B8, I use Data Validation from the Data...

In Excel Video 174, I explained that one of the gotchas in VLOOKUP is that when you copy a VLOOKUP formula, the column reference doesn’t change. We used an Excel function called COLUMN as the column reference so that we could easily copy the VLOOKUP formulas. In Excel...

Excel Video 175 tackles HLOOKUP. HLOOKUP is very similar to VLOOKUP, so similar in fact that we’ll use the same data from the VLOOKUP videos to discuss HLOOKUP. The difference between VLOOKUP and HLOOKUP literally is the first letter. V stands for Vertical (look in...