IToverview.com - Easy To Learn Tutorials!

IToverview.com

Tutorials


Arithmetic Functions & Operators

We have explained about commonly used arithmetic functions like addition, subtraction, multiplication, division, round off, average, count, max, and min etc.


Arithmetic Operators in Excel 2013
Arithmetic Operators

Addition:

  • Place the cursor in I10.
  • In I10 cell, type =I5+I6+I7+I8+I9 and press Enter.
  • You can see the value of 120000 in I10 cell.

Subtraction:

  • If you want to find out the difference between maximum salary and minimum salary.
  • Place the cursor in I10.
  • In I10 cell, type =MAX(I5:I9) - MIN(I5:I9) and press enter (I5:I9 means the values present in cells I5, I6, I7, I8, I9).
  • You can see the value of 40000 in I10 cell.

Multiplication:

  • If you want to find out the total commission for those two salesmen: Under Total Commission Header, you don't see any values for those 2 salesmen.
  • Place the cursor in K5 to calculate the commission for John Smith.
  • In K5 cell, type =J5*I5 and press enter.
  • You can see the value of 2500 in K5 cell.
  • Do the same thing on K6 cell to calculate the commission for another salesman Kevin Peterson. You can see 7500 in K6 cell.

Division:

  • If you want to find out the percentage of commission(10000) when compared to salary(120000).
  • Place the cursor in K11 cell.
  • In that cell, type =(K5+ K6)/SUM(I5:I9)*100 and press enter.
  • You can see the value of 8.333333333 in K11 cell.

Rounding Off:

  • How to round it to 2 decimals for the value present in K11 (8.333333333).
  • Place the cursor on K11 and right click. In the menu, click format cells. A new screen is opened. Under Number Tab, select number from category. Select the positive number 1234.10. In decimal places, select 2. Click OK.
  • You can see a new value of 8.33.
  • NOTE: You can do the rounding off on a cell or on all cells under a column.

Average:

  • =AVERAGE(I5:I9) would return 24000 (By using function).
  • =(I5+I6+I7+I8+I9)/5 (By using formula).

Count:

  • =COUNT(I5:I9) would return 5 when all values are present (10000 (I5), 15000 (I6), 20000 (I7), 25000 (I8), 50000 (19)).
  • =COUNT (I5:I9) would return 4 when all values are not present (10000 (I5), 15000 (I6), 20000 (I7), 25000 (I8), (19)) . Intentionally, we deleted the value in the cell (I9).
  • =COUNT(B5:B9) would return 0. We counted the values present in the column "FIRST NAME". It will count only the numerical values. It will not count if value is not present in that cell.

Max:

  • =MAX (I5:I9) would return 50000 (maximum value in those cells).