Excel Functions and Formulas – Mathematical Functions
There are many basic mathematical functions built into Excel. These functions help you prevent writing commonly repeating and complex formulas by yourself. Basic Excel functions are for addition, subtraction, multiplication and division. You can use the symbols +, -, * and / respectively to do the above mentioned operations. The order of operations is same as in standard mathematical operation. The part of operation which is specified in parenthesis would be always calculated first. Next, the priority would be given to multiplication, division, addition and then subtraction.
Following are some of Excel functions.
SUM: If you wish to add values, you can use this function. If you try to add text values, then you will get an error and empty cell will return 0 as the value. Consider the excel file below to illustrate some examples.
Now, the results you could expect for the following examples are
- =sum(A1:A3) 135. You get the result by adding 20, 50 and 65.
- =sum(A1:A3,100) 235. You get the result by adding 20, 50 and 65 and then 100 to the result.
- =sum(A1:A3)-100 35. You get the result by adding 20, 50 and 65 and subtracting 100 from the result.
- =sum(A1+A6) 96. You get the result by adding values 20 and 76.
- =sum(A1+A4) #VALUE!. You get the error as you tried to add a number and a text value.
- =sum(A5+A6) 76. You get the result by adding 0 and 76.
AVERAGE: Average calculates the average of different numbers. Text fields and blank entries are not included in the calculation of average. Using the same data given above, see the examples.
- =average(A1:A3) 45. You get the result (20+50+65)/3
- =average(A1:A3,55) 47.5. You get the result (20+50+65+55)/4
- =average(A1,A5) 20. You get the result 20/1
- =average(A2,A4) 50. You get the result 50 as text value is not included in calculation.
MAX: Max calculates the maximum value in a list of values. Text values are not included in the calculation of max.
ROUND: Round function rounds a number to specified number of decimal places. Syntax of ROUND function is ROUND(number, number of digits).
- =round (12.346, 2) 12.35 Values after decimal is rounded to 2 decimal places and as the third value is 6 (greater than or equal to 5) the value changed to 35.
- =round (123.346,-2) 100 Value before decimal is rounded to 2 places and as 23 is less than 50, the value became 100.
ROUNDDOWN: ROUNDDOWN works similar to ROUND function and syntax also is same, but it always rounds down.
- =rounddown (12.346, 2) 12.34 Values after decimal is rounded down to 2 decimal places.
- =rounddown (126.346,-1) 120 Value before decimal is rounded to 1 place.
ROUNDUP: ROUNDUP works similar to ROUND function and syntax also is same, but it always rounds up.
- =roundup (12.343, 2) 12.35 Values after decimal is rounded up to 2 decimal places.
- =roundup (126.346,-1) 130 Value before decimal is rounded up to 1 place.
EVEN: EVEN rounds up positive number up and negative number down to the nearest even integer.
- =even(11.4) 12
- =even(12.2) 14
- =even(-5) -6
ODD: ODD rounds up positive number up and negative number down to the nearest odd integer.
- =odd(11.4) 13
- = odd (14.2) 15
- = odd (-5) -5
- = odd(-5.3) -7
PMT: PMT function is used to calculate periodic payment for any kind of annuity. We have used the same function in the first chapter to calculate gold loan. Syntax of PMT function is PMT(interest rate, number of payments, present value of loan, future value of money, type).
SIN, COS, TAN, SINH, COSH, and TANH: These are basic trigonometric functions built into Excel.
Read More about Excel here