Home Archives for Excel Functions
September 18, 2008 1 Comment

The versatile and valuable =IF() Function

MS Excel’s =IF() Function is a very versatile function and it is used in a variety of situations. Watch this short video lesson to learn how to use it when Grading Scores, Calculating Shipping Rates and “Trapping Errors.”

Here are the steps to follow in this lesson:

  1. The =IF() Function requires three arguments: A “Logical Test” – (which will return either a TRUE of a FALSE Response); the Value if “True,” and the Value if “False.”
  2. In my first example I have set up a simple scoring system for grades. For the Value if TRUE, I have entered “Pass.” Notice that the TEXT must be enclosed in ” “.
  3. In my second example, I have “nested” several =IF() functions inside the Value if FALSE Function Argument. You can “nest” up to 7 different IF() Functions in a formula.
  4. Notice how useful the =IF() Function is when you want to “trap an error.” Especially when you are starting to build a spreadsheet and many cells contain Zeros or NULL Values.

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.

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , , , , , ,
September 2, 2008 1 Comment

Learn how Excel calculates time

Today’s lesson builds on the knowledge we gained in the prior video. So, rather than calculate “Dates”in Excel, we will calculate “Time.” Excel stores both Dates and Times as Serial numbers in a cell. This enables us to calculate the amount of time between two dates or two times.

Learning how to properly format the cells that calculate time is crucial to achieving correct results. It is also important to observe the proper syntax for entering times in cells (00:00:00 PM) or Hours: Minutes: Seconds AM or PM)

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

  1. Enter a Time function. e.g. =NOW() in a cell. Use the Keyboard Shortcut Ctrl+Shift+~ to reveal the serial number for that time.
  2. Excel counts time beginning at 12:00 AM or Midnight. So, the serial number for 6:00AM is 0.25
  3. If you only want to “Time Stamp” a cell, use the Keyboard Shortcut Ctrl+Shift+: (This entry will not update!)
  4. The =TIME() function requires three arguments (Hours, minutes, seconds)
  5. Be careful when performing calculations the show the difference between Start Time and End Time. If the End Time “spans midnight,” Excel will return an error unless…
  6. You use an =IF() Function in this calculation =IF(End_Time<Start_Time, End_Time+1, End_Time)-Start_Time
  7. To Increment a series of cells by 90 minutes use =Start_Time+TIME(1,30,0) and then copy the formula across the cells in the series.
  8. Formats matter! To correctly calculate hours that exceed 24 hours: Use this format – [h]:mm in the cell that contains the calculation.

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.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , , , ,
August 27, 2008 3 Comments

Learn how Excel calculates dates

If you create invoices or track time-sheets, you use date calculations in Excel. If you earn interest from an investment or pay interest on a loan, you need to understand how Excel handles dates. Once you grasp the concept that a date is a serial number, then you will start to understand how spreadsheets calculate dates. In this lesson, Danny will demonstrate the key concepts that will enable you to work smarter when you use dates in Excel formulas.

