by ·4 Comments

Calculate your age, WITHOUT using Excel’s DateDif function.


Calculate Your Age in Excel, without DATEDIF function

          My previous post on using Excel’s DATEDIF function resulted in rather productive discussions on LinkedIn. First of all, some users are concerned that the DATEDIF function will no longer be supported in Excel and will cease to exist at some point in the future. Microsoft openly states that “this function is provided for compatibility with Lotus 1-2-3.” , and given the history of this support, I don’t see why it will be discontinued. Nonetheless, I will make an attempt to replace DATEDIF function with other functions readily available in the program. Secondly, as a reminder that English is not the only language in the world, Microsoft created “local” versions of this function. A user confirmed existence of the SIFECHA function, but the syntax to use it, involves using semicolons, instead of commas. If you are using Spanish version of Excel, you might want to try both versions:

SIFECHA(fecha_inicial,fecha_final,unidad)
SIFECHA(fecha_inicial;fecha_final;unidad)

          According to this site , remaining versions of this function include:
Continue reading

by ·1 Comment

Calculate your age, using Excel’s DateDif function.


Excel's DateDif function - Calculate Your Age

          During this time of the year, most of us find ourselves performing some sorts of date manipulations in Excel: running year-end reports, creating a project plan for the next year, or simply setting up a new calendar. Let’s use one of Excel’s “hidden” functions to calculate one’s age in years, months, and days.

          We remember that Excel stores dates as serial numbers, in consecutive order. According to Microsoft, Day 1 fell on January 1st, 1900. Therefore, today’s date is equivalent to number 42,020 (meaning, 42,020 days passed since January 1st, 1900.) This makes it fairly easily to calculate difference between two dates [in days] by subtracting one date from another. While Excel does not support dates prior to January 1st, 1900 out of the box, if you need to work with such dates, John Walkenbach offers an Excel add in: XDate to help with this task.
Continue reading

by ·0 Comments

Using Nested If Statements.


Nested IF Function

          A reader asked me to explain logic behind the formula used in my Holiday Email post. That solution involved using multiple IF functions as arguments within another IF function, technique also known as nested IF logic. I find that the best way to use nested if functionality is through using decision trees AND Excel’s VBA syntax. Charting a decision tree will help us better understand the task at hand, and putting various conditions and the ways to handle them in writing, will prepare us to enter necessary formula in Excel.

          Let’s suppose that we need to implement the following logic with 4 possible scenarios. If the value of cell 1 is string “Case 1” AND cell 2 is string “Case 2”, we need the output to be 4. If cell 1 equals “Case 1”, but cell 2 does NOT equal “Case 2”, we need the output to be 3. If cell 1 does NOT equal “Case 1” , but cell 2 equals “Case 2”, the output should be 2. Finally, if cell 1 does not equal “Case 1”, NOR cell 2 equals “Case 2”, the output is 1. Hopefully, using the decision tree below makes it easier to conceptualize these scenarios.
Continue reading

by ·2 Comments

Excel Formulas vs. Functions – Auto Loan Calculator (PMT)


Car Loan Calculator using PMT Function in Excel

         If you are like me, you are using terms Excel Formulas and Functions interchangeably. However, these expressions represent distinctly different concepts. We can think of a formula as a statement that returns results of a certain user-defined calculation. Formulas can contain values (numeric, date, text,) cell references, ranges of cells, named ranges; they can perform any mathematical operation (addition, subtraction, multiplication, division, exponentiation,) as well as evaluate results of Excel’s built-in functions…A function is a predetermined formula that helps us simplify complex procedures and operations. For example, great folks at Microsoft programmed different functions that allow us to sum a group of numbers, search for a specific word within a text string, determine current date and time, or even figure out amount of our loan payment. Ability to construct complex Excel formulas , using multiple Excel functions is one of the prerequisites for becoming an Excel expert. Formulas always start with the equal sign ( = ), and we always enclose functional arguments (if any) inside parentheses.

          Let’s suppose that you need to calculate auto loan payment for your dream car – 2015 Ford Mustang GT. According to TrueCarPrice , for the base V8 model, you should expect to shell out $ 32,864 of your hard-earned dollars for this car; if you reside in the state that Ford Motor Company calls home. This price is comprised of $ 29,612 true value estimate, $ 567 market premium, $ 825 delivery charge, and state sales tax. According, to Bank Rate, you can realistically finance this purchase with a 3% interest rate loan. Since you just received your Holiday bonus, you want to put it towards a $5,000 down payment on your new car. Let’s calculate your monthly car payment using both: formula method without utilizing Excel functions: =P*i*(1+i)^n/((1+i)^n-1) , as well as through a formula incorporating Excel’s PMT function. Both of these methods rely on formulas to perform the necessary calculations, but only the second method makes use of an Excel function.
Continue reading