Question

Week 3 Discussion Board - Performing Calculations with Formulas and Functions Using the Internet, conduct a thorough search for more information on using formulas and functions in Excel and discuss two for your Discussion Board response. Choose from the following topics: • Session 3.1 Translate an equation with a function Do calculations with dates and times Extend data and formulas with AutoFill Use the Function Library • Calculate statistics Session 3.2 Using the Quick Analysis toolbar Use absolute and relative cell references Use a logical function Retrieve data with lookup tables Do what-if analysis with Goal Seek

Answer 1

Session 3.1

Do calculations with dates and times

DATE FUNCTION

To create a valid date when year, month and day components are given individually, Excel DATE function is used. The date functions enables the dates to change automatically according to the changes in the values in the worksheet.

It creates a date with a day,month and a year. The syntax of the DATE function in excel is =DATE(year,month,day) where year stands for Number for year,month stands for number for month and day stands for Number for day.

For example the DATE function in excel can be used to create the dates like May 17,1994 and September 4, 1994 like this:

=DATE(1994,5,17)          //           May 17,1994

=DATE(1994,9,4)           //            September 4, 1994

The DATE function in excel is useful when the date is required to change automatically according to the changes given in other values. For example, if A1 cell contains year 2018, then:

=DATE(A1,4,15)           //             April 15, 2018

The date will automatically changed to April 15, 2019 if the value in cell A1 is changed to 2019.

The dates in excel begin with the year 1900. If year entered is between 0 and 1900, excel will automatically add 1900 to it.

Month in excel can either be more than 12 and less than 0. If month is greater than 12, Excel will automatically add month to the first month in the year specified. If month in the formula is less than or equal to 0, Excel will automatically subtract the absolute value of month plus 1 (ABS(month) + 1) from the first month of the given year.

TIME FUNCTION

The TIME function in excel is a built in function in excel which allows us to create a time with individual second, minute and hour components. When a time is to be inserted in another formulas, then TIME function in excel is very useful.

The purpose of the time function is to create a time with the given hour, seconds and minutes. After entering the formula, time is displayed in the cell in the form of a decimal number.

The syntax of the time function is =TIME(hour,minute,second)

where hour represents the hour of the time, minutes represents the minute of the time and the second represents the second of the time we wish to create. For example:

=TIME(3,0,0)            //               3 Hours

=TIME(0,3,0)            //               3 Minutes

=TIME(0,0,3)            //               3 Seconds

Session 3.2

Use A Logical Function

The logical function in MS Excel consists of AND,OR,XOR and NOT. These 4 logical functions are used in MS Excel to work with logical values. The logical function are used in excel to do the conditional analysis rather than evaluating only one comparison. We will discuss AND function in detail:

The AND function in excel returns the result "TRUE" if all the arguments entered in the formula are true. For example, =AND(B2>=15,A2<5) formula gives the result as TRUE if value in B2 is more than or equal to 15 and A2 is less than 5, otherwise the answer will be displayed as false.

In logical functions in excel text as well as numeric values can be used. If in a logical function, a argument contains empty cell, the such cell is ignore by the function but if all the cells are empty #VALUE! error is displayed by the formula.

If the argument in logical function contains only numerical values, then the formula returns "TRUE", if any value is 0 then formula returns "FALSE". For example, if cells B1:B6 contain numbers, then formula =AND(B1:B6) will return the result as TRUE and if none of the cells in argument contains 0, the FALSE will be displayed.

A logical function returns the #NAME? error if you've misspell the function's name or attempted to use the function in an earlier Excel version that does not support it.