RANDBETWEEN Function

In this article I will discuss about RANDBETWEEN Function which returns a new random number but these numbers get changed each time your spreadsheet recalculates.

What is meant by 'spreadsheet recalculates' ?

Every time you enter something or rectify your formula or data spreadsheet gets refreshed. This can better be understood after using this function.

So RANDBETWEEN Function generates random numbers between the specified ranges.

Description:

The Microsoft Excel RANDBETWEEN function returns a random number that is between a bottom and top range. You need to specify TOP and BOTTOM numbers.

Syntax:

The syntax for the RANDBETWEEN function in Microsoft Excel is simple

=RANDBETWEEN( bottom, top )

Parameters or Arguments

bottom

The smallest integer value that the function will return.

top

The largest integer value that the function will return.

Things to Remember

1.   RANDBETWEEN function will recalculate whenever worksheet is refreshed, therefore, if you have generated random numbers using RANDBETWEEN function, please use paste special to prevent changing these numbers whenever excel workbook or worksheet refreshes.

2.   These numbers are integer type, RANDBETWEEN function generates integer type output. In my next article I will show you how to generate decimal random numbers.

Example:

I have used BOTTOM number as 100 and TOP number as 999 to generate random integer numbers between 100 to 999.

How to use RANDBETWEEN Function in Excel

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...

MROUND Function

We have discussed CEILING & FLOOR function with significance, in this article we will discuss about MROUND function to round a number to a given numbers' multiplication.

Description :

The Excel MROUND function returns a number rounded to a given multiple. MROUND will round a number up or down, depending on the nearest multiple.

Syntax :

=MROUND (number, multiple)

Arguments :

number - Its a number that should be rounded up or down.

multiple - Its a number which is used as 'multiple' when rounding.

Few Things to Remember :

1.   MROUND rounds up, away from zero.

2.   The rounding occurs when the remainder left from dividing number by multiple is greater than or equal to half the value of multiple.

3.   Number and multiple must have the same sign.

Difference Between CEILING and MROUND Function

CEILING, rounds a number up, to the nearest integer or nearest multiple of significance, it can only be away from Zero;

whereas, MROUND, rounds up or down a number to the specific multiplier.

Below examples will clarify the difference between MROUND and CEILING function.

Example :

In this example we will use different Sales Amount and will fix the second parameter as $ 1000 for both MROUND and CEILING function.

Our formula will be for MROUND =MROUND(C3, 1000)

and

=CEILING(C3, 1000) for CEILING function.

If you notice carefully, you will find that CEILING function is giving output as $ 1000, irrespective of the input number (150, 499, 501 & 999) but MROUND shows $ 1000 as output only when the number is greater than or equal to $ 500.

This means that, CEILING function rounds up a number away from ZERO in multiplication of the provided significance.

And, MROUND function rounds a number up or down to the specific multiplier.

In this case if we use 500 as multiplier in MROUND function it will display $ 500 if the number provided is 499 (=MROUND(C4, 500)).

MROUND Function in Excel

Difference between MROUND  and CEILING Function in Excel

Hope you have enjoyed this article ...

Keep reading, sharing & Stay blessed ...

Pivot Table From Single or Multiple Pivot Table

Pivot of a Pivot ? Is it possible in Microsoft Excel ?

Yes, it's possible in Excel even you can create Pivot Table from multiple Pivot Tables.

In this article, we will discuss how to create Pivot Table from another Pivot Table as data source.

We will also discuss how to create Pivot Table from multiple Pivot Table as data sources.

To get PivotTable from another PivotTable as source, we need to use PivotTable and PivotTable Chart Wizard.

 PivotTable and PivotTable Chart Wizard

Microsoft Excel 2007/2010/2013/2016/2019 hides the PivotChart Wizard, which is not shown in the Ribbon.

Without Classic Menu for Excel installed, you will not find it. 

To use 'PivotTable and PivotChart Wizard' as menu item, there still are several ways to configure it.

Excel also provided us shortcut to get this wizard, Alt+D+P.

If you press these keys combined, a new window will appear, the PivotTable and PivotChart Wizard.

The problem with this shortcut is that every time you need PivotTable and PivotChart Wizard you must press these key combination.

The permanent solution is to get it somewhere in the menu. Therefore, lets first learn how to get PivotTable and PivotChart Wizard in the menu or as a button permanently.

Steps to configure the Wizard :

1.   Click File / Office Tab, and click “Options” item.

2.   Click the Customize Ribbon / Excel Options.

