Exceldata.weebly.com



Excel functionsTODAYDescription: Returns the current date.Syntax: TODAY()Example: =TODAY() - Returns the current date=TODAY()+5 - Returns the current date plus 5 days.NOWDescription: Returns the current date and timeSyntax: NOW()Example: =NOW() - Returns the current date and timeSUMDescription: The SUM function adds all the numbers that you specify as arguments?(argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.). Syntax: SUM(number1,[number2],...])Example: =SUM(A2:A5) - Adds the values in cells A2 through A5.AVERAGEDescription:Returns the average (arithmetic mean) of the arguments.Syntax:AVERAGE(number1, [number2], ...)Example: =AVERAGE(A2:A5) - Average of the numbers in cells A2 through A5MINDescription: Returns the smallest number in a set of values.Syntax: MIN(number1, [number2], ...)Example: =MIN(A2:A5) - Return Smallest number from cells A2 to A5MAXDescription: Returns the largest value in a set of values.Syntax: MAX(number1, [number2], ...)Example: =MAX(A2:A5) - Return largest number from cells A2 to A5COUNTDescription: The COUNT function counts the number of cells that contain numbers.Syntax: COUNT(value1, [value2], ...)Example: =COUNT(A2:A5) - Counts the number of cells that contain numbers in cells A2 through A5.COUNTADescription: The COUNTA function counts the number of cells that are not empty in a range?(range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.).Syntax: COUNTA(value1, [value2], ...)Example: =COUNTA(A2:A5) - Counts the number of nonblank cells in cells A2 through A5.COUNTBLANKDescription: Counts empty cells in a specified range of cells.Syntax: COUNTBLANK(Range)Example: =COUNTBLANK(A2:A5) - Counts empty cells in the range A2 through A5CONCATENATE Description: The CONCATENATE function joins up to 255 text strings into one text string.Syntax: CONCATENATE(text1, [text2], ...)Example: ABC1MasudKarimMasudKarim2MasudKarimMasudKarim3880295576548802-95576544=CONCATENATE(A1,B1) Concatenates the string in cell A1 and the value in cell B1.=CONCATENATE(A2,” “, B2)Concatenates the string in cell A2, a space character, and the value in cell B2.LEFTDescription: LEFT returns the first character or characters in a text string, based on the number of characters you specify.Syntax: LEFT(text, [num_chars])Example: ABC1University of Liberal ArtsU2University of Liberal ArtsUniversity=LEFT(A1) - Returns U=LEFT(A2,10) - Returns UniversityRIGHTDescription:RIGHT returns the last character or characters in a text string, based on the number of characters you specify.Syntax: RIGHT(text, [num_chars])Example: ABC1University of Liberal Artss2University of Liberal ArtsArts=RIGHT(A1) - Returns s=RIGHT(A2,4) - Returns ArtsMIDDescription: MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify.Syntax: MID(text, start_num, num_chars)Example: ABC12013052005288-02-910088802=MID(A1,5,2) - Returns 05=MID(A2,4,2) - Returns 02LENDescription:LEN returns the number of characters in a text string.Syntax: LEN(text)Example: ABC1ULAB42Dhaka5=LEN(A1) - Returns 4=LEN(A2) - Returns 5UPPERDescription:Converts text to uppercase.Syntax: UPPER(text)Example: =UPPER(dhaka) returns DHAKALOWERDescription:Converts all uppercase letters in a text string to lowercase.Syntax: LOWER(text)Example: =LOWER(DHAKA) returns DhakaPROPERDescription:Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.Syntax: PROPER(text)Example: =PROPER(dhaka) returns Dhaka=PROPER(dhaka city) returns Dhaka CityEXACTDescription: Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise.Syntax: EXACT(text)Example: =EXACT(“dhaka”,”dhaka”) returns TRUE=EXACT(“dhaka”,”Dhaka”) returns FALSEIFDescription: The IF function returns one value if a condition you specify evaluates to TRUE, and another value if that condition evaluates to FALSE.Syntax: IF(logical_test, [value_if_true], [value_if_false])AB1Age2 20Adult3 17MinorFrom the above Table, we want to find out either the person is Adult or Minor on the basis of their age. If age less than 18 then minor, otherwise adult. From the above Table we know that first person is adult, second person is minor and third person is adult. In cell B2 write =IF(A2<18,”Minor”,”Adult”)Now copy the formula by double clicking the + sign in B2 and automatically it will fill other two cells.The operations in the logical_test of the If function are:= Equals to, < > Not Equals to, > Greater than, >= Greater than or equal to< Less than, <= Less than or equal toDATEDIFDescription: Difference between two dates in whatever unit of measure you specify.Syntax: Datedif(Date 1, Date 2, Unit of Measure) Unit of Measure: “Y” = Years“M” = Months“D” = Days“YM” = Number of months since the last year “MD” = Number of days since the last monthExample:Joining DateToday’s Date Year, Months, DaysTotal MonthsTotal Days1/1/200810/30/201249295717649/5/200910/30/20123125371151In cell C2 write =DATEDIF(A2,B2,”Y”) returns 4In cell D2 write =DATEDIF(A2,B2,”YM”) returns 9In cell E2 write =DATEDIF(A2,B2,”MD”) returns 29In cell F2 write =DATEDIF(A2,B2,”M”) returns 57In cell G2 write =DATEDIF(A2,B2,”D”) return 1764COUNTIFDescription: The COUNTIF function counts the number of cells within a range that meet a single criterion that you specify.Syntax: COUNTIF(range, criteria)COUNTIFSDescription: Applies criteria to cells across multiple ranges and counts the number of times all criteria are met. Syntax: COUNTIFS(criteria_range1, criteria1, [criteria_range2, citeria2]…)AVERAGEIFDescription: Returns the average (arithmetic mean) of all the cells in a range that meet a given criteria.Syntax: AVERAGEIF(range, criteria, [average_range])AVERAGEIFSDescription: Returns the average (arithmetic mean) of all cells that meet multiple criteria.Syntax: AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)SUMIFDescription: You use the SUMIF function to sum the values in a range?(range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet criteria that you specify.Syntax: SUMIF(range, criteria, [sum_range])SUMIFSDescription: Adds the cells in a range?(range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) that meet multiple criteriaSyntax: SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)SUBSTITUTEDescription: Substitutes new_text for old_text in a text string. Syntax: SUBSTITUTE(text, old_text, new_text, [instance_num])TRIMDescription:Removes all spaces from text except for single spaces between words.Syntax: TRIM(text)Example: =TRIM(Nazrul Islam) returns Nazrul Islam=TRIM( dhakacity) returns dhakacityDAYDescription: Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31.Syntax: DAY(serial_number)MONTHDescription: Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (January) to 12 (December).Syntax: MONTH(serial_number)YEARDescription: Returns the year corresponding to a date. The year is returned as an integer in the range 1900-9999.Syntax: YEAR(serial_number)DATEDescription: The DATE function returns the sequential serial number that represents a particular date.Syntax: DATE(year,month,day)WEEKDAYDescription: Returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.Syntax: WEEKDAY(serial_number,[return_type])TEXTDescription: The TEXT function converts a numeric value to text and lets you specify the display formatting by using special format strings. Syntax: TEXT(value, format_text)Guidelines for date formatS“DDD” = Displays the day as an abbreviation (Sun to Sat).“DDDD” = Displays the day as a full name (Sunday to Saturday).“MMM”= Displays the month as an abbreviation (Jan to Dec).“MMMM”= Displays the month as a full name (January to December).How to?prevent duplicate?entries in a column in?Excel?Select the range of cells in which you want to prevent duplicate values.Choose Validation from the Data menu. In Excel 2010, click the Data tab and choose Data Validation from the Data Validation option’s drop-down list (in the Data Tools group).Click the Settings tab.Choose Custom from the Allow drop-down list.Enter a formula in the following form into the Formula control:=COUNTIF($range,firstcellinrange) = 1For example if you want to prevent duplicate entries in the range of A1 t0 A20 write the formula =COUNTIF($A$1:$A$20,A1)=1Click the Error Alert tab.Enter the text?Duplicate Entry?in the Title control.In the Error Message box, enter a meaningful description, such as?The value is a duplicate value and therefore, not valid.Click OK.How do protect a cell from being edited by others in Microsoft Excel 2010?First, you'll need to un-protect all of the cells on your sheet. To do this, select all of the rows and columns in your sheet. Right-click on then select "Format Cells" from the popup menu.When the Format Cells window appears, select the Protection tab. Uncheck the "Locked" checkbox. Click on the OK button.Next, select the cell(s) that you wish to protect. Right-click and then select "Format Cells" from the popup menu.When the Format Cells window appears, select the Protection tab. Check the "Locked" checkbox. Click the OK button.For the locking of the cells to take effect, you must also protect the worksheet. To do this, select the?Review tab?from the toolbar at the top of the screen. Then click on?Protect Sheet?button.A "Protect Sheet" window will appear. You may enter a password to protect the sheet if you wish. The password is optional. Click on the OK button. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download