How to add・subtract・multiply・divide, and calculate division remainder

Being recognized as a ‘spreadsheet program’, Excel is strong in various types of calculations.
Here we will explain about the basics of Excel, the ‘four arithmetic operations‘.

Example of use 1) Four arithmetic operations

Below is a sales report of a Bakery shop.
We will calculate the total sold amount of Baguette.

 

① Click on cell to input formula.

 

② Type ‘=’.

 

③ Click on the unit price of Baguette (Cell ‘C4’).

 

④ Continue formula by typing ‘* (asterisk)’ which is a symbol for “multiplication” in Excel.

 

⑤ Click on the number of Baguette (cell ‘D4’).

 

⑥ Press Enter to complete formula.

=C4*D4

 

 

POINT!

Remember that all formulas in Excel begin with the ‘=’ sign.

C4×D4=  ⇒  =C4*D4

Symbols for the four operations…

Symbol Meaning
+ Addition
Subtraction
* Multiplication
/ Division

 

 

Example of use 2) How to calculate the ‘remainder’ in division

=MOD(number, divisor)

[number] = Numerator (The number that gets divided)
[divisor] = Denominator (The number that divides)

Definition of function A function that calculates the remainder from the result of the [number] divided by the [divisor].
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

To calculate the division remainder, we use the MOD function.

① Divide cell ‘B4’ by cell ‘D4’ and calculate the remainder in cell ‘F4’.

(1) [number] : Select cell that gets divided, cell ‘B4’.
(2) [divisor] : Select cell that divides, cell ‘D4’.
=MOD(B4,D4)

 

Rounding up and down/ROUND, ROUNDUP, ROUNDDOWN function

=ROUND(number, num_digits)

[number] = Number to round
[num_digits] = The number of digits to round

=ROUNDUP(number, num_digits)

[number] = Number to round up
[num_digits] = The number of digits to round up

=ROUNDDOWN(number, num_digits)

[number] = Number to round down
[num_digits] = Number of digits to round up

Definition of functions A function that rounds, rounds up, or rounds down a ‘number’ based on the selected ‘num_digits’.
EXCEL version Excel2003 ・ Excel2007 ・ Excel2010 ・ Excel2013・ Excel2016

 

About the [num_digits] (number of digits)…

The number of digits we identify in these functions is not the unit we want to round, but we identify the digit that we want to keep after the rounding process, using the following values. The ones is identified as ‘0’, and the smaller the unit the [num_digits] gets bigger, and the bigger the unit the [num_digits] will get smaller.

Thousands Hundreds Tens Ones Tenths Hundredths Thousandths
-3 -2 -1 0 1 2 3

Example) If we want to round the number ‘1234.56’ to tenths, it will  be:

[num_digits] = 1

 

Example of use) Rounding・Rounding up・Rounding down

Below is a sales report showing sales amounts for each employee.
We will ① round, ② round up, and ③round down the total (column E).

① Round the value of the total = cell ‘E4’.

(1) [number] : Select cell with total amount, ‘E4’.
(2)[num_digits] : Since we are rounding to the nearest whole number, we input ‘0’.
=ROUND(E4,0)

② Round up the value of the total = cell ‘E4’.

(1) [number] : Select cell with total amount, ‘E4’.
(2) [num_digits] : Since we are rounding up to the nearest whole number, we input ‘0’.
=ROUNDUP(E4,0)

① Round down the value of the total = cell ‘E4’.

(1) [number] : Select cell with total amount, ‘E4’.
(2) [num_digits] : Since we are rounding down to the nearest whole number, we input ‘0’.
=ROUNDDOWN(E4,0)

 

Rounding to the nearest thousands

Let’s check how to round numbers to the nearest thousands.

Round the value of the total = cell ‘E4’ (to the nearest thousands).

(1) [number] : Select cell with total amount, ‘E4’.
(2) [num_digits] : Since we are rounding to the nearest thousands, we input ‘-3’.
=ROUND(E4,-3)

 

How to include and exclude decimals through rounding/Format cells

Through settings, Excel can automatically round decimals as the value is typed or calculated.

Now let’s try to round the total by changing the settings in the cell.

 

Example of use) Changing the cell format

① Select cell to round.

 

② Right click on cell and click ‘Format cells‘.

 

③ Change the display of cells by following the steps:

 

(1) Click [Number] on the upper left hand.
(2) Select ‘Currency’.
(3) Change the ‘Decimal places’ from ‘1’ to ‘0 (zero)’.

④ Press ‘OK’ to complete.

 

POINT!

What is a dialog box?

A dialog box is a small window that appears in front of other windows, and it often appears when changing detailed settings.
In a dialog, we can check and change certain settings.