These are the steps to follow in this video lesson:

  1. Dates, in Excel, are serial numbers. Serial number 1 is January 1, 1900. (Apple Macintosh computers use January 1, 1904 as serial number 1.)
  2. Use the Keyboard shortcut Ctrl+Shift+~ to reveal the serial number of any date.
  3. The =TODAY() function is very useful. It will update to reflect your system’s date. If you want to “date stamp” a cell, use the keyboard shortcut Ctrl+; to enter today’s date (this will not update)
  4. Many formulas use the =DATE() function. To understand this Function, experiment with the =YEAR(), =MONTH() and =DAY() functions. These functions are frequently used inside the =DATE() function.
  5. For the Formula =End_Date-Start_Date, decide if you worked, (earned interest, paid interest, etc.) on either date. If so, the formula is =End_Date-Start_Date+1
  6. To calculate the # of Years between dates use a formula like; =YEAR(C7)-YEAR(B7)
  7. Excel has a “secret” formula =DATEDIF() – you can not get help for this formula. Use it to determine a person’s age, e.g. =DATEDIF(B11, C11, “y”)
  8. To determine the “Day in the year” use this formula =B15-DATE(YEAR(B15), 1, 0) – where cell B15 contains the date that you want to use.
  9. To see how many day remain in the year, use this formula =DATE(YEAR(B19), 12, 31)-B19 – where cell B19 contains the date that you want to use.
  10. When you want to write a formula to increment cells by 1 month (assuming that the starting date will change, use this formula =DATE(YEAR(F16),MONTH(F16)+1,DAY(F16))
  11. When you want to write a formula to increment cells by 1 year (assuming that the starting date will change, use this formula =DATE(YEAR(F17)+1,MONTH(F17),DAY(F17))

If you have questions on this lesson, please send me an email danny@thecompanyrocks.com

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.

Related Videos

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: ,
August 11, 2008 4 Comments

Use Vlookup function to prepare a payroll statement

A viewer wrote to me, asking for help in preparing his company’s payroll statement. I found an Excel Template using the =VLOOKUP() function to help him. In this Excel Video Training lesson, I demonstrate how to effectively use Lookup functions from 2 different worksheets.

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

  1. For the 1st part of this lesson I use an Excel Template called Payroll which I downloaded from the MS Excel website.
  2. The Payroll Statements draw information from two different worksheets via the =VLOOKUP() Function.
  3. Make sure that your “table arrays” have the key values in the 1st (leftmost) column.
  4. To return an “exact match” type in FALSE for the 4th argument.
  5. If you want to copy your =VLOOKUP() formulas, use “Absolute Cell” references for the table array.

Find the Excel Video Training 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:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training, MS Excel Training, Video Response to Viewers Tagged: , , ,
August 10, 2008 4 Comments

Use Excel's =VLOOKUP() function to produce an invoice

Excel’s =VLOOKUP() function is used in almost every business form that we encounter – Customer invoices or Employee Payroll statements, etc. This short training video will show you how and when to use the Vertical Lookup function.

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

  1. The key to understanding the =VLOOKUP() function is to look for a “value” in the 1st column (leftmost) of a table array. So bear this in mind when you construct tables for Price lists, Employee Information, Inventories, etc.
  2. 3 Arguments are required. The 1st argument – “What value do you want to look up?” You can either enter a value or point to a cell reference.
  3. The 2nd argument asks for the location of the table array. Select the entire table, but NOT the column headers.
  4. The 3rd argument asks, “Once I have found the value in the table, what information do you want returned in your formula?” This is the Column indes i.e. counting from left to right, what is the 3 of the column that contains the information.
  5. The 4th argument is optional – enter “FALSE” if you require an exact matching value.

Find the Excel Video Training Lesson that you want – Index to 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.

Related Video Lessons

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , , ,
August 1, 2008 4 Comments

Goal Seek to optimize the results of the =PMT() function

Here are the steps to follow in this video lesson:

  1. Calculate your monthly payment on a loan using =PMT() function
  2. =PMT() requires three arguments: “Interest Rate” / # of payments per year, “# of payments” over the course of your loan, the “Amount Borrowed.”
  3. For Goal Seek: Select the cell that contains your formula and then choose Tools – Goal Seek.
  4. In the “To Value:” box type in your GOAL – e.g. The amount that you want your =PMT() to return.
  5. In the “By Changing Cell:” select a cell that is referenced in your =PMT() formula.

Find the video lesson that you want – Index to all Excel Video Lessons

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

Share and Enjoy:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , , ,
July 23, 2008 Leave a Comment

Use the =TRIM() Function to eliminate unnecessary space inside a cell

Here are the steps to follow in this lesson:

  1. When we wrote the formula =B4&” “&C4&” “&D4)  we found an unnecessary blank space between “First Name” and “Last Name” when the cell reference for “Middle Name” was blank.
  2. Edit that formula by using the =TRIM() Function to remove the extra space in the resulting cell.

Find the video lesson that you want – Index to 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:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training, Text Functions Tagged: , ,
July 21, 2008 1 Comment

How to identify and convert text values to numbers

Here are the steps to follow in this lesson:

  1. Very that all values are actually numbers e.g. use the =COUNT() and =ISTEXT() functions.
  2. To convert a series a text values to numbers, first select a blank cell and copy it to the clipboard.
  3. Highlight your text values and choose Edit, Paste Special. In the Paste Special Dialog Box choose ADD.
  4. All text values are now converted into numbers that can be summed, etc.

Find the video lesson that you want – Index to 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:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , ,
July 20, 2008 1 Comment

Name a cell and use it in a formula

Here are the steps to follow in this lesson:

  1. A “named cell” creates an Absolute cell reference.
  2. One way to name a cell is to select the cell and type the name you want in the “Name Box” (Do not use any spaces) and hit Enter
  3. To apply the Named Cell in an existing formula, highlight the part of the formula and select Insert, Name, Paste (the named cell) and click OK.
  4. Consider creating a “Named Constant” for numbers you use frequently in a formula e.g. Sales Tax or Inflation Rate.
  5. To create a Named Constant choose Insert, Name, Define and in the “Refers to” box type (for example) = 0.0715
  6. Follow the same procedure to use the Constant in your formula: Insert, Name, Paste (named Constant) and click OK.
  7. Edit the Named Constant if e.g. the Sales Tax Rate changes. All formulas that use the Named Constant (in this workbook) will update automatically!

Click Here to Find the Excel Training Video in My Index

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:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training, Tips and Time Savers Tagged: , , , ,
July 19, 2008 1 Comment

Use absolute cell reference in % of total formula

Here are the steps to follow in this lesson:

  1. Make the right side of your formula – the reference to the “Total” – an Absolute Cell Reference.
  2. Change =C2/G1 to =C2/$G$1 by using the F4 Shortcut key.
  3. To reveal all Formulas in a worksheet, use CTRL + ~ (This is a toggle.)

Find the video lesson that you want – Index to 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:
  • Add to favorites
  • Facebook
  • Twitter
  • Technorati
  • Print
  • email
  • Digg
  • StumbleUpon
  • del.icio.us
  • Yahoo! Buzz
  • Google Bookmarks
  • Orkut
  • SphereIt
  • Sphinn
Link to this post!
Filed Under: Excel Functions, Excel Training Tagged: , ,
« Previous Page
Next Page »