MOD Function Reminder of a Division

So far we discussed about how to get the INTEGER portion from a division.

To get the INTEGER portion from a division this, we used QUOTIENT function and INTEGER function.

In this article, we will discuss How to get the reminder part of a division in Excel.

Microsoft Excel provides MOD Function. MOD function is very simple and very easy to use.

Let’s discuss it.

Description

The Microsoft Excel MOD function returns the remainder after a number is divided by a divisor.

Note : The basic mathematics says that if we try to divide any number by 0 (zero), the result will be an indeterminate form.

Therefore, if the divisor is 0, then the MOD function will return the #DIV/0! error.

The MOD function is categorized as a Math/Trig Function and can be entered as part of a formula in a cell of a worksheet, i.e., as nested function.

Syntax

The syntax for the MOD function in Microsoft Excel is,

=MOD(number, divisor)

Parameters or Arguments

number

A numeric value whose remainder you wish to find.

divisor

The number used to divide into the number parameter. If the divisor is 0, then it will return the #DIV/0! error.

Returns

The MOD function returns a numeric value.

If the divisor is 0, then the MOD function will return the #DIV/0! error.

Example :

Suppose we need to get the reminder part of a division, the divisor is 5 and the dividend is 17.

The formula will be =MOD(17,5) or you can give cell reference as the below image shows.

Please look at the below image, which displays how to use MOD function.

How to use MOD Function in Excel

Please note :

1.   QUOTIENT Function is used to get the Integer portion of a division.

2.   Whereas MOD Function is used to get the Reminder part of a division.

3.   The division result without reminder is simply use a ‘/’, without quotes in a formula.

I hope you have enjoyed this small article. Take care…

Thank you for reading…

Integer portion of a division – INT Function – The Problem & Solution

In my last article, I had discussed about Quotient Function in Microsoft Excel to get the integer portion of a division.

But there is another function which can also give the same result if applied, INT function.

But there is differences between these two, therefore, please take caution before applying INT function to get the integer portion of a number which we will discuss at the end of this article.

Let us first discuss INT function.

Description

The Microsoft Excel INT function returns the integer portion of a number.

Syntax

The syntax for the INT function in Microsoft Excel is very simple

=INT(expression)

Parameters or Arguments

expression

A numeric expression whose integer portion is returned. You nay directly enter numbers with decimal or you can refer cells e.g., for division, =INT(A2/A3).

Note :

If the expression is negative, the INT function will return the first negative number that is less than or equal to the expression.

Returns

The INT function returns an integer value.

Example :

=INT(65.35)   Or    =INT(5/2)   or   =INT(any formula nested)

As the name suggest generally it is used to get the integer portion of a number, the result is same as QUOTIENT function, but there is a technical difference between INT & QUOTIENT function.

INT function rounded down the number, e.g.,

=INT(55.70) the result will be 55 and if you enter =INT(55.39) 

the result will be same as QUOTIENT function.

But what if the number is a NEGATIVE number ?

The result will be wrong. As INT function, will round down the number and rounding down for negative number will increase the Integer portion of a number.

INT Function & QUOTIENT Function

Therefore, if you need to get the integer portion from result of a division, always use QUOTIENT Function.

Please note : INT will return wrong integer if the number is negative.

I hope you have enjoyed this small article. Take care…

Thank you for reading….

6 Reasons why HLOOKUP not working - Solved

HLOOKUP not working !!!

Don’t need to get panic. 

I know you have tried a lot but all in vain. 

Calm down, there are very few reasons behind not working of HLOOKUP function.

Don’t hurry. 

First understand why HLOOKUP is not working, therefore, we will dig deeper to solve the problem permanently.

In this article, I will discuss with you how to trace the possible errors while implementing HLOOKUP and how to rectify these errors.

But before going I would like to review the SYNTAX of HLOOKUP once again.

It’s very like VLOOKUP, technically only one change is in the syntax and it is ROW_INDEX_NUMBER in place of COL_INDEX_NUMBER in VLOOKUP in the third parameter of HLOOKUP function.

We all know the ‘H’ signifies Horizontal in HLOOKUP, and ‘V’ for Vertical in VLOOKUP.

Lets review the syntax again. For more details, you may read this article.

SYNTAX

=HLOOKUP( lookup_value, table or array, row_index_number, [range_lookup] )

Note :- Please note our brain is trained to lookup vertically, from left to right – generally in daily life we compare or match column wise in VLOOKUP.

