Chapter 28: All Calculation Functions in Excel

Introduction to Calculation Functions
Calculation functions in Excel are essential for performing mathematical operations and analyses. This chapter covers various functions used for calculations, including those for rounding, finding absolute values, and more. Each function is explained with its syntax and practical examples.

1. EVEN & ODD

EVEN

• Purpose: Rounds a number up to the nearest even integer.
• Syntax: =EVEN(number)
• Example: =EVEN(3) returns 4.

ODD

• Purpose: Rounds a number up to the nearest odd integer.
• Syntax: =ODD(number)
• Example: =ODD(5) returns 5, and =ODD(6) returns 7.

2. ABS

ABS

• Purpose: Returns the absolute value of a number (i.e., the number without its sign).
• Syntax: =ABS(number)
• Example: =ABS(-5) returns 5.

3. MOD

MOD

• Purpose: Returns the remainder after a number is divided by a divisor.
• Syntax: =MOD(number, divisor)
• Example: =MOD(10, 3) returns 1.

4. INT

INT

• Purpose: Rounds a number down to the nearest integer.
• Syntax: =INT(number)
• Example: =INT(5.9) returns 5.

5. LARGE & SMALL

LARGE

• Purpose: Returns the nth largest value in a data set.
• Syntax: =LARGE(array, n)
• Example: =LARGE(A1:A10, 2) returns the 2nd largest value in the range A1

.

SMALL

• Purpose: Returns the nth smallest value in a data set.
• Syntax: =SMALL(array, n)
• Example: =SMALL(A1:A10, 3) returns the 3rd smallest value in the range A1

.

6. ROUND, ROUNDUP, & ROUNDDOWN

ROUND

• Purpose: Rounds a number to a specified number of digits.
• Syntax: =ROUND(number, num_digits)
• Example: =ROUND(3.14159, 2) returns 3.14.

ROUNDUP

• Purpose: Rounds a number up, away from zero, to a specified number of digits.
• Syntax: =ROUNDUP(number, num_digits)
• Example: =ROUNDUP(3.14159, 2) returns 3.15.

ROUNDDOWN

• Purpose: Rounds a number down, towards zero, to a specified number of digits.
• Syntax: =ROUNDDOWN(number, num_digits)
• Example: =ROUNDDOWN(3.14159, 2) returns 3.14.

Pro Tip: Use ROUND to control the precision of your calculations, ROUNDUP when you need to ensure that numbers are rounded up, and ROUNDDOWN to keep numbers rounded down. For financial and statistical analyses, precise rounding can significantly affect your results.

7. MROUND

MROUND

• Purpose: Rounds a number to the nearest multiple of a specified value.
• Syntax: =MROUND(number, multiple)
• Example: =MROUND(15, 4) returns 16.

8. FLOOR & CEILING

FLOOR

• Purpose: Rounds a number down, towards zero, to the nearest multiple of significance.
• Syntax: =FLOOR(number, significance)
• Example: =FLOOR(15.7, 2) returns 14.

CEILING

• Purpose: Rounds a number up, away from zero, to the nearest multiple of significance.
• Syntax: =CEILING(number, significance)
• Example: =CEILING(15.7, 2) returns 16.

9. LCM & GCD

LCM

• Purpose: Returns the least common multiple of two or more integers.
• Syntax: =LCM(number1, number2, …)
• Example: =LCM(4, 6) returns 12.

GCD

• Purpose: Returns the greatest common divisor of two or more integers.
• Syntax: =GCD(number1, number2, …)
• Example: =GCD(8, 12) returns 4.

10. SUBTOTAL

SUBTOTAL

• Purpose: Returns a subtotal in a list or database.
• Syntax: =SUBTOTAL(function_num, range)
• Function Numbers: Use 1 for AVERAGE, 9 for SUM, 2 for COUNT, etc.
• Example: =SUBTOTAL(9, A1:A10) returns the sum of the range A1

, considering filtered data.

11. ROMAN

ROMAN

• Purpose: Converts an Arabic number to Roman numerals.
• Syntax: =ROMAN(number, [form])
• Form: 0 for Classic, 1 for Simplified.
• Example: =ROMAN(1987, 0) returns MCMLXXXVII.

12. Practice Assignment

1. Rounding:
   • Use ROUND, ROUNDUP, and ROUNDDOWN to round a list of numbers to different decimal places.
2. Absolute Values and Modulus:
   • Apply ABS to a range of numbers and use MOD to find remainders.
3. Finding Extremes:
   • Use LARGE and SMALL functions to identify the top 3 largest and smallest values in a data set.
4. LCM and GCD:
   • Calculate the LCM and GCD of pairs of numbers.
5. Date Calculations:
   • Use FLOOR and CEILING to round dates to the nearest specified unit (e.g., month, year).

Pro Tip: When working with calculations, it’s often useful to combine these functions to achieve complex mathematical results. Understanding each function’s behavior allows you to manipulate data precisely and effectively in your Excel spreadsheets. Anjni Computer Education offers practical examples and exercises to help you master these functions.