Floor Function

Today in this article I will discuss with you about Floor Function which is categorized as Math function  in Microsoft Excel.

Let us first understand what Floor function does !!!

In simple words it rounds down a number, your next question is what is the difference between ROUND and FLOOR function or what is the difference between ROUNDDOWN and FLOOR function.

The difference is FLOOR Function rounds a number down, towards zero, to the nearest multiple of 'Significance'.

It seems a little bit complicated but trust me after reading this article you will be able to understand and use the Floor function. Let us first understand the Syntax of this function.

Syntax

The syntax for the FLOOR function in Microsoft Excel is,

=FLOOR( number, significance )

Parameters or Arguments

number

The number that you wish to round down.

significance

The multiple of significance that you wish to round a number to.

Example:

We will use an example to understand FLOOR function.

Suppose, we have a list of a sales team with their monthly sales. Each sales representative is allotted with the incentive price for every $1000 sales which is 5% of the relevant sales amount, now we need to calculate the incentive amount that will be paid to the representative as an incentive at the end of the month.

So if we tabulate this problem, it will be like below.

How to use Floor Function in Excel

To find the relevant sales which have to be the nearest multiple of 1000, we will use the FLOOR function in excel with a nearest factor of 1000.

The formula will be : =FLOOR(C3,1000)

How to use Floor Function in Excel

The first part of the problem is done, now we will also calculate the second part of the said problem by simply multiplying it by 5%.

I think you have fully understood where and how to use floor function, in the above example we used FLOOR function in excel to deal with significant value and it is also useful in calculations for currency conversions, discounts.

How to use Floor Function in Excel

Using FLOOR function in excel, we can round the time values to the nearest time interval. The process is same, you need to put the time in significant parameter.

How to use Floor Function in Excel to calculate Time

Note :

v If the number to be rounded is a positive number, the FLOOR function will round the value towards zero, that is it will lower the value of the number as much possible to the nearest significant factor.

v If the number is a negative number the FLOOR function will round the value away from the zero.

v If the number is the exact multiple of the significant value, there will be no rounding of the number and FLOOR Function will return the same value.

v The FLOOR function in Excel throws #NUM! error, when the number is positive and significance is a negative value, it throws #DIV/0! Error when the significant value is 0 because the function iterates the value by dividing the multiple until it gets the lowest value that is 0 and divisibility by 0 means error and the Excel FLOOR function also throws an error when either of the argument is not numeric.

v In the previous version of Excel (2003 and 2007), the number and the significant values should have the same sign, else the excel FLOOR function would return an error, however, this limitation of FLOOR function has been improvised in the latest version of Excel (2010 and later), now it can round off a negative number with a positive significance.

Hope you have enjoyed ...

Keep reading, sharing & Stay blessed ...

Insert or Embed Excel File In Word, Excel Or PowerPoint Presentation - As Object

Today in this article I will discuss with you how to Embed Excel (as Object) in office tools, like Microsoft Word, Microsoft PowerPoint and in Microsoft Excel itself.

What is Embedding ?

Excel Object Linking and Embedding - Object Linking and Embedding (or OLE for short) is a technique used to insert data from one programme into another.

So what do I mean to say by 'Excel as Embedded Object' ?

This questions may arise in your mind, to understand what is 'Excel as Embedded Object' suppose you are in a meeting where you need to give a speech about your opinion or performance with the supportive data. The two things you required

1.   Power Point Presentation and

2.   Excel workbook with data

Now in an ongoing discussion/meeting, if your data in Excel need to display then you must minimize PowerPoint presentation and then you have to find out the specific folder where your excel is stored and then you open it to display the data.

Now, in another situation let suppose you need to email your presentation with the supporting Excel file you have to attach two files (*.ppt or *.xls).

Or suppose you need to update the Excel data and to be displayed in PowerPoint presentation then also you have to update and copy the data then paste this data in PowerPoint slide.

Using embedded object you can shorten this process and can accomplish the task in seconds.

Embed is a process where we insert a file or data from one application to another application, e.g., attach or insert an Excel file or data in PowerPoint presentation or Word file or in Excel.

You may think that you can put a link and that will also do the same job, i.e., it will also refer to the file too.

You are right but in Embedding, if you update your excel file it will easily change the data in the PowerPoint presentation.

Let us learn how to insert Excel in PowerPoint presentation and Microsoft Word.

Excel Workbook Embedded in Microsoft Word

How to insert Excel in Excel ?

Steps :

1.   On the Insert tab, in the Text group, click Object.

Object Menu in Excel

2.   In the Insert Object dialog box, select Create from file.

Select from File

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

Select from File - Display as icon

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Embedded Excel Displaying as icon

Save this workbook.

Now whenever you double click on this ICON, it will open this workbook.

Note : The important thing to be noticed is that whenever you send this or share this Excel workbook, the attached or inserted Excel will also be shared.

How to insert or Embed Excel in Microsoft Word ?

Steps :

Same Steps to be followed,

1.   On the Insert tab, in the Text group, click Object.

Object Menu in Word

2.   In the Insert Object dialog box, select Create from file.

Select from File - MS Word

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Excel file Embedded in MS Word

How to insert or Embed Excel in Microsoft PowerPoint ?

Steps :

Steps are same,

1.   On the Insert tab, in the Text group, click Object.

Object Menu in PowerPoint Presentation

2.   In the Insert Object dialog box, select Create from file.

Select from file - PowerPoint Presentation

3.   Click Browse, and in the Browse dialog box, browse to and then select the Excel workbook that contains the Excel file that you want to insert, and then click OK.

4.   In the Insert Object dialog box, select 'Display as icon' and then click OK.

Excel file embedded in PowerPoint Presentation

You might noticed that the process is same to embed Excel file (it can be any file, pdf, text etc.) in any office tool.

You might also noticed that I always used the option 'Display as icon' not the other option 'Link' or 'Link to a file'.

Let us understand what will happen if we use the 'Link' option.

Embed Excel in PowerPoint using Link to file.

I have done all the steps mentioned above and embedded the Excel file as Linked in PowerPoint and it is displayed as below.

Excel file embedded as LINK in PowerPoint Presentation

Link Excel file to PowerPoint Presentation

The existing table in the excel is displayed as 'table' in PowerPoint presentation, another noticeable thing is that if you double click this table this will activate the Excel and now if you update this table it will be updated in PowerPoint presentation also. (I am using Office 2007 so it will open the Excel workbook but in Office 2013 and after 2013) you can edit the table inside the PowerPoint presentation)

Link Excel file to Microsoft Word

The whole table will be embedded in the word file as 'Table' and you can also edit the excel inside the MS Word file. Please follow the below picture.

Excel file embedded in Word as LINK

Link Excel file to Excel

The Excel table will be displays as OBJECT in excel file. Please follow the below picture.

Excel file embedded in Excel as LINK

Hope you have enjoyed ... Please comment if you have any question or doubts in your mind...

Keep reading & Sharing

Stay blessed ...