Excel Functions | The Company Rocks

Danny October 17, 2009 Leave a Comment

Use the =COUNTIF() Function to see how many badges have been claimed

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.

Learn Excel quickly. Over 5 1/2 hours of Excel 2007 training on my DVD. Each lesson averages @ 6 minutes.

Shop for the DVDs in “The 50 Best Tips for…” series at my online bookstore.

Share and Enjoy:

Filed Under: COUNTIF and SUMIF Functions, Excel Functions, Excel Training, MS Excel 2007 DVD, MS Excel Training, Video Response to Viewers Tagged: COUNTIF Function, Excel Video Training, Filter a List, IF() Function, New in Excel 2007

Danny April 3, 2009 Leave a Comment

Three Frustrations When Calculating TIme in Excel

When I am training a class to use MS Excel, I always get at least one question about performing time calculations. I sense that the person asking the question is frustrated. When I ask, “How many of you are frustrated when you try to perform a calculation to total time periods in Excel?” nearly every hand is raised! And they are right; Excel’s time calculations can cause frustration.

In this video lesson, I will show you how to overcome three common frustrations that you may have when performing time calculations in Excel.

Here are the steps to follow in this Excel Training Video:

Format the cell that contains the formula =End_Time – Start_Time) to the Custom Number Format h:mm
I encourage you to use this formula instead of a simple formula: =IF(End_Time < Start_Time, End_Time + 1, End_Time)-Start_Time.
This will eliminate any possible “Negative times” which result in a cell filled with ######### A Negative Time is frequently the result of “Time Periods that Span Midnight. e.g. Start @ 11:00 PM and End @ 3:00 AM
TIP: Use this Formula =MOD(End_Time – Start_Time, 1) This is the MODULUS Function.
When you calculate the total number of hours and the result exceeds 24 hours, use this Format [h]:mm

Search My Index of Excel Video Lessons

“The 50 Best Tips for …” is now available at the online store for The Company Rocks

Simplify Data Lookups in Excel

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:

simplify-data-lookups

Looking for the answer to your Excel Questions? Use my Index of Excel Topics to find the right video

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Using =COUNTIF() Function to Validate Data

It is great to be back posting on The Company Rocks Excels. Thank you for your patience as I have worked to complete a series of Excel 2007 Training Videos to be published this Spring.

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 F3 (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.

Find the Excel Training Video that you want in the Index of Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Use the =SUMIF() Function to Tally YTD Sales

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.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Find the Excel Video Lesson that you want – Index to all Excel Topics

Share and Enjoy:

Filed Under: Excel Date and Time Functions, Excel Functions, Excel Training, SUMIF and SUMIFS Functions Tagged: DATE() Function, Keyboard Shortcuts in Excel, Named Cell in a formula, SUMIF Function, TODAY() Function in Excel, Track cumulative sales in Excel

Danny October 24, 2008 1 Comment

Click & Drag a Data Series to Goal Seek in Excel

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:

I use the =FV() Function to show the future value of my investment for each year that we invest. The =FV() Function requires a “fixed” interest rate and a constant investment value contributed each period.
I inserted a “Line” type chart onto my worksheet – i.e. I “embedded” the chart on the sheet.
I used a “Custom Format” on the Value Axis for our numbers (#,##0,K) to show the numbers as thousands ($80K)
For this chart, I also changed the “Maximum Value” of the Value Series by typing in a new (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.

Find the Excel Video Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:

Filed Under: Excel Charts, Excel Functions, Excel Training, What-if Analysis Tagged: "What-if" Analysis in Excel, Excel Video Training, Format Excel Chart Values, FV() Function, Goal Seek, Line Chart in Excel

Danny October 18, 2008 2 Comments

Excel Tells You the Future Value of Your Investment

Hey, want to know what your investment will be worth in 5 years? 10 years? Want a sure thing? Excel correctly calculates the future value of any investment! Provided …

You make a series of regular investments – even if it is just one “lump sum.”
The interest rate that you earn is “constant” – that is a “fixed annual interest rate.”

OK, so now you understand that neither I nor Excel are “touting sure thing” winners in the stock market. No one can do that. But, you can’t beat Excel when it comes to accurate – and easy to use – financial calculations.

In this video lesson, I demonstrate how to use both the =FV() and =PMT() Functions to calculate Future Values. I also show you how to perform “What-If” Analysis using a One-Input Data Table.

Here are the steps to follow in this Excel Video Lesson:

Input values for Interest Rate (RATE), Number of Periods (NPER) and your Investment (PMT). These are the three required arguments for the =FV() Function. Use the Function Argument Dialog Box to ensure that you use the correct syntax for this function.
One of the “optional” arguments is “Type.” If you omit this, Excel defaults to a “0” which means that you will make your investment contribution at the end of each period. A “1” means that the contribution is made at the beginning of each period. Over a number of years, this can make a significant difference in the “Future Value” of your investment.
FV is also an “argument” in the =PMT() Function. If you want to calculate how much you need to contribute each month to reach a Savings Goal, use the =PMT() Function. However, in this case, the FV – while showing as an “Optional Argument” will refer to the cell containing your Savings Goal.
Create a One-Input Data Table to perform “What-if” Analysis – e.g. to substitute a series of different interest rates in your =PMT() – or =FV() Function.

NEW! Download the Practice File for this Excel Video Lesson:

future-values

Find the Excel Video Lesson you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:

Filed Under: Excel Formulas, Excel Functions, Excel Training, What-if Analysis Tagged: "What-if" Analysis in Excel, Excel Data Tables, Excel Video Training, Financial Functions in Excel, FV() Function, PMT() Function

Danny October 5, 2008 Leave a Comment

Generate Random Numbers for Excel Spreadsheets

When I create the examples for this podcast I frequently use the =RANDBETWEEN() function to generate random numbers. Then, I usually copy the formulas and use Paste Special to convert them into values.

Recently, I watched Bill Jelen. Mr. Excel demonstrate an absolutely amazing Excel Trick – use your Right-mouse button to move a selected block of cells over the the right. And then, without releasing the mouse button, move the block of cells back into place. A menu pops up and asks if you want to “Copy here as Values.” Try it! It is amazing! What a time-saver!

Here are the steps to follow in the Excel Video Lesson:

Use the =RANDBETWEEN() Function to generate random numbers in your Excel spreadsheet. The function takes two arguments – a Top Number and a Bottom Number (both of your choosing.)
The RANDBETWEEN Function is part of the Analysis ToolPak Add-IN. Activate it by choosing Tools – Add-Ins- Analysis ToolPak.
Select the block of cells before you write the formula and then use Ctrl+Enter to populate all of the cells in your selected range with the formula.
To convert the formulas to values, use Copy, Paste Special Values.
OR… try this incredible trick (courtesy of Bill Jelen, Mr. Excel: Select the block of cells. With the RIGHT-Mouse Button, move the Border of the cell range over the the right and then back into place. Then, when you release the mouse a menu pops up. Choose, “Copy as Values Here.” It is amazining!

Find the Video Lesson that you want. Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:

Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: Bill Jelen, Convert Formulas to Values, Excel Analysis Toolpak Add-in, Excel Tricks, Mr. Excel, Paste Special Values, RANDBETWEEN() Function

Danny September 27, 2008 Leave a Comment

Trust Your Sources but Verify Your Data’s Accuracy

You may be familiar with the expression, “Trust but Verify!” President Reagan used this phrase when referring to the Soviet Union’s agreement to disarm their missiles. It is also a good approach to follow when you receive Excel data from someone else:

Never open up a data file unless you trust the source.
Never make a decision on the data until you verify the accuracy of the formulas and the structure of the spreadsheet.

In this Excel Video Lesson, I demonstrate how to use the GoTo Special Dialog box to verify the accuracy of your data.

Here are the steps to follow in this lesson:

Choose Edit – GoTo – Special – Formulas to highlight all of the cells containing formulas in the worksheet.
The Keyboard Shortcuts for “GoTo” are Ctrl+G or the F5 Key.
In the “Special” dialog box, you can also choose “Constants.” This may make it easier to spot the cells that are “hard-coded” with a constant value when they should contain a formula.
You can narrow your selection to “Text,” “Logical,” Numeric,” etc. formulas.
Use the Ctrl+~ (Tilde) Shortcut to show the actual formulas in your worksheet.

NEW! Download the Practice File Used in This Excel Training Video

go-to-special

Find the Excel Video Lesson that you want – Index to all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Excel’s QuickSum Calculator – Get Results Without Writing a Function

MS Excel 2003 has a great feature – The QuickSum Calculator – but you have to know where to find it. It is on the Status Bar – that area at the lower right of your screen. Simply select at least two data cells. They can be contiguous or non-contiguous. But remember where to look for the QuickSum Calculator – it is on the Status Bar.

Want to change the function? It simple! Just right-click on the name of the Function. You can choose Average, Count, Count Numbers, Min or Max – Excel’s most common Functions.

This is a great way to perform an Ad-hoc Query. I recommend that you use this for spreadsheets with Subtotals. Watch this short video lesson to see how this works – and how you can save time!

Find the Excel Video Lesson that you want – Index of all Excel Topics

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Share and Enjoy:

Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: AutoSum, Average Function, Excel Functions, Excel Subtotals, QuickSum Calculator, Status Bar

Danny Rocks “Training You to Excel”

Follow Danny on Social Media

Related Videos

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Video Lessons

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

Related Videos

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

NEW! Download the Practice File for this Excel Video Lesson:

News! My DVD, “The 50 Best Tips for Excel 2007” is now available to purchase. I invite you to visit my online bookstore for more details.

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

NEW! Download the Practice File Used in This Excel Training Video

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Related Videos

News! My DVD, “The 50 Best Tips for Excel 2007” is now availabe to purchase. I invite you to visit my online bookstore for more details.

Locate My Video Tutorials by Category

Seach My Website with Keywords

Podcast Feeds

Blogroll

Blogs for Business Writing

Blogs for Excel

Blogs for Presentations

Register or Log In

Tags