3.   In the Excel Options window, you will see Customize the Ribbon or Customize option.

4.   Choose Commands Not in the Ribbon or All Commands by clicking the arrow, and then use the scroll bar to search for the PivotTable and PivotChart Wizard.

5.   When finally find out the Wizard, click Add to add it into the Ribbon / Quick Action Toolbar, and then click OK to finish the step.

The images are shown Step by step.

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Excel Option to Create PivotTable and PivotTable Chart Wizard

Now, as we have configured the PivotTable wizard successfully, we can use this wizard to create PivotTable from another PivotTable as data source.

Steps :

1.   Select any blank cell in the Sheet

2.   Use the button in the Quick Access Toolbar or alternatively you can use Alt+D+P shortcut to start the PivotTable and PivotChart Wizard.

Quick Access Toolbar Button - PivotTable and PivotTable Chart Wizard

PivotTable and PivotTable Chart Wizard Step 1

3.   You will notice that multiple options are visible and the top option 'Microsoft Office Excel list or Database' is selected.

4.   Click 'Next', it will ask you to select the range, select the range from the existing PivotTable and click 'Next'.

PivotTable and PivotTable Chart Wizard Step 2

5.   The third and last step is to select the location of the new PivotTable either in Existing Worksheet or in a New Worksheet.

PivotTable and PivotTable Chart Wizard Step 3

6.   Click 'OK' and you will get your PivotTable using previous PivotTable as data source for the new PivotTable.

Example :

I am using Excel 2007 and in Excel I have inserted a PivotTable source is from a data table in this Excel Workbook which simply shows Day wise Date wise Target of various products.

To understand it better I have given a simple image.

PivotTable from another PivotTable as Data Source

PivotTable from Multiple PivotTables

So you learnt how to create PivotTable from another PivotTable, the second question comes in mind, How to create  PivotTable from Multiple PivotTables ?

1.   It is very simple, notice the PivotTable and PivotChart Wizard's third option, "Multiple consolidation ranges", click the radio button to select this option and click 'Next'.

PivotTable from Multiple PivotTable

2.   The next step will ask you to select 'Create a single page field for me' and 'I will create the page fields', select the first option, 'Create a single page field for me', click 'Next'.

PivotTable from Multiple PivotTable

3.   Now, in this step Excel wants to take input "Where are the worksheet ranges that you want to consolidate?"

        a) Select the first range i.e., from first PivotTable and click 'Add' which will add the location of the PivotTable in the below box

        b) Again select the second PivotTable and click 'Add'.

PivotTable from Multiple PivotTable

4.   Now, choose the location where you want to insert your new combined PivotTable, click 'Finish'.

PivotTable from Multiple PivotTable

Now your data is ready.

Example of PivotTable from Multiple PivotTable

Note : A report filter is automatically generated in this PivotTable which if selected will display data from the specific locations.

Hope you have enjoyed reading this article ...

Keep reading, sharing & Stay blessed ...

Ceiling Function

In this article I will discuss about CEILING function, another Mathematical function in Microsoft Excel.

In my last article we have discussed about Floor Function which is used to round down a number in respect to given significance. 

For example, suppose Angelina Jolie earns $44,019 per day and we need to provide another incentive value on this daily earnings which will be calculated on the multiplication of $1000  rounded down (which is 'significance' in Excel).

Here we need to use Floor function and the formula will be =FLOOR(C9,1000). (Where C9 cell contains $44,019).

Which means we have rounded down the value towards zero / '0'.

Now if we need to calculate the same incentive value but in rounded up i.e., away from zero and in multiplication of $1000 (significance), then we must use CEILING Function.

We will use Ceiling function in Excel in this article but before that let us first understand the syntax of Ceiling function.

How to use Ceiling Function ?

Syntax

The syntax for the CEILING function in Microsoft Excel is same as Floor function,

=CEILING(number, significance)

Parameters or Arguments

number

The number that you wish to round up.

Significance

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

Example :

We will use an example to understand how to use CEILING 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 but the incentive will be on higher / rounded up of sales value.

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

How to use Ceiling Function in Excel

To find the relevant sales which have to be the farthest multiple of 1000 (away from zero), we will use the CEILING function in excel with a nearest factor of 1000.

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

How to use Ceiling 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%.

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

How to use Ceiling Function in Excel

Note : If you notice carefully you will discover that in FLOOR function the result is lower than the actual value and in CEILING  function the value gets increased to reach the significance.

Hope you have enjoyed reading this article ...

Keep reading, sharing & Stay blessed ...

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 ...