On the contrary, in HLOOKUP we match ROW wise, the opposite of VLOOKUP.

Therefore, the most important thing is to change our mind setup from left to right to up and down. This is how HLOOKUP works.

Below is the few major Check List to check why HLOOKUP is not working,.

1.               Not Having Lookup_Value in First Row :

If LOOKUP VALUE (which you are searching for, the first parameter) is not available in the table or array from where you are getting or matching data or source table, you will get #N/A error.

Please watch carefully the below image. Here I want to match the employee with the Sales Quantity in the small table from the large table. Here I am getting #N/A error because “Employee 14” is not present in the first table.

HLOOKUP not working

This is a small example, therefore, what you need to do is search manually for the Lookup Value by pressing Ctrl+F in your Keyboard.

If Value is not found, then it’s OK, use IFERROR to mask the #N/A error.

2.               Counting the Wrong Number of Rows for ‘Row_index_number’ parameter

Please recount the ROW NUMBER from where you select the rows in the first table from where you are fetching data.  Remember this count will be from TOP to BOTTOM unlike in VLOOKUP from left to right.

Note : Counting ROW Number starts from ‘1’ and  include the very first column as shown in the picture.

In this example I wrongly count the column number as 4 instead of 3.

=HLOOKUP(E11,B3:J5,4,0)

The error will be #REF error.

HLOOKUP not working

3.               Extra Space or Characters

Please check for Extra spaces in your formula, if you have doubt that there might be extra space, use TRIM function to remove extra spaces.

To learn more about TRIM,click here.

4.               Not Using ‘FALSE’ for Exact Matching [Range_Lookup]

Although, sometimes HLOOKUP works perfectly but do not forget to use [Range_Lookup], False or 0 for exact matching and True or 1 for approximate matching. It’s always better to make a good habit.

5.               Forgetting Absolute References (pressing F4 can do this) When Copying the Formula

While copy, and paste HLOOKUP formula or just dragging you must put $ sign to restrict the column not to move down or side by side. This is called Absolute reference.

There are two ways to do this, one you insert a $ sign manually or you can use F4 (Function Key) key.

HLOOKUP not working

6.               A new row has been inserted in the old table where HLOOKUP was working

In a previously calculated table where HLOOKUP has been used and now inserting a new row will create HLOOKUP unstable.

To resolve this either you can recheck your HLOOKUP formula or you can use an additional function MATCH to be nested with HLOOKUP. Using MATCH with HLOOKUP will make your formula very dynamic.

Please click here to learn how to useMATCH function with HLOOKUP.

I hope you have enjoyed this article. Take care…

Thank you for reading….

Integer portion of a division – Quotient Function

In this article, I will discuss about Quotient Function in Microsoft Excel to get the integer portion of a division.

As we all know, there are three parts in a division and in a division, the dividend (Numerator) is divided by the divisor (Denominator) to get a quotient.

Parts of a Division

Parts of a Division

Description

The Microsoft Excel QUOTIENT function returns the quotient or the integer part of a division.

Syntax

The syntax for the QUOTIENT function in Microsoft Excel is very simple

= QUOTIENT (numerator, denominator)

Parameters or Arguments

numerator

The dividend.

denominator

The divisor.

Returns

The QUOTIENT function returns the integer part of a division.

Example :

The below image will clarify Quotient function very well. This function is very easy to implement.

How to Use Quotient Function in Excel 

I hope you have enjoyed this small article. Take care…

Thank you for reading….

REPT Function

In this article, I will discuss about a very small function in Microsoft Excel, REPT function. Although REPT function is used very rarely in Worksheets but it is very useful in Excel VBA or Excel Macro.

Most probably REPT is derived from Repeat word, so we can easily understand that this function has the capability to repeat some data.

We will use this function in Excel Macro in our upcoming articles to repeat some specific data.

Description

The Microsoft Excel REPT function returns a repeated text value a specified number of times.

It can print the numbers and special characters also.

Syntax

The syntax for the REPT function in Microsoft Excel is very simple

=REPT( text, number )

Parameters or Arguments

text

The text or number or special character to repeat.

number

The number of times to repeat the text value.

Returns

The REPT function returns a string/text value.

Example :

The below image will clarify this tiny function very well.

How to Use REPT function to repeat texts

I hope you have enjoyed this small article. Take care…

Thank you for reading….