Lesson 2:Basic and Advanced formulas in Excel 2010

Lesson 2:Basic and Advanced formulas in Excel 2010

In this Lesson i will tell you what is the most important formulas in excelAmazing excel project management add in. Read more ... » and how to use them, First read the short description for each formula, then watch the video.


[cmamad id="2420" align="none" mobid="ad-off"]


&nbspRequirement Management Plan Template. Read more ... »;

A- Short Description:

List of MS Excel FunctionsMS Excel Functions and Formulas you need to use regularly. Read more ... » and Formulas you need to use regularly with photos and detailed explanation how to use each Microsoft ExcelAmazing excel project management add in. Read more ... » formula.

1- DateProject Charter Template. Read more ... » and time functions:

DATE(year,month,day):Returns the sequential serial numberFor Smart Only! Can you solve this Math riddle?. Read more ... » that represents a particular date. If the cell format was General before the function was entered, the result is formatted as a date.

Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900.

DAYS360(start_date,end_date):Returns the number of days between two datesMS Excel Functions and Formulas you need to use regularly. Read more ... » based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. Use this function to help compute payments if your accounting system is based on twelve 30-day months.

MS Excel Day360 Function
MS Excel Day360 Function

 

NOW( ): Returns the serial number of the current date and time. If the cell format was General before the function was entered, the result is formatted as a date.

 

TODAY( ): Returns the serial number of the current date. The serial number is the date-time codeFor Smart Only! Can you solve this Math riddle?. Read more ... » used by Microsoft Excel for date and time calculations. If the cell format was General before the function was entered, the result is formatted as a date.

 

WORKDAY(start_date,days,holidaysCreate Gantt Chart and cash flow using excel with sample file. Read more ... »): Returns a number that represents a date that is the indicated number of working daysCreate Gantt Chart and cash flow using excel with sample file. Read more ... » before or after a date (the starting date). Working days exclude weekends and any dates identified as holidays. Use WORKDAY to exclude weekends or holidays when you calculate invoice due dates, expected delivery times, or the number of days of work performed.

 

MS Excel WORKDAY Function
MS Excel WORKDAY Function

WORKDAY.INTL(start date,finish day,week endCreate Gantt Chart and cash flow using excel with sample file. Read more ... »,holidays): same as the above but gives you the option to select your weekend day or days.

 


2- Logical functions:

 

AND(logical1,logical2, …): Returns TRUE if all its arguments are TRUE; returns FALSE if one or more argument is FALSE.

MS Excel AND Function
MS Excel AND Function

IF(logical_test,value_if_true,value_if_false): Returns one value if a condition you specify evaluates to TRUE and another value if it evaluates to FALSE.

 

Logical_test     is any value or expression that can be evaluated to TRUE or FALSE. For example, A10=100 is a logical expression; if the value in cell A10 is equal to 100, the expression evaluates to TRUE. Otherwise, the expression evaluates to FALSE.

Value_if_true     is the value that is returned if logical_test is TRUE. For example, if this argument is the text string “Within budget” and the logical_test argument evaluates to TRUE, then the IF function displays the text “Within budget”. If logical_test is TRUE and value_if_true is blank, this argument returns 0 (zero). To display the word TRUE, use the logical value TRUE for this argument. Value_if_true can be another formula.

Value_if_false     is the value that is returned if logical_test is FALSE. For example, if this argument is the text string “Over budget” and the logical_test argument evaluates to FALSE, then the IF function displays the text “Over budget”. If logical_test is FALSE and value_if_false is omitted, (that is, after value_if_true, there is no comma), then the logical value FALSE is returned. If logical_test is FALSE and value_if_false is blank (that is, after value_if_true, there is a comma followed by the closing parenthesis), then the value 0 (zero) is returned. Value_if_false can be another formula.

 

MS Excel IF Function
MS Excel IF Function

3- Lookup and reference functions:

 

HLOOKUP(lookup_value,table_array,row_index_num,range_lookup): Searches for a value in the top row of a table or an array of values, and then returns a value in the same column from a row you specify in the table or array. Use HLOOKUP when your comparison values are located in a row across the top of a table of data, and you want to look down a specified number of rows. Use VLOOKUPAdd cost to all primavera activities from excel with Vlookup. Read more ... » when your comparison values are located in a column to the left of the data you want to find.

MS Excel HLOOKUP Function
MS Excel HLOOKUP Function

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup):

Searches for a value in the first column of a table array and returns a value in the same row from another column in the table array.

 

The V in VLOOKUP stands for vertical. Use VLOOKUP instead of HLOOKUP when your comparison values are located in a column to the left of the data that you want to find.

MS Excel VLOOKUP Function
MS Excel VLOOKUP Function

 


4- Math and trigonometry functions:

 

ROUND(number,num_digits): Rounds a number to a specified number of digits.

MS Excel Rounds Function
MS Excel Rounds Function

SUM(number1,number2, …): Adds all the numbers in a range of cells.

MS Excel SUM Formula
MS Excel SUM Formula

SUMIF(range,criteria,sum_range): Adds the cells specified by a given criteria.

Range     is the range of cells that you want evaluated by criteria.

Criteria     is the criteria in the form of a number, expression, or text that defines which cells will be added. For example, criteria can be expressed as 32, “32”, “>32″, or “apples”.

Sum_range     are the actual cells to add if their corresponding cells in range match criteria. If sum_range is omitted, the cells in range are both evaluated by criteria and added if they match criteria.

MS Excel SUMIF Formula
MS Excel SUMIF Formula

5- Statistical functions:

 

AVERAGE(number1,number2,…): Returns the average (arithmetic mean) of the arguments.

 

MS Excel AVERAGE Formula
MS Excel AVERAGE Formula

AVERAGEA(value1,value2,…): Calculates the average (arithmetic mean) of the values in the list of arguments.

 

COUNT(value1,value2,…): Counts the number of cells that contain numbers and counts numbers within the list of arguments. Use COUNT to get the number of entries in a number field that is in a range or array of numbers.

MS Excel COUNT Formula
MS Excel COUNT Formula

COUNTA(value1,value2,…): Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.

 

COUNTIF(range,criteria): Counts the number of cells within a range that meet the given criteria.

Range    is the range of cells from which you want to count cells.

Criteria    is the criteria in the form of a number, expression, cell reference, or text that defines which cells will be counted. For example, criteria can be expressed as 32, “32”, “>32″, “apples”, or B4.

MS Excel COUNTIF Formula
MS Excel COUNTIF Formula

MAX(number1,number2,…): Returns the largest value in a set of values.

MS Excel MAX Formula
MS Excel MAX Formula

 

MIN(number1,number2,…): Returns the smallest number in a set of values.


B- Watch this Video:

 

[embedplusvideo height="511" width="645" standard="http://www.youtube.com/v/Wa1uT0OW4PA?fs=1" vars="ytid=Wa1uT0OW4PA&width=645&height=511&start=&stop=&rs=w&hd=0&autoplay=0&react=1&chapters=&notes=" id="ep1656" /]

[cmamad id="2420" align="none" mobid="ad-off"]


 

  • Share on Social Networks

Leave a comment

Your email address will not be published.

Password Reset
Please enter your e-mail address. You will receive a new password via e-mail.