Share in Facebook


13 November 2018

MOD Function Reminder of a Division


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


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

05 November 2018

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

20 October 2018

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


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








I hope you have enjoyed this article. Take care…
Thank you for reading….

17 October 2018

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



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
How to Use Quotient Function in Excel 










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

04 October 2018

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
How to Use REPT function to repeat texts


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

05 September 2018

XOR Function


Today I will discuss about the logical ‘XOR’ function in Microsoft Excel in this article in detail.











XOR – Exclusive OR function, although not used widely in Microsoft Excel but sometimes using this function can reduce lot of nested functions, thus eliminating the complex nested formula by single formula.

XOR Function in Microsoft Excel
XOR Function in Microsoft Excel



Where to use XOR Function?

This function is little complicate to understand at first, therefore, we will discuss it using an example first then we will discuss it in detail. Suppose, we need to derive the winning teams from a basketball competition league and the statistics of wining and runners up teams are as below image.









XOR Function in Microsoft Excel
XOR Function in Microsoft Excel


The team who has won the maximum number of matches will qualify for the next level. Therefore, we should find the team with maximum number of 'WON' word from the five round of competition and declare that team as the Qualified for the next game.

It can be done in other ways, e.g., use COUNTIFS function to count the number of ‘WON’ statement in a row and then declare that team as Qualified if the occurrence of WON word is 3 or 4 or 5.

But the easiest way to do it is using XOR function. Those who are little familiar with programming or logic gate in computer components are much comfortable with this concept but there is no harm in reading again.

Let us discuss this function in details.

XOR returns TRUE if either statement is TRUE, but returns FALSE if both statements are TRUE. If neither is TRUE, XOR also returns FALSE.


XOR Function in Microsoft Excel
XOR Function in Microsoft Excel

Return value

TRUE or FALSE

Syntax

=XOR (logical1, [logical2], ...)

Parameters or Arguments

logical1 - An expression, constant, or reference that evaluates to TRUE or FALSE.

logical2 - [optional] An expression, constant, or reference that evaluates to TRUE or FALSE.


What will be the return type of XOR function?


There will be two outputs, either TRUE or FALSE.

If two conditions are TRUE, XOR will return FALSE

If two conditions are FALSE, XOR will return FALSE

Otherwise XOR will return TRUE. 

These above rules are for such condition where there are TWO conditions given.

Please note carefully the below image, the output is TRUE when  two conditions' results are TRUE & FALSE respectively and the output is FALSE when there are results are TRUE & TRUE or FALSE & FALSE.

XOR Function in Microsoft Excel
XOR Function in Microsoft Excel



How to use XOR function in multiple conditions?

When there are multiple conditions the output of XOR function will be TRUE only WHEN THE NUMBER OF TRUE CONDITIONS ARE ODD otherwise the output will be FALSE i.e., if the number of FALSE CONDITIONS ARE EVEN the XOR output will be FALSE.

Please follow the below image which will clarify this situation easily.

Using XOR Function in Multiple Conditions in Microsoft Excel
Using XOR Function in Multiple Conditions in Microsoft Excel


I hope you have enjoyed this article. Take care…
Thank you for reading….

23 August 2018

OR Function


Like ‘AND’ logical function ‘OR’ function is also widely used in Microsoft Excel with other Logical functions like ‘IF’, ‘COUNTIF’, ‘SUMIF’ ‘AND’ etc., as well as other functions.Today I will discuss about the logical OR function in this article.









To understand OR function in Microsoft Excel let suppose I give you a funnel with a filter in it which can filter the return type of any condition.

Now, you pour all the condition to be tested (like, 100<90, 100 is less than 90) in this funnel and if ANY CONDITION return ‘TRUE’ then ‘OR’ function will return TRUE.

Let have another example to understand this function, suppose we have following data to be checked whether the return type is TRUE or FALSE.


Ø 90 < 75            à FALSE Statement
Ø 120 < 100        à FALSE Statement
Ø 75 < 200          à TRUE Statement
Ø 55 < 24            à FALSE Statement
Ø 34 < 23            à FALSE Statement
Ø 27 > 72            à FALSE Statement

From the above 6 statements we can understand that only ONE condition is TRUE, rest conditions are FALSE.
Therefore, ‘OR’ function will return TRUE.

How to use OR Function
How OR Function Works


Now let us discuss OR function in details.

Description

The Microsoft Excel OR function returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE.

Syntax

The syntax for the OR function in Microsoft Excel is











=OR(logical1, [logical2...])

Note : Here logical means Conditions to be checked.

Parameters or Arguments

logical1

A condition to test that can either be TRUE or FALSE.

Logical2...

Conditions to test that can either be TRUE or FALSE. There can be up to 30 conditions.

Returns

The OR function returns TRUE if any of the conditions are TRUE.
The OR function returns FALSE if all conditions are FALSE.

Difference Between logical AND function & OR function

The syntax of both the function is almost same,

=OR(logical1, [logical2...])

=AND(logical1, [logical2...])

AND function returns TRUE when ALL the conditions are TRUE

on the contrary 

OR function returns TRUE when ANY ONE of the conditions is TRUE

I hope you have enjoyed this article. Take care…
Thank you for reading….