Formulas and functions are the building blocks of working with numeric data in Excel. This article introduces you to formulas and functions. Show
In this article, we will cover the following topics.
Tutorials DataFor this tutorial, we will work with the following datasets. Home supplies budget S/NITEMQTYPRICESUBTOTALIs it Affordable?1Mangoes96002Oranges312003Tomatoes125004Cooking Oil565005Tonic Water133900 House Building Project Schedule S/NITEMSTART DATEEND DATEDURATION (DAYS)1Survey land04/02/201507/02/20152Lay Foundation10/02/201515/02/20153Roofing27/02/201503/03/20154Painting09/03/201521/03/2015 What is Formulas in Excel?FORMULAS IN EXCEL is an expression that operates on values in a range of cell addresses and operators. For example, =A1+A2+A3, which finds the sum of the range of values from cell A1 to cell A3. An example of a formula made up of discrete values like =6*3. =A2 * D2 / 2 HERE,
Formulas practical exerciseWe will work with the sample data for the home budget to calculate the subtotal.
We will now write the formula that calculates the subtotal Set the focus to cell E4 Enter the following formula. =C4*D4 HERE,
Press enter key You will get the following result The following animated image shows you how to auto select cell address and apply the same formula to other rows. Mistakes to avoid when working with formulas in Excel
Check Out What is Function in Excel?FUNCTION IN EXCEL is a predefined formula that is used for specific values in a particular order. Function is used for quick tasks like finding the sum, count, average, maximum value, and minimum values for a range of cells. For example, cell A3 below contains the SUM function which calculates the sum of the range A1:A2.
The importance of functionsFunctions increase user productivity when working with excel. Let’s say you would like to get the grand total for the above home supplies budget. To make it simpler, you can use a formula to get the grand total. Using a formula, you would have to reference the cells E4 through to E8 one by one. You would have to use the following formula. = E4 + E5 + E6 + E7 + E8 With a function, you would write the above formula as =SUM (E4:E8) As you can see from the above function used to get the sum of a range of cells, it is much more efficient to use a function to get the sum than using the formula which will have to reference a lot of cells. Common functionsLet’s look at some of the most commonly used functions in ms excel formulas. We will start with statistical functions. S/NFUNCTIONCATEGORYDESCRIPTIONUSAGE01SUMMath & TrigAdds all the values in a range of cells=SUM(E4:E8)02MINStatisticalFinds the minimum value in a range of cells=MIN(E4:E8)03MAXStatisticalFinds the maximum value in a range of cells=MAX(E4:E8)04AVERAGEStatisticalCalculates the average value in a range of cells=AVERAGE(E4:E8)05COUNTStatisticalCounts the number of cells in a range of cells=COUNT(E4:E8)06LENTextReturns the number of characters in a string text=LEN(B7)07SUMIFMath & TrigAdds all the values in a range of cells that meet a specified criteria. Numeric FunctionsAs the name suggests, these functions operate on numeric data. The following table shows some of the common numeric functions. S/NFUNCTIONCATEGORYDESCRIPTIONUSAGE1ISNUMBERInformationReturns True if the supplied value is numeric and False if it is not numeric=ISNUMBER(A3)2RANDMath & TrigGenerates a random number between 0 and 1=RAND()3ROUNDMath & TrigRounds off a decimal value to the specified number of decimal points=ROUND(3.14455,2)4MEDIANStatisticalReturns the number in the middle of the set of given numbers=MEDIAN(3,4,5,2,5)5PIMath & TrigReturns the value of Math Function PI(π)=PI()6POWERMath & TrigReturns the result of a number raised to a power. String functionsThese basic excel functions are used to manipulate text data. The following table shows some of the common string functions. S/NFUNCTIONCATEGORYDESCRIPTIONUSAGECOMMENT1LEFTTextReturns a number of specified characters from the start (left-hand side) of a string=LEFT(“GURU99”,4)Left 4 Characters of “GURU99”2RIGHTTextReturns a number of specified characters from the end (right-hand side) of a string=RIGHT(“GURU99”,2)Right 2 Characters of “GURU99”3MIDTextRetrieves a number of characters from the middle of a string from a specified start position and length. Date Time FunctionsThese functions are used to manipulate date values. The following table shows some of the common date functions S/NFUNCTIONCATEGORYDESCRIPTIONUSAGE1DATEDate & TimeReturns the number that represents the date in excel code=DATE(2015,2,4)2DAYSDate & TimeFind the number of days between two dates=DAYS(D6,C6)3MONTHDate & TimeReturns the month from a date value=MONTH(“4/2/2015”)4MINUTEDate & TimeReturns the minutes from a time value=MINUTE(“12:31”)5YEARDate & TimeReturns the year from a date value=YEAR(“04/02/2015”) VLOOKUP functionThe VLOOKUP function is used to perform a vertical look up in the left most column and return a value in the same row from a column that you specify. Let’s explain this in a layman’s language. The home supplies budget has a serial number column that uniquely identifies each item in the budget. Suppose you have the item serial number, and you would like to know the item description, you can use the VLOOKUP function. Here is how the VLOOKUP function would work. =VLOOKUP (C12, A4:B8, 2, FALSE) HERE,
The animated image below shows this in action Download the above Excel Code SummaryExcel allows you to manipulate the data using formulas and/or functions. Functions are generally more productive compared to writing formulas. Functions are also more accurate compared to formulas because the margin of making mistakes is very minimum. Which option is used to name a range of cells define range?Create a name by using the Define Name option
Another way to make a named range in Excel is this: Select the cell(s). On the Formulas tab, in the Define Names group, click the Define Name button.
What is the function for range in Excel?Use Range (arg), where arg names the range, to return a Range object that represents a single cell or a range of cells. The following example places the value of cell A1 in cell A5. The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range.
|