Do you manage Projects? Do you create Project work schedules? Do you need to know how many days were actually worked on your Project?
If so, this Excel video lesson is for you. A viewer asked for my help. He did not want to use MS Project for his work schedules. Rather, he wants to know which Excel Functions to use for his Project work schedules.
In this lesson, I demonstrate how to use two functions included in the Excel “Analysis Tool-Pak”:
Here is another response to a viewer request. The letter asks for my help in identifying, counting and totaling the amount of “Past Due” invoices. In the viewer’s letter, she wanted me to use the =NOW() Function. This function returns the current date and time (Hour, Minute, Second) from your computer’s system clock. The =TODAY() Function is similar, but it returns only the current date. Both the =NOW() and =TODAY() Functions are “volatile.” This means that the value that they return will automatically update according to your computer’s system clock. THis makes them excellent reference points in formulas that identify “Past Due” invoices.
In addition to using the =IF() Function to identify the invoices that are “Past Due,” I also demonstrate two other functions: =COUNTIF() to total the number of “Past Due” invoices and =SUMIF() to give me the total dollar amount that is “Past Due.” I recreate these formulas, this time, using “named cell ranges” in the formulas.
Finally, I show you a great new Filtering Feature in Excel 2007 – the ability to filter by time period e.g. “Next Week!”
Check out my new DVD, “The 50 Best Tips, Tricks, and Techniques for Excel 2007.”It contains over 5 1/2 hours of training for Excel 2007. You can locate the specific tip that you want to learn – and in @ 6 minutes, you will have received all of the information that you need to become more productive in this area.
A viewer wants to know how many badges have been claimed and how many have not yet been claimed. In Excel, use the =COUNTIF() Function to get the answer(s) to this question.
If you have ever struggled with using “Nested =IF() functions” you will welcome the =COUNTIF() Function – it is so much easier to use because the arguments for the function follow a logical progression.
My viewer also wanted to see the names of the guests who have claimed or who have not yet claimed their badges. To answer this request, I use a Data Filter – very easy to use and change.
In this Excel Video Lesson I demonstrate a great tip that dramatically simplifies data lookups!
Yes, learning how to use the Lookup Functions (VLOOKUP, HLOOKUP, MATCH, INDEX) is important. But, there is an easier way to answer some simple questions about you data.
In this video, I show you how to use the combination of “Named Ranges” and the “Intersection Operator” – the SPACE – to return the value at the intersection of a specific column and specific row. This “tip” is guaranteed to save you time and simplify your data lookups!
Here are the steps to follow in this lesson:
Create “Named Ranges” for your data set.
Use the “Intersection Operator” – the SPACE – in your formula. e.g. =Region_2 Week_16
It’s that simple!
NEW! Download the Excel file that I used in this video lesson:
Here are two ways to use the =COUNTIF() Function. First to count the number of employees in each department. And then, as a Formula in Data Valadation to ensure that employee numbers entered in a column are unique values.
Here are the steps to follow in this lesson:
Use the =COUNTIF() Function to count the number of values in a Range that match the Criteria that you set.
We have already named the Range $A$2: $B$27 as “Department.” Let’s use the F# (Paste Names) Shortcut to select this for our Range Argument.
For the Criteria, point and click the cell in Column D that contains the label for our first department (Accounting) and Click OK.
You can now copy the formula down to the other cells in Column E – This gives you a “Head Count” by Department.
For our second example, we are going to use DATA – VALIDATION to ensure that only “unique values” will be accepted in Column A – The “Employee Number” Column.
On the SELECT tab “Allow” drop-down, click “Custom.” Enter this formula in cell A2:
=COUNTIF($A$2:$A$27, A2)=1 This ensures that only unique values will be accepted. Note the use of “Absolute” and “Relative” references in the formula. The “Range” is stated as an “Absolute” $A2:$A$27
You will need to copy the Data Validation from cell A2 down to this rest of the cells in the range that you wish to validate. In this video I use Paste Special – Validation.
Today’s lesson is my answer to a viewer’s question: “How does Excel calculate a date in the future?”
The viewer wrote me because they were having trouble writing a formula that would return a date “N-Months” in the future. Or “3 years in the future” from a specific starting date.
I am asked this question frequently. In this short Excel video lesson I demonstrate how you write the formula.
Here are the steps to follow in this Excel Training Video:
Use the AutoFill feature and Options to increment your “Starting Date.” I recommend using your “right-mouse button” to AutoFill because it automatically brings up a Menu of Options when you release the Right-Mouse Button.
In response to the viewer’s question, we will choose “Fill Months.”
Remember that we are using a “Hard-Coded” date as our “Starting Date,” so each value is really a “constant value.” This may not be the optimal result that we are seeking.
In order to provide flexibility (with our starting date) we need to learn how to construct a formula that will increment (in this case the MONTH) our values.
Use the “DATE() Function.
For each of the “Arguments,” use the YEAR(), MONTH() and DAY() functions.
To answer my viewer’s question we use MONTH(A2) +1 in the “Month Argument.”
To “Augment the Year,” we use YEAR(A2) +1 in the “Year Argument.”
Finally, write a “Formula” to verify that Excel is properly accounting for “Leap Years.”
Many of my clients keep a “running total” of sales. They put “Dates” in Column “A” and enter each day’s sales in Column “B.” Next is Column “C” which contains the formula =SUM(B$:B250). That is fine – it works.
However, if you use the =SUMIF() function to total Year-to-Date (YTD) Sales you gain flexibility, save space and save time – because you do not have to scroll down several screens to see your YTD Sales total!
In this video, I show you how to use “Named Ranges” in a =SUMIF() Formula to keep track of your cumulative sales total.
Here are the steps to follow in this lesson:
Traditionally, you use a formula like =SUM(B$3:B250) to give you a “running total” for YTD Sales.
The=SUMIF() Function returns the SUM of vales that meet a “Single Criterion” (the IF portion) .
The 1st Argument is – What is the Range that you want your criteria to evaluate? Consider “Naming” this Range – it makes it easier to write your formula and explain your formula.
The 2nd Argument is – What is your criteria? Be sure to use this syntax – “Comparison Operator”
In the video example we also need to include the “&” to join the cell “D2.” Cell “D2,” in this example, contains the Function =TODAY()
The 3rd Argument is – What Range do you want to SUM to return the values that meet your Criteria?
The =DATE() Function returns the serial number for a specific date. It requires 3 Arguments. =Date, Year, Month, Day)
You will save time and gain productivity when you “Name Cells and Ranges” and then you them in your formulas.
Use the F3 shortcut key to “Paste Names” into your formulas.
Did you know that you can click on a Data Series in an Excel Chart and “drag it to reach a new value?” You can!
Changing the data value in your chart brings up the Goal Seek Dialog Box. Now, it is a simple matter to confirm your new value and tell Excel which cell reference to change in your formula. Instantly, your chart redraws and your data and formula are updated with the substitute value – and your NEW GOAL!
Here are the steps to follow in this Excel Video Lesson:
We use the =FV() Function to show the future value of our investment for each year that we invest. The =FV() Function requires a “fixed” interest rate and a constant investment value contributed each period.
We inserted a “Line” type chart onto our worksheet – i.e. we “embedded” the chart on the sheet.
We used a “Custom Format” on the Value Axis for our numbers (#,##0,K) to show the numbers as thousands ($80K)
For this chart, we also changed the “Maximum Value” of the Value Series by typing in a ne (higher) number.
To reach a “new goal” on our chart, first click to select the data series. Then click again and drag the data line to a new point on the chart.
This brings up the “Goal Seek” Dialog Box. Edit your new goal if necessary. Then click on the cell that you want to change in order reach your goal. That cell must be referenced in your formula. The chart redraws and your formula results are updated.
MS Excel’s =IF() Function is a very handy function. It is used in a variety of situations. Watch this short video lesson to learn how to use it in Grading Scores, Calculating Shipping Rates and in “Trapping Errors.”
Here are the steps to follow in this lesson:
The =IF() Function require 3 arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response.) The Value if “True,” and The Value if “False.”
In our 1st example we have set up a simple scoring system for grades. For the Value if TRUE, We have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
In our second example, we have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
Notice how useful the =IF() Function is when we want to “trap error.” Especially when we are starting to build a spreadsheet and many cells contain Zeros or NULL Values.
