EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Excel Tips CEILING Function in Excel
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup and Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • EXCEL COURSE
  • Online Excel Data Analysis Course
  • Excel for Finance Course

CEILING Function in Excel

By Jeevan A YJeevan A Y

CEILING Function in Excel

CEILING Function (Table of Contents)

  • CEILING in Excel
  • CEILING Formula in Excel
  • How to use the CEILING Function in Excel?

Introduction to CEILING in Excel

The ceiling function is another mathematical function in excel which is quite useful which is used for rounding off any decimal number closer to the value selected in the function but away from 0. RND function rounds off any decimal value to a whole number, whereas in the Ceiling function, we can choose the number to which extent we want to see the decimal number. We can either get the whole number or value in 0.5 formats.

In simple terms, you are providing both number and significance number, and you are asking Excel to round up the given to the nearest multiple of given significance number.

For example: If the formula reads =CEILING (22, 8), it will return the value of 24. Now, let us break down the formula.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

If you multiply 8 with 3, the resulting number is 24. The supplied number is 22 what is the nearest multiple numbers of 8.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,639 ratings)

8 * 1 = 8 (Not nearest to 22)

8 * 2 = 16 (Not nearest to 22)

8 * 3 =24 (Nearest to 22)

8 * 4 = 32 (Too far from 22)

So CEILING function returns the nearest multiple of significance number to the nearest supplied number.

CEILING Formula in Excel:

The Formula for the CEILING function is as follows:

CEILING Formula in Excel

The Formula of CEILING function includes 2 arguments.

  • Number: It is a mandatory argument. The number you want to round up.
  • Significance: This is also a required argument. This is the number you are multiplying to round up the first given number.

How to Use CEILING Function in Excel?

CEILING in Excel is very simple and easy to use. Let us understand the working of the CEILING function by some examples.

You can download this CEILING Function Excel Template here – CEILING Function Excel Template

Example 1

In the below image, I have applied the CEILING function to the numbers from A2:A7. I will discuss this one by one.

CEILING Example 1

  • In the first formula, the given number is 5.236, and the significance we are giving is 0.1. Formula used is =CEILING(A2,B2)

CEILING Example 1-1

Formula returned the value of 5.3, which means the formula rounded up the 5.236 to the next nearest multiple of 0.1, i.e. 5.30.

CEILING Example 1-2

  • In the second formula, we are rounding up the 5.5 to the next decimal number, i.e. 5.6. The formula used is =CEILING(A3,B3)

CEILING Example 1-3

The significance we have given is 0.7, i.e. 0.7*8=5.60.

CEILING Example 1-4

  • Now look at the third formula number is 10, and the significant number is 4. One thing you need to observe here is 4*2=8 (this is also nearest to 10) and 4*3=12 (this is also nearest to 10). The formula used is =CEILING(A4,B4)

CEILING Example 1-5

CEILING always returns the next nearest number. O, the result of the formula is 12.

CEILING Example 1-6

  • The fourth formula number is 20, and the significant number is 3, i.e. 3*7 = 21, which is nearest to 20. Formula used is =CEILING(A5,B5)

CEILING Example 1-7

The result of the formula is 21.

CEILING Example 1-8

  • CEILING also works for negative numbers too. In the fifth formula, the number is negative (-8), and the significance is positive (3). 3*3 = 9, which is nearest to 8. The Formula used is =CEILING(A6,B6)

CEILING Example 1-9

Since it is a negative number, the formula returns the lower value, i.e. 3*2=6, and the supplied number is negative; the result also will be negative only (-6).

CEILING Example 1-9.1

  • Final formula is 0.5*2 = 1 that is round up of 0.56. 0.5*1 = 0.5 only this is lower than the original value of 0.56. Formula used is =CEILING(A7,B7)

CEILING Example 1-10

The next highest value is 1.

CEILING Example 1-10.1

Example 2

In the first example, we have seen positive numbers with positive significance. In this example, I will explain the scenarios of negative numbers with positive significance.

CEILING Example 2

Negative numbers always round down the values, unlike positive numbers, which always round up the values.

  • In the first formula, the number is -12, and the significance is 5. Formula used is =CEILING(A2,B2)

CEILING Example 2-1

Significance multiple is 5*2 = -10 which is nearest to -12 unlike 5*3 =-15.

CEILING Example 2-1.1

  • The second formula returns 0; the reason behind this is the given number is -6, but we are multiplying values is 8, which always returns the value of more than 6. The formula used is =CEILING(A3,B3).

CEILING Example 2-2

Therefore, the formula returns nothing.

CLNG Example 2-2.1

  • Third, one is also the same as the second one. The significant number is more than the given number. Formula used is = CEILING(A4,B4).

CLNG Example 2-3

The result will be zero.

CLNG Example 2-3.1

  • In the fourth formula, a number is -15, and significance is 6. A formula used is =CEILING(A5,B5).

CLNG Example 2-4

Significance multiple is 6*2 = -12 which is nearest to -15 unlike 6*3 =-18.

CLNG Example 2-4.1

  • In the fifth formula, a number is -10, and significance is 5. The formula used is =CEILING(6,B6).

CLNG Example 2-5

The significance multiple is 5*2 = -10, which is equal to the given number.

CLNG Example 2-5.1

  • In the sixth formula, the number is -25, and the significance is 7. The formula used is =CEILING(A7,B7).

CLNG Example 2-6

Significance multiple is 3*3 = -21 which is nearest to -25 unlike 7*4 =-28.

CLNG Example 2-6.1

Things to Remember

  • In this function, the number always gets larger, i.e. round up always based on the multiple of significance number.
  • The number should not be less than the significance numbers; otherwise, the result would always be zero.
  • This formula does not accept values other than numeric values. If anyone of the supplied argument is non-numeric, then the result will be #VALUE!
  • The number is positive, and the significant number is negative, then we will get the error as #NUM! Therefore, if the significant number is negative, then the number also should be negative.

Recommended Articles

This has been a guide to the CEILING Function. Here we discuss the CEILING Formula and how to use the CEILING function along with a practical example and downloadable excel templates. You may also look at these useful functions in excel –

  1. MID Function in Excel
  2. SUM Function in Excel
  3. POWER Function in Excel
  4. SUBSTITUTE Function in Excel 
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Data Analysis Training (17 Courses, 8+ Projects)4.9
Excel for Finance Training (18 Courses, 7+ Projects)4.8
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download CEILING Function Excel Template

EDUCBA

Download CEILING Function Excel Template

EDUCBA

डाउनलोड CEILING Function Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more