EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel FLOOR Excel Function
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 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
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

FLOOR Excel Function

By Pradeep SPradeep S

FLOOR Function in Excel

FLOOR Function in Excel (Table of Contents)

  • FLOOR in Excel
  • FLOOR Formula in Excel
  • How to Use the FLOOR Function in Excel?

FLOOR in Excel

The floor function is used for rounding down the decimal number and integer numbers, but this function converts the selected numbers to the nearest possible specified numbers to any down value of it. For example, if we have a number 10 that we need to round, then multiple values should down less than 2 to see the change as per syntax significance. If we select 3 here in place of significance multiple, then we will see round down to nearest 3 as 9.

Start Your Free Excel Course

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

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,412 ratings)

FLOOR Formula in Excel:

Below is the FLOOR Formula in Excel.

FLOOR Formula in Excel

Explanation of FLOOR Function in Excel:

The FLOOR formula in excel has two arguments.

  • Number: It is a number you want to round or a number that we want to round.
  • Significance: It is multiple to which you wish to round the number.

How to Use the FLOOR Function in Excel?

FLOOR function in Excel is very simple and easy to use. Let us understand the working of the FLOOR function in Excel by some FLOOR Formula example.

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

Example #1

In this FLOOR function in Excel example, I have to sort out only bundles that are complete, i.e. bundles having only 5 quantities of a product. The quantities are as mentioned in column B; Now, I want to bundle them in multiples of 5 using floor function in column C. Need to Follow the below-mentioned steps in Excel:

Select the output cell where we need to find out floor value, i.e. C8 in this example.

Example 1-1

Click the insert function button (fx) under the formula toolbar, a dialog box will appear. Type the keyword “floor” in the search for a function box; the FLOOR function will appear in select a function box. Double click on the FLOOR function.

FLOOR Example 1-2

A dialog box appears where arguments (Number & significance) for floor function needs to be filled or entered.

FLOOR Example 1-3

Here, number one is 14; Whereas the significance value is 5, the Significance value is common to other cells.

Following FLOOR formula is applied in the C8 cell, i.e. =FLOOR(B8,5).

FLOOR Example 1-4

Output or Result: The value 14 is rounded to 10.

FLOOR Example 1-5

Similarly, it is applied to other cells in that column to get the desired output.

FLOOR Example 1-6

Example #2

Here, In the second example of the FLOOR function in Excel, the number argument is a decimal value in cell B17 & the significance value is 2 in cell C17. I need to find out the nearest multiple of 2 for the Decimal value of 5.7

Need to Follow the below-mentioned steps in Excel:

Select the output cell where we need to find out floor value, i.e. D17 in this example.

Example 2-1

Click the insert function button (fx) under the formula toolbar, a dialog box will appear, type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on the FLOOR function.

FLOOR Example 2-2

A dialog box appears where arguments (Number & significance) for floor function needs to be filled.

FLOOR Example 2-3

Here number argument is 5.7, Whereas the significance value is 2. Following floor, formula is applied in the D17 cell, i.e. =FLOOR(B17,C17)

FLOOR Example 2-4

Output or Result: It Rounds 5.7 down to the nearest multiple of 2, and it returns the value 4.

FLOOR Example 2-5

Example #3

In the third example of the FLOOR function in Excel, the number argument is a negative value (-4.5) in cell B22 & the significance value is also a negative value (-2) in cell C22. I need to find out the nearest multiple of -2 for the number value -4.5.

Select the output cell where we need to find out floor value, i.e. D22 in this example.

Example 3-1

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on the FLOOR function.

Insert Function Example 3-2

A dialog box appears where arguments (Number & significance) for floor function needs to be filled.

FLOOR Example 3-3

Here, number one is -4.5, Whereas the significance value is -2, Both of which are negative values. Following FLOOR formula is applied in a D22 cell, i.e. =FLOOR(B22,C22).

FLOOR Example 3-4

Output or Result: It Rounds -4.5 down to the nearest multiple of -2, and it returns the value -4.

FLOOR Example 3-5

Example #4

FLOOR function in Excel returns an #NUM! error value if the number value is positive and the significance value is negative. In the below-mentioned example, the number argument is a positive value (5.7) in cell F9 & the significance value is a negative value (-2) in cell G9. I need to find out the nearest multiple of -2 for the value 5.7.

Select the output cell where we need to find out floor value, i.e. H9 in this example.

Example 4-1

Click the insert function button (fx) under the formula toolbar; a dialog box will appear, type the keyword “floor” in the search for a function box, FLOOR function will appear in select a function box. Double click on floor function.

Insert Function Example 4-2

A dialog box appears where arguments (Number & significance) for floor function needs to be filled.

FLOOR Example 4-3

Here number argument is 5.7, Whereas the significance value is -2

Following FLOOR function is applied in an H9 cell, i.e. =FLOOR(F9,G9).

FLOOR Example 4-4

Output or Result: FLOOR function returns an #NUM! error value if the number is positive value and significance is a negative value.

NUM Error Example 4-5

Whereas in the case if the number is negative and significance is a positive value. In current versions of Excel 2016 & 2010 or later, it can handle a negative number and positive significance. #NUM! error value will not occur. In the below-mentioned example, a negative number argument (-23.75) in cell F10 and a positive significance argument (1) in cell G10.

FLOOR Example 4-6

FLOOR Function in excel reverses the negative number away from zero, i.e. it returns an output value as -24 (More negative value) in cell H10.

FLOOR Example 4-7

Things to Remember

The FLOOR Function in excel performs rounding based on the below mentioned or following rules:

  • If the number argument is equal to an exact multiple of significance, no rounding occurs.

FLOOR Example 5-1

The Output will be :

FLOOR Example 5-2

  • If either argument ((number, significance) is non-numeric (Text value or string), the FLOOR function returns an #VALUE! error.

FLOOR Example 5-3

The Output will be :

FLOOR Example 5-4

  • If “number” or “significance” argument have different signs (e.g. #, %,?), FLOOR function returns an #VALUE! error.

FLOOR Example 5-5

The Output will be :

VALUE Error Example 5-6

  • The significance & number argument must have the same arithmetic sign (positive or negative); otherwise, if they have different arithmetic signs.

FLOOR Example 5-7

The Floor function returns or results in an #NUM error value.

NUM Error Example 5-8

  • Exceptional case or scenario, In current versions of Excel 2016 or (2010 and later), a negative number argument (-23.75) in cell F10 and a positive significance argument (1) in cell G10 Floor function reverses the negative number away from zero, i.e. -24 in cell H10, if you try this it in Excel 2007 & earlier versions, it returns a value error (#NUM! error value)

FLOOR Example 5-9

The Output will be :

FLOOR Example 5-10

  • If the number argument is a positive value and the significance argument is “0” or ZERO, it returns #DIV/0! Error.

FLOOR Example 5-11

The Output will be :

DIV Error Example 5-12

Recommended Articles

This has been a guide to FLOOR in Excel. Here we discuss the FLOOR Formula in Excel and How to use the FLOOR Function in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. XIRR In Excel
  2. NOT in Excel
  3. Write Formula in Excel
  4. Excel Text with Formula
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
5 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 FLOOR Function Excel Template

EDUCBA

Download FLOOR Function Excel Template

EDUCBA

डाउनलोड FLOOR 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