Excel Mod - Free Excel Tutorial

March 26, 2023March 26, 2023

How to Convert Julian Date to a Calendar date in Excel

This post will guide you how to convert Julian date to a calendar date in Excel. How do I convert a given Julian Date to a calendar date with a formula or VBA Code in Excel.

1. What is Julian Date?

Julian day number is the integer assigned to a whole solar day in the Julian day count starting from noon Universal time, with Julian day number 0 assigned to the day starting at noon on Monday, January 1, 4713 BC, proleptic Julian calendar (November 24, 4714 BC, in the proleptic Gregorian calendar), a date at which three multi-year cycles started (which are: Indiction, Solar, and Lunar cycles) and which preceded any dates in recorded history.

2. Convert Julian Date to Calendar date Using Formula

If you want to convert Julian date to a calendar date for your date values, and you can use a formula based on the IF function, the LEFT function and the MOD function. Like this:

=("1/1/"&(IF(LEFT(B2,2)*1<20,2000,1900)+LEFT(B2,2)))+MOD(B2,1000)-1

Type this formula into cell c2, and press Enter key on your keyboard to apply this formula.

Then go to HOME tab, select Date as Number Format in the Number group.

Keep to select the Cell C2, and drag the AutoFill Handle down to other cells to apply this formula to convert the Julian date format into Calendar date.

3. Convert Julian Date to Calendar Date with VBA Code

You can also use a User Defined function with VBA Code to convert Julian date to a Calendar date in Excel. just do the following steps:

Step1: open the VBA editor in Excel by pressing Alt + F11

Step2: Insert a new module and paste the below code into the module.

How to Convert Julian Date to a Calendar date vba 3.png

Function JulianToCalendar_ExcelHow(julianDate As Double) As Date
    Dim year As Integer
    year = IIf(Left(julianDate, 2) < 20, 2000, 1900) + Left(julianDate, 2)
    JulianToCalendar_ExcelHow = DateSerial(year, 1, 1) + (julianDate Mod 1000) - 1
End Function

Step3: you can use the formula in a blank cell to convert it to a calendar date.

=JulianToCalendar_ExcelHow(B2)

How to Convert Julian Date to a Calendar date vba 4.png

This function takes a Julian date as input (in the format of a double) and returns a calendar date (in the format of a Date object).

4. Convert calendar date to Julian date using Formula

To convert a calendar date to a Julian date in Excel, you can use the following formula:

=TEXT(C2,"yy")&TEXT((C2-DATEVALUE("1/1/"&TEXT(C2,"yy"))+1),"000")

where C2 is the cell containing the calendar date you want to convert.

How to Convert Julian Date to a Calendar date 10.png

Let’s See How this Formula Works:

The TEXT function is used to convert the year portion of the date to a two-digit number in YY format. The formula TEXT(C2,”yy”) extracts the last two digits of the year from the date in cell C2.
The DATEVALUE function is used to convert a text string in the format “1/1/YY” to a date value. The formula DATEVALUE(“1/1/”&TEXT(C2,”yy”)) returns the date value for January 1 of the same year as the date in cell C2.
Subtracting the January 1 date value from the date value in cell C2 gives the number of days between January 1 and the date in cell C2.
Adding 1 to this number gives the day of the year for the date in cell C2.
The TEXT function is used again to format the day of the year as a three-digit number with leading zeros. The formula TEXT((C2-DATEVALUE(“1/1/”&TEXT(C2,”yy”))+1),”000″) returns the day of the year in DDD format.

5. Convert calendar date to Julian date with VBA Code

You can create a User Defined function with VBA Code to convert a calendar date to a Julian date in Excel, just do the following steps:

Step1: open the VBA editor in Excel by pressing Alt + F11

Step2: Insert a new module and paste the below code into the module.

How to Convert Julian Date to a Calendar date vba 1.png

Function ConvertToJulianDate_ExcelHow(myDate As Date) As String
    Dim myYear As String
    Dim julianDay As String
    
    myYear = Format(myDate, "yy")
    julianDay = Format(myDate - DateSerial(year(myDate), 1, 1) + 1, "000")
    
    ConvertToJulianDate_ExcelHow = myYear & julianDay
End Function

Step3: You can then call the function from a worksheet cell or from another VBA macro.

For example, to convert the date in cell C2 to a Julian date in cell E2, you could use the formula in cell E2.

=ConvertToJulianDate_ExcelHow(C2)

How to Convert Julian Date to a Calendar date vba 2.png

The function calculates the Julian date by extracting the year from the calendar date using the Format function, and then subtracting the date of January 1 of that year from the calendar date, adding 1 to get the number of days since January 1, and then formatting the result as a 3-digit number using the Format function.

6. Video: Convert between Julian date and Calendar Date in Excel

In this video, you will learn how to convert between Julian dates and calendar dates in Excel using both formulas and VBA code.

7. Related Functions

Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel LEFT function
The Excel LEFT function returns a substring (a specified number of the characters) from a text string, starting from the leftmost character.The LEFT function is a build-in function in Microsoft Excel and it is categorized as a Text Function.The syntax of the LEFT function is as below:= LEFT(text,[num_chars])…t)…

March 12, 2023March 12, 2023

How to Check If a Number is Integer in Excel

This post will guide you on how to check if a number is an integer in Excel using different methods. Excel provides built-in functions such as the INT and MOD functions that you can use to determine if a number is an integer.

Additionally, you can create a user-defined function using VBA code to check if a number is an integer. By using these methods, you can easily determine if a number is an integer and use it in your calculations or data analysis in Excel.

1. Check If Value is Integer Using INT Function

Assuming that you have a list of data in range B1:B5, in which contain numeric values. And you want to test each cell value if it is an integer, if true, returns TRUE, otherwise, returns FALSE. How can I do it. You can use a formula based on the INT function to achieve the result. Like this:

=INT(B1)=B1

Type this formula into a blank cell, such as: Cell C1, and press Enter key on your keyboard. Then copy this formula from cell C1 to range C2:C5 to apply this formula to check values.

Let’s see how this formula works:

The INT function try to extract the integer portion from a given cell value, if the returned value is equal to the default cell value, it indicated that numeric value is an integer.

2. Check If a Number is Integer Using MOD Function

You can also use the MOD function in combination with IF function to check if a cell value is integer in Microsoft Excel Spreadsheet. Just use the following MOD formula:

=IF(MOD(B1,1)=0, "TRUE", "FALSE ")

Select a cell where you want to display the result, then enter this formula, press Enter.

How to Check If a Number is Integer in Excel 10.png

The MOD function in Excel is used to return the remainder after dividing one number by another. If a number is divided by 1 and the remainder is 0, then the number is an integer.

3. Check If a Number is Integer with User Defined Function (VBA Code)

You can create a user-defined function in VBA (Visual Basic for Applications) to check if a number is an integer in Excel. Here’s how you can do it:

Step1: Open Excel and press Alt + F11 to open the Visual Basic Editor.

Step2: In the editor, click on Insert > Module to create a new module.

Step3: Type the following code into the module:

How to Check If a Number is Integer in Excel 11.png

Function IsInteger_ExcelHow(ByVal num As Double) As Boolean
    If num = Int(num) Then
        IsInteger_ExcelHow = True
    Else
        IsInteger_ExcelHow = False
    End If
End Function

Step4: Save the module and return to the Excel worksheet.

Step5: In the cell where you want to display the result, enter the formula:

 =IsInteger_ExcelHow(B1)

where B1 is the cell containing the number you want to check.

How to Check If a Number is Integer in Excel 12.png

If the number in cell B1 is an integer, the formula will return TRUE. If it is not an integer, the formula will return FALSE.

4. Video: Check If Cell Value is Integer

This video will guide you how to check if a number is integer in Cells in Excel with different methods.

5. Related Functions

Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…

May 4, 2022March 12, 2023

Working Time Calculation Based on Timesheets

In the office, a special machine record the time when you start working (clock in office) and when you finish working (clock out of office). We can calculate the total working time by subtracting the end working time from the start working time.

If we also take out the lunch break, we have to subtract the lunch break from the total working time we get. See example below, we calculate effective working hours with the help of Excel MOD function, launch break is “thrown away”.

Working Hours Calculation Based on Clock1

Notes:

Before creating the formula to calculate the working time, we should first set the number format to “Time”.

FORMULA

In this example, the formula is:

=MOD(C3-B3,1)-MOD(E3-D3,1)

EXPLANATION

Here, you may wonder why we use the MOD function instead of just using the simple addition and subtraction operations. Firstly, let’s introduce Excel MOD function.

MOD function lists in Excel Formulas->Math & Trig functions.

MOD function returns the remainder of two numbers in a division operation.

Syntax:

=MOD (number, divisor)

For example, MOD(5,3)=2.

In the first MOD function:

#number: C3-B3

Subtract the start working time from the end working time, then we can get “total working hours”.

#divisor: 1

In the second MOD function:

#number: E3-D3

Subtract the start of the lunch break from the end of the lunch break to get “lunch break time”.

#divisor: 1

In this example, on Friday, the start time is Thursday 6:30:00 PM, but the end time is Friday 6:00:00 PM (cross midnight). If we ignore the day of the week, then the start time is later than the end time, and without the MOD function, Excel would return an error if it applied only subtraction operations. To cover the “across midnight” case (start time later than the end time, but lasting no more than 24 hours), we set the divisor argument to “1” to avoid getting a negative value. That’s why we apply Excel MOD function here to calculate working hours.

Take Friday as an example to calculate the working hours.

Total 21 hours and 30 minutes are displayed after running the formula.

If we just apply subtraction, the number cannot be shown in “Time” format properly.

If we set number format to “General”, a negative number is displayed.

In the example, the formula =MOD(C3-B3,1)-MOD(E3-D3,1) is equivalent to “total working hours” minus “lunch break”. So the actual working time is calculated correctly by this formula.

Related Functions

Excel MOD function
the Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

March 28, 2022

Add Minutes to Time in Excel

Adding whole hours or decimal minutes to a given time in Excel is a fairly simple process. You can do this by using the Start_time+TIME(0,minutes,0) and Start_time+minutes/1440 functions. In this add minutes to time guide, we will show you how to do this.

Adding whole Minutes

If you want to add whole minutes to a given time, you can use the following formula:

=Start_time+TIME(0, minutes,0)

Start_time: the time that you want to add minutes to
Minutes: the minutes that you wish to add

This function returns a time formatted string.

Cell C2 shows the number of minutes you want to add, while Cell D2 displays a formula that will calculate how many more cells need changing.

=B2+TIME(0,C2,0)

=B4+C4/1440

To see the result, press Enter key.

Explanation

The TIME function is a great way to convert decimal numbers into an appropriate time format. Here, we change the number of minutes from 50 (which converts it into PM) and then add:00 before adding another set amount for minutes-in, in this case, 0 because there aren’t any other digits available on our keyboard!

Note: To subtract the minutes from a time period, you need to use this formula.

= MOD(Start_time-TIME(0, minutes,0),1)

For Instance, Cell B2 shows time, and cell C2 shows the number of minutes you want to subtract. If you want to subtract a number of minutes, use the below formula:

=MOD(B2-TIME(0,C2,0),1)

To see the result, hit Enter key.

The MOD (modulus) function is an important tool for financial professionals. It can be used to flip negative numbers into positive ones yield accurate results in most cases!

Add decimal Minutes to time

Generic formula:

=Start_time+ minutes /1440

Start_time: the time that you wish to add minutes to.
minutes: the minutes that you wish to add to start_time.

The value should be returned in time format.

For instance, cell B2 contains the time value, and cell C2 displays the number of minutes you want to add. Please see the below formula for this purpose.

=B2+C2/1440

To see the result hit Enter key.

24 hours in a day, and one minutes is 1/1440th of the whole duration.

Note: Subtracting the minutes from time is easy with this formula.

=MOD(Start_time- minutes /1440,1)

The number of minutes you want to subtract is displayed in cell C2, and time is displayed in B2. To find the correct formula, please use this simple equation:

=MOD(B2-C2/1440,1)

To see the result hit Enter key.

When Excel detects negative values in a cell, it converts them to positive with the MOD function. This means you can just flip your negatives around and get an accurate time reflecting how long something takes!

Related Functions

Excel TIME function
The Excel TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
Excel MOD function
the Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

March 23, 2022March 28, 2022

Add Hours To Time in Excel

Adding whole hours or decimal hours to a given time in Excel is a fairly simple process. You can do this by using the Start_time+TIME(hours,0,0) and Start_time+hours/24 functions. In this add hours to time guide, we will show you how to do this.

Adding whole hours

If you want to add whole hours to a given time, you can use the following formula:

=Start_time+TIME(hours,0,0)

Start_time: the time that you want to add hours to
Hours: the hours that you wish to add

This function returns a time formatted string.

Cell C2 shows the number of hours you want to add, while Cell D2 displays a formula that will calculate how many more cells need changing.

=B2+TIME(C2,0,0)

=B4+C4/24

To see the result, press Enter key.

Explanation

The TIME function is a great way to convert decimal numbers into an appropriate time format. Here, we change the number of hours from 5 (which converts it into PM) and then add:00 before adding another set amount for minutes-in, in this case, 0 because there aren’t any other digits available on our keyboard!

Note: To subtract the hours from a time period, you need to use this formula.

= MOD(Start_time-TIME(hours,0,0),1)

For Instance, Cell B2 shows time, and cell C2 shows the number of houses you want to subtract. If you want to subtract a number of hours, use the below formula:

=MOD(B2-TIME(C2,0,0),1)

To see the result, hit Enter key.

The MOD (modulus) function is an important tool for financial professionals. It can be used to flip negative numbers into positive ones yield accurate results in most cases!

Add decimal hours to time

Generic formula:

=Start_time+hours/24

Start_time: the time that you wish to add hours to.
Hours: the hours that you wish to add to start_time.

The value should be returned in time format.

For instance, cell B2 contains the time value, and cell C2 displays the number of hours you want to add. Please see the below formula for this purpose.

=B2+C2/24

To see the result hit Enter key.

24 hours in a day, and one hour is 1/24th of the whole duration.

Note: Subtracting the hours from time is easy with this formula.

=MOD(Start_time-hours/24,1)

The number of hours you want to subtract is displayed in cell C2, and time is displayed in B2. To find the correct formula, please use this simple equation:

=MOD(B2-C2/24,1)

To see the result hit Enter key.

Note:

1. The number of hours when using the Start_time + hours / 24 formula can be a decimal number. See screenshot:

2. If the result is over 24 hours, then under the Formate cell’s Number tab, go to the Custom section and format the result as [h]:mm:ss.

Related Functions

Excel TIME function
The Excel TIME function returns a date in serial number format for a particular time (the hour, minute and second).The syntax of the TIME function is as below:= TIME(hour, minute, second)…
Excel MOD function
the Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

February 8, 2022March 24, 2022

Extract or Filter Every Nth Row

Assume that you’re in a situation where you need to filter every nth row from a list having a few values. I’m pretty sure you’d choose to do it manually, which is also a great choice when you only have a few values in a list and want to filter out every nth row.

However, if you are dealing with several values in the list and want to filter out the nth row, then executing these jobs manually would be a stupid move since there is a 90% probability that you will become tired of it and will be unable to accomplish your assignment on time.

But don’t worry, after carefully reading this post, filtering off every nth row from a list with multiple values will be a piece of cake for you.

So let’s go into the article and get you out of this bind.

The formula in General

In MS Excel, use the following formula to filter every nth row:

=FILTER(total_data,MOD(SEQUENCE(ROWS(total_data)),n)=0)

Explanations of Syntax

Before we get into the formula for getting the job done quickly, we need to grasp each syntax to see how each syntax helps filter out every nth row in MS Excel.

Filter: This tool helps narrow down or filter out a range of data based on user-defined criteria.
Comma symbol (,): In Excel, this comma symbol serves as a separator, aiding in separating a list of values.
total_data: is nothing more than representing each column in the list.
Parenthesis (): The primary function of this Parenthesis symbol is to group and separate elements.
ROWS: This refers to the nth row you want to filter out.
MOD(): The MOD function accepts two real number operands as inputs and returns the remainder of dividing the integer component of the first parameter (the dividend) by the integer part of the second argument (the divisor)
SEQUENCE(): The SEQUENCE function in Excel lets you create a sequence of consecutive numbers in an array, such as 1, 2, 3, 4.

Let’s See How This Formula Works

For example, suppose you have a task in which there is a table with candidates from two regions (i.e., West and East ) and which are assigned to a particular sales number; now you want to filter out every second row, which is the sequence which we would write in the formula; now let’s analyze how to write the formula and how this formula would do it.

To filter every second row, we would use the following formula based on the supplied list:

=FILTER(A2:C10,MOD(SEQUENCE(ROWS(A2:C10)),2)=0)

To filter and extract every nth row, use a formula that combines the FILTER function with MOD, ROW, and SEQUENCE. Where data refers to the designated range A2:C10. With n hardcoded as 2, the FILTER function retrieves every third row in the data.

The FILTER function filters and extracts data based on logical criteria. This example aims to extract every second record from the supplied data, although there is no row number information in the data range A2:C10.

The first stage, working from the inside out, is establishing a row number set. This is accomplished using the SEQUENCE function as follows:

=SEQUENCE(ROWS(A2:C10))

The ROW function returns the number of rows in the specified range of data. SEQUENCE provides an array of 9 integers in sequence based on the number of rows:

{1;2;3;4;5;6;7;8;9}

This array is sent straight to the MOD function as the number parameter, with the divisor hardcoded as 2. MOD is configured to check if row numbers are divisible by 2 with a remainder of zero.

=MOD(SEQUENCE(ROWS(A2:C10)),2)=0 /// is it divisible by 2?

MOD produces an array of TRUE and FALSE values, as shown below:

{FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE}

Take note that TRUE values correlate to every second row in the data range A2:C10. This array is passed straight to the FILTER function as the include parameter. As a final result, FILTER returns every second row of data.

Related Functions

Excel Filter function
The Excel FILTER function extracts matched records from a collection of data using one or more logical checks. The include argument specifies logical tests, which might encompass a wide variety of formula conditions.==FILTER(array,include,[if empty])…
Excel MOD function
the Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel ROWS function
The Excel ROWS function returns the number of rows in a cell reference.The syntax of the ROWS function is as below:= ROWS(array)…

December 8, 2020December 8, 2020

How to Sum Every Nth Column in Excel

Sometimes for data listed in rows, we may want to sum data every Nth column, for example sum data only in ODD column or EVEN column (every 2 column). In our daily life, we may meet many cases like this. It is necessary for us to have the knowledge of sum data by every Nth column in excel. Actually, excel built-in functions SUMPRODUCT, MOD, and COLUMN can help us resolve this issue properly.

This article will show you ‘to sum every Nth column’ based on SUMPRODUCT, MOD and COLUMN functions. MOD function is frequently used in sum every Nth case, COLUMN returns column number for a reference. Thus, SUMPRODUCT function is used for ‘sum’, the combination of MOD and COLUMN works on locate ‘every Nth column’. We will introduce above three functions with simple examples, descriptions, screenshots and explanations in this article, and also show you the usage of them. Finally, we will let you know the formula workflow step by step clearly.

After reading the following article, I’m sure you can have a simple understanding of SUMPRODUCT, MOD and COLUMN functions. Besides, you can learn well on sum data every Nth column/row. I’m sure you can work well with these functions in your daily work in the future.

EXAMPLE:

We prepare three lists and set ‘N’ value in J column. N means to sum data every ‘Nth’ column. For example, for list2, N=2, so we sum data every 2 column, in fact we need to sum data in C3, E3, G3 and I3. As N is not a fixed value, it can be any integer, so we need to take this into consideration when creating a formula.

FORMULA:

To figure out this problem, we can apply a formula based on SUMPRODUCT, MOD and COLUMN functions.

Step 1: In K2 enter the formula =SUMPRODUCT(–(MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0),B2:I2).

Step 2: Click Enter to get return value. Verify that 140 is calculated correctly.

(In list1, N=1, sum data every 1 column means sum data in all columns, we can also use SUM(B2:I2) to sum total directly.)

Step 3: Copy the formula to K3 and K4. References are automatically updated in the formula.

We can see that total values are calculated correctly.

USAGE OF COLUMN and MOD FUNCTIONS

We have introduced SUMPRODUCT function usage in previous articles. In this formula, the core part is the combination of MOD and COLUMN functions. We will have a brief review about SUMPRODUCT function when analyzing the workflow of each function in the formula.

The Usage of COLUMN Function

For COLUMN function, the syntax as below:

=COLUMN ([reference])

COLUMN function can return the column number for a reference. The reference can be a cell or a range.

Usage 1: For example, when we enter ‘=column(B1)’ in D1, column number 2 is returned as B column is the second column in this worksheet. Thus, we can see that COLUMN function only returns the column number for the reference (B1 for example) and it is not affected by the reference value (15 in B1).

Usage 2: If the reference is a range, COLUMN function will return the column number of the leftmost column when pressing Enter directly after entering the formula. See example below. In this case B3:D6 contains 4 rows and 3 columns, COLUMN function will return the leftmost column (B column in this case), so 2 is returned.

Usage 3: But if the reference is a range, COLUMN function will return a horizontal array contains all column numbers in this range when selecting output part and pressing CTRL+SHIFT+ENTER. See example below. Enter =COLUMN(B3:D6) in E1, then in formula bar press F9, we can get an array {2,3,4}. So in this case, COLUMN function can return an array instead of an integer.

Usage 4: By the way, If the reference is omitted, COLUMN function returns current column number. See example below:

The Usage of MOD Function

For MOD function, the syntax as below:

=MOD(number, divisor)

MOD function can return the remainder of the two arguments ‘number is divided by divisor’. We can directly enter the two arguments number and divisor into formula to get remainder, see example below:

Remainder is 0. 0 is returned as 15 is divisible by 3, remainder is 0.

Remainder is not 0. 3 is returned because when dividing 15 by 4, it doesn’t return a whole number, the remainder is 3.

The two arguments also can be two references. Then MOD function will use the reference to execute formula. See example below:

In our instance =SUMPRODUCT(–(MOD(COLUMN(B3:I3)-COLUMN(B3)+1,J3)=0),B3:I3), ‘Number’ is a formula COLUMN(B3:I3)-COLUMN(B3)+1, ‘Divisor’ is a reference J3.

HOW THIS FORMULA WORKS:

After introducing above basic syntax, arguments, usage of COLUMN and MOD functions, we will analysis the working process of our formula in this article. Firstly, we will introduce each function workflow based on list1 and the formula in K2. In our instance list1, for snippet (MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2)=0), refer to COLUMN function usage#3, COLUMN(B2:I2) actually returns an array {2,3,4,5,6,7,8,9}, please see screenshot below. (select COLUMN(B2:I2) in formula bar, then press F9, you can see the array.)

As COLUMN(B2)=2, J2=1, so MOD(COLUMN(B2:I2)-COLUMN(B2)+1,J2) equals to MOD({2,3,4,5,6,7,8,9}-{2}+1,1). Please see screenshot below.

Continue to calculate the formula, we can get MOD({1,2,3,4,5,6,7,8},1).

In this formula, array {1,2,3,4,5,6,7,8} is argument ‘Number’, 1 is argument ‘Divisor’, as all numbers in the array are divisible by 1, so MOD({1,2,3,4,5,6,7,8},1)=0 equals to MOD({1,2,3,4,5,6,7,8})=0, compare each value in the array with 0, we can get below result.

Add double negative symbol (–) before TURE (or FALSE) result to convert TRUE to 1 and FALSE to 0, then the array converts to {1,1,1,1,1,1,1,1}, see screenshot below:

In list1, we can see that as value in J2 is 1, so sum data every 1 column is equivalent to sum all data in list B2:I2. But for list2, we need to sum data every second column, so actually we need to sum data in C3,E3,G3 and I3. Similar to list1, firstly we get all returned values of COLUMN function.

Now number argument is {1,2,3,4,5,6,7,8}, divisor is 2.

A number is divided by 2, the remainder is 0 or 1. After calculating MOD({1,2,3,4,5,6,7,8},2), we get an array {1,0,1,0,1,0,1,0}.

After comparing with 0, we can get TRUE or FLASE as result.

Convert TRUE to 1 and FALSE to 0 by adding double negative (–).

Now we come to the outermost function SUMPRODUCT. SUMPRODUCT function syntax has the following arguments:

=SUMPRODUCT(array1, [array2], [array3], …)

For list1, the last step is =SUMPRODUCT({1,1,1,1,1,1,1,1},B2:I2). We can see that there are two arrays, the first array is {1,1,1,1,1,1,1,1}, the second array is the values in B2:I2 {10,15,20,30,5,25,20,15}.

So, SUMPRODUCT({1,1,1,1,1,1,1,1},{10,15,20,30,5,25,20,15}) can be seen as value in one array multiplies by the corresponding value in another array, in this case the final array is equivalent to {1*10, 1*15,1*20,1*30,1*5,1*25,1*20,1*15}={10,15,20,30,5,25,20,15}, then sum data in the array {10,15,20,30,5,25,20,15}, 10+15+20+30+5+25+20+15=140.

For list2, the last step is =SUMPRODUCT({0,1,0,1,0,1,0,1},B3:I3). We can see that there are two arrays, the first array is {0,1,0,1,0,1,0,1}, the second array is the values in B3:I3 {20,10,30,40,25,15,35,45}.

So, for SUMPRODUCT({0,1,0,1,0,1,0,1},{20,10,30,40,25,15,35,45}), multiply value in array1 by the value in array2, so we get {0*20,1*10,0*30,1*40,0*25,1*15,0*35,1*45}={0,10,0,40,0,15,0,45}. SUMPRODUCT({0,10,0,40,0,15,0,45})=10+40+15+45=110.

RESULT:

Let’s check the formula workflow in F3. Actually, the workflow for list3 is the same, only divisor is changed to 3 for MOD part.

Step 1: Execute COLUMN function, convert all reference to real values.

Step 2: Execute MOD function.

Step 3: Compare values with 0 and convert TRUE/FALSE to 1/0.

Step 4: Execute SUMPRODUCT function, to sum data based on two arrays. SUMPRODUCT({0,0,20,0,0,35,0,0}=55. So we get 55 in K4.

SUMMARY:

COLUMN function returns an array if COLUMN function is entered as a horizontal array formula.
COLUMN function returns the leftmost column if COLUMN function is not entered as a horizontal array formula.
MOD function is often used in calculating ‘Nth’ rows/columns cases.
To sum ODD columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=1),list)

To sum EVEN columns:

=SUMPRODUCT(–(MOD(COLUMN(list)-COLUMN(first cell)+1,2)=0),list)

Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….

November 11, 2020

How to Count Cells that Contain even or odd numbers in Excel

This post will guide you how to count the number of cells that contain odd or even numbers within a range of cells using a formula in Excel 2013/2016.How do I count cells that contain odd numbers through the use of an Excel VBA Macro.

Count Number of Cells that Contain Even Numbers

Assuming that you have a data list in the range of cells B1:B6, and you want count the number of cells containing only even numbers, and write the result in cell D1.You can use a formula based on the SUMPRODUCT function and the MOD function. Like this:

=SUMPRODUCT(–(MOD(B1:B6,2)=0))

Let’s see how this formula works:

=MOD(B1:B6,2)=0

The Mod function can be used to check whether the cell within the range B1:B6 containing the numbers if they are divided by 2 or not. If True, it indicates that that number is an even number. This formula returns an array result like:

{TRUE;TRUE;TRUE;FALSE;FALSE;TRUE}

The double negative operator would convert the above result array into 1 and 0. And still returns an array result. Like:

{1;1;1;0;0;1}

The SUMPRODUCT function will add those numbers in the array result and returns the final result.

Count Number of Cells that Contain Odd Numbers

If you want to count number that contain odd numbers within a range of cells B1:B6, and you can use a formula based on the SUMPRODUCT function along with the MOD function. Like below:

=SUMPRODUCT(–(MOD(B1:B6,2)<>0))

The Mod function will check whether the cell with the range B1:B6 containing numbers are divided by 2 or not.

Count Number of Cells that Contain Odd Numbers Using VBA

You can also count cells that contain odd numbers from a range using VBA macro, just do the following steps:

Step1# open your excel workbook and then click on “Visual Basic” command under DEVELOPER Tab, or just press “ALT+F11” shortcut.

Get the position of the nth using excel vba1

Step2# then the “Visual Basic Editor” window will appear.

Step3# click “Insert” ->”Module” to create a new module.

Step4# paste the below VBA code into the code window. Then clicking “Save” button.

Sub CountCellsContainOddNumbers()
Dim mysheet As Worksheet
Dim myrange As Range
Set mysheet = Worksheets("Sheet3")
Set myrange = mysheet.Range("B1:B6")
For Each xcell In myrange
cellmod = xcell Mod 2
oddnum = oddnum + cellmod
Next xcell
mysheet.Range("D1") = oddnum

End Sub

Step5# back to the current worksheet, click on Macros button under Code group. then click Run button.

Step6# let’s see the result:

Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products. The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

August 29, 2020August 23, 2020

How to Highlight Every Other Row or Every Nth Row in Excel?

Sometimes we may want to highlight every other row or every Nth row in a spreadsheet to make data in different rows looks more clearly. Though we can apply table styles to highlight table rows automatically, we can also highlight rows by excel ‘Conditional Formatting’ function. Actually, ‘Conditional Formatting’ has a powerful capability to highlight cells per some rules, it already contains some rules like ‘Duplicate Values’ in itself, we can also define new rule by ourselves. In this free tutorial, we will introduce you the way to highlight every Nth row through ‘Conditional Formatting->New Rule’ function. Follow steps with screenshots below, we can make sure you can learn it well.

Precondition:

Create a table with several rows. Now we want highlight rows every other row or every Nth row.

Method 1: Highlight Every Other Row by Conditional Formatting Function

Step 1: Select the range to highlight rows, you can ignore the header part. Click Home in ribbon, then click Conditional Formatting in Styles group, select New Rule in menu.

Step 2: On New Formatting Rule dialog, select the last rule type: Use a formula to determine which cells to format.

Step 3: In Edit the Rule Description section, in Format values where this formula is true textbox enter the formula =ISEVEN(ROW()).

Step 4: Click Format button in Preview section. Verify that Format Cells window is displayed.

Step 5: Click Fill tab, then select a color as background color. For example, select yellow, then click OK.

Step 6: After above step, we go back to New Formatting Rule window. You can see that in Preview section, cell is filled with yellow. Click OK.

Step 7: Check the result. See screenshot below. Rows are highlighted every other row.

Method 2: Highlight Every Nth Row by Conditional Formatting Function

Just repeat above steps, only a little difference in some steps. See simple steps below.

Step 1: In above step#3, in Edit the Rule Description section, in Format values where this formula is true textbox enter the formula =MOD(ROW(),3)=1. In this formula, highlight rows every third row.

Step 2: Select yellow to highlight rows. See preview part.

Step 3: Check the result, see screenshot below. It works well. If you want the header part is also included in counting, you can update the formula as =MOD(ROW(),3)=0.

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

May 4, 2020May 3, 2020

How to Calculate Total Work Days or Hours Exclude Weekends/Holidays in Excel

In our daily work we often need to count the total working-days or working-hours for employees then we can pay for them the salary in some cases. But we have to count the total exclude weekends or holidays as we don’t work on these days. So, we need to know how can we calculate the total work days or hours exclude these non-working days. Actually, excel functions are so strong that can be used directly to do calculation for some specific cases, this article will introduce you to calculate total work days/hours by NETWORKDAYS function, now let’s get started.

Before learning the formula, we need to prepare a simple table for demonstration.

In above table, we can see that the normal working day is started from 9:00 (lower) to 18:00 (upper), total 9 hours, and we want to calculate the work days/hours (exclude weekends) between 3/2/2020 15:00 to 3/9/2020 18:00. Holidays is 3/5/2020. Please make sure the format is correct for your date and time. If you are not sure the format for your cells, you can enter Format Cells window to set them by date or time or custom.

Part 1: Calculate Total Work Days Exclude Weekends

As we mentioned, we will use NETWORKDAYS function to count days. This function is easily to understand, it is used for returning the days between start date and end date, it automatically excludes the weekends.

=NETWORKDAYS(start_date,end_date,holidays)

Step 1: In C26, enter the formula =NETWORKDAYS(A26,B26).

Step 2: Click Enter to get result.

Step 3: As we set cell format as date and time, so we need to convert it to general for C26. Select C26, and click dropdown list in Number. Select General.

Step 4: Verify that 6 is displayed properly. It is an approximate value for total work days, start time and end time are not included into consideration. Anyway, it already excludes the weekends as we expect.

Part 2: Calculate Total Work Days Exclude Holidays

We just need to add holiday parameter into the formula. So, in C26, this time we enter =NETWORKDAYS(A26,B26,A34), then click Enter.

Verify that this time 5 is displayed as total work days. Holiday is excluded.

Part 3: Calculate Total Work Hours Exclude Weekends

If we pay for salary based on hours or we need to know an exactly working hours for an employee, we can use NETWORKDAYS function to calculate total work hours as well, but the formula is a little complex, and it also contains some other functions.

Step 1: In D26, enter the formula

=(NETWORKDAYS(A26,B26)-1)*(B31-B30)+IF(NETWORKDAYS(B26,B26),MEDIAN(MOD(B26,1),B31,B30),B31)-MEDIAN(NETWORKDAYS(A26,A26)*MOD(A26,1),B31,B30).

You can see details below about formula. I marked parameters in blue.

=(NETWORKDAYS(Start Date Time,End Date Time)-1)*(Upper-Lower)+IF(NETWORKDAYS(End Date Time, End Date Time),MEDIAN(MOD(End Date Time,1),Upper,Lower),Upper)-MEDIAN(NETWORKDAYS(Start Date Time,Start Date Time)*MOD(Start Date Time,1),Upper,Lower)

Step 2: Click Enter to get the result. As currently the format is set as General when calculating total work days in Part 1. So, 2 is displayed in this field now. We need to convert it to proper format later. It also can be displayed as 1/2/1900 0:00 probably if you don’t change format during the whole process.

Step 3: Select D26, right click to load menu, then select Format Cells to convert format for this cell.

Step 4: Under Number tab, select Custom in Category list, then select [h]:mm:ss, click OK. You can also type your own format as well.

Step 5: Verify that 48 hours is calculated properly.

Notes:

If the start time and end time are both included between upper and lower period, you can use a simpler formula below:

=(NETWORKDAYS(Start Date Time,End Date Time)-1)*(Upper-Lower)+MOD(End Date Time,1)-MOD(Start Date Time,1)

In this case, enter below formula:

=(NETWORKDAYS(A26,B26)-1)*(B31-B30)+MOD(B26,1)-MOD(A26,1)

Part 4: Calculate Total Work Hours Exclude Holidays

You can just use 48 to minus 9 hours (Upper-Lower). Or you can also use below format, just add holiday parameter into the first NETWORKDAYS function is ok.

=(NETWORKDAYS(A26,B26,A34)-1)*(B31-B30)+IF(NETWORKDAYS(B26,B26),MEDIAN(MOD(B26,1),B31,B30),B31)-MEDIAN(NETWORKDAYS(A26,A26)*MOD(A26,1),B31,B30)

Related Functions

Excel MEDIAN function
The Excel AVERAGE function returns the median of the given numbers. And the median is the number in the middle of a list of supplied numbers.The syntax of the MEDIAN function is as below:=MEDIAN (number1, [number2], …)….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

March 29, 2020February 5, 2023

How to Stack Data from Multiple Columns into One Column in Excel

Are you tired of having your data spread out across multiple columns, making it difficult to analyze and understand? It can be overwhelming to have to constantly switch back and forth between columns, hunting for the information you need. This post will show you how to stack data from multiple columns to one column in Microsoft Excel Spreadsheet.

This article will introduce you the two methods. After reading the article below, you will find the two ways are verify simple and convenient to operate in excel.

In previous article, I have shown you the method to split data from one long column to multiple columns by VBA and Index function.

For example, see the initial table below:

And we want reverse data into one column and make it looks like:

Now we can follow below two methods to make it possible. Let’s start it.

1. Stack Data in Multiple Columns into One Column by Formula

Step 1: Select range A1 to F2 (you want to do stack), in Name Box, enter a valid name like Range, then click Enter.

Step 2: In any cell you want to locate the first cell of destination column, enter the formula

=INDEX(Range,1+INT((ROW(A1)-1)/COLUMNS(Range)),MOD(ROW(A1)-1+COLUMNS(Range),COLUMNS(Range))+1).

Please be aware that you have to replace ‘Range’ in this formula to your defined name in Name Box.

Step 3: Click Enter. Verify that ‘ID’ (the value in the first cell of selected range) is displayed properly.

Step 4: Drag the fill handle to fill I column. Verify that data in previous initial location is reversed to one column properly.

Note:

If you drag the fill handle to cells extend selected range cell number, error will be displayed in redundant cell.

2. Stack Data in Multiple Columns into One Column by VBA

Step 1: On current visible worksheet, right click on sheet name tab to load Sheet management menu. Select View Code, Microsoft Visual Basic for Applications window pops up.

Or you can enter Microsoft Visual Basic for Applications window via Developer->Visual Basic.

Step 2: In Microsoft Visual Basic for Applications window, click Insert->Module, enter below code in Module1:

Sub StackDataToOneColumn()
    Dim Rng1 As Range, Rng2 As Range, Rng As Range
    Dim RowIndex As Integer

    Set Rng1 = Application.Selection
    Set Rng1 = Application.InputBox("Select Range:", "StackDataToOneColumn",        Rng1.Address, Type:=8)
    Set Rng2 = Application.InputBox("Destination Column:", "StackDataToOneColumn", Type:=8)

    RowIndex = 0
    Application.ScreenUpdating = False

    For Each Rng In Rng1.Rows
        Rng.Copy
        Rng2.Offset(RowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        RowIndex = RowIndex + Rng.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Step 3: Save the codes, see screenshot below. And then quit Microsoft Visual Basic for Applications.

Step 4: Click Developer->Macros to run Macro. Select ‘StackDataToOneColumn’ and click Run.

Step 5: Stack Data to One Column dialog pops up. Enter Select Range $A$1:$F$2. Click OK. In this step you can select the range you want to do stack.

Step 6: On Stack Data to One Column, enter Destination Column $I$1. Click OK. In this step you can select the first cell from destination range you want to save data.

Step 7: Click OK and check the result. Verify that data is displayed in one column properly. The behavior is as same as the result in method 1 step# 4.

3. Merge Cells into One in Excel

To merge or consolidate cells in Microsoft Excel, follow these steps:

Step 1: Select the cells you want to merge.

Stack Data from Multiple Columns into One Column in Excel20

Step 2: Right-click on the selected cells and choose “Merge Cells” from the context menu.

Step 3: Alternatively, you can click on the “Merge & Center” button in the “Alignment” section of the “Home” tab on the ribbon.

Stack Data from Multiple Columns into One Column in Excel21

Note: When merging cells, the contents of the leftmost cell will be preserved, while the contents of the other cells will be lost. If you have important data in multiple cells, it’s recommended to copy the data to another location before merging the cells.

4. Combine two columns in excel without losing data

To combine two columns in Microsoft Excel without losing data, you can use the following formula:

Step 1: In a new column C1, enter the following formula:

=A1 & B1

combine two columns in excel without losing data1

If you want to combine data from two or multiple cells in Excel, you can also use this formula.

Step 2: Press Enter to apply the formula.

combine two columns in excel without losing data2

Step 3: Drag the formula down to the end of the data in the columns.

combine two columns in excel without losing data3

The formula uses the & operator to concatenate the contents of two cells. In this example, the formula combines the contents of cells A2 and B2 into a single cell. By dragging the formula down, you can combine the contents of all the cells in the two columns.

You can also use the CONCATENATE function to combine columns, which works in a similar manner. The formula would look like this:

=CONCATENATE(A1, B1).

combine two columns in excel without losing data

5. Combine 2 Columns with a Space

If you want to combine two columns with a space between them, you can use the following formula:

=A1 & " " & B1

In this example, the formula combines the contents of cells A1 and B1 into a single cell, separated by a space. By dragging the formula down, you can combine the contents of all the cells in the two columns.

6. Append Columns in Excel

If you want to append two columns in Microsoft Excel, just follow these steps:

Step 1: Select the first column of data that you want to append.

Step 2: Right-click on the selected cells and choose “Copy” from the context menu.

Step 3: Select the second column of data that you want to append, and right-click on the first cell in the column.

Step 4: Choose “Insert Copied Cells” from the context menu.

This will insert a copy of the first column of data after the second column, effectively appending the two columns.

7. Conclusion

Stacking data from multiple columns into one is a useful operation in Microsoft Excel when you need to combine and analyze data from different sources. The process can be easily achieved by using the built-in functions such as the & operator or the CONCATENATE function, which allow you to concatenate the contents of multiple cells into one.

Hope you can like this post.

8. Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
Excel COLUMNS function
The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….

January 4, 2019January 4, 2019

How to Highlight Every Other Row Using Conditional Formatting in Excel

This post will guide you how to highlight every other row in Excel. How do I shade every other row with conditional formatting in Excel.

Highlight Every Other Row
Video: Highlight Every Other Row

Highlight Every Other Row

Assuming that you have a list of data in range A1:C9, and you want to highlight every second product name in your data. And you can easily achieve the result using conditional formatting feature. The below are steps to highlight every second row in Excel:

#1 select the range of cell A2:C9 which you want to highlight every second row.

#2 go to HOME tab, click the Conditional Formatting command under Styles group. Then select New Rules… from the drop-down menu list.

3# the New Formatting Rule window will appear.

4# select the Use a formula to determine which cells to format option under Select a Rule Type: box, and then enter the following formula in the Format values where this formula is true: box:

=MOD(ROW(),2)=1

Note: The MOD function returns the remainder when the row number is divided by 2. And it will check each cell if it meets the criteria. And it will check Cell A2 firstly, and the row function in Cell A2 would return 2, and the MOD(2,2) formula returns 0, which does not meet our criteria. And then it will move on the other cell to check it based on the formula. If it returns 1, then it matches the criteria. It highlights the entire row.

5# click the Format… button, then the Format Cells window will appear.

6# in the “Format Cells” window, switch to the Fill tab, choose the background color, and then click OK button. You can also switch to other tabs to tweak the settings as you want.

7# back in the New Formatting Rule window, you can see a preview of your rows background color. Then click OK button.

You will see that every second row have been highlighted with the background color as you set.

Video: Highlight Every Other Row

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

September 13, 2018February 18, 2019

Generate All Possible Combinations of Two Lists in Excel

This post will guide you how to generate all possible combinations with a formula in excel. How do I create list with all possible combinations of two lists in Excel. How to list all combinations from two separate list or range in Excel.

Generate All Possible Combinations of Two Lists in Excel

Assuming that you have two list of data in different column, A and B, and you want to get a list of all possible combinations from those two list in Column A and B. How to achieve it.

You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function. Like this:

=IF(ROW()-ROW($F$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($F$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($F$1),COUNTA(B:B))+1))

Type this formula into Cell F1, and then drag the AutoFill Handle down column F until you get cells that look empty.

Note: if you want to use this formula into other cell, you need to change the absolute cell reference to that cell. Such as: form F1 to E1.

=IF(ROW()-ROW($E$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($E$1))/COUNTA(B:B)+1))&INDEX(B:B,MOD(ROW()-ROW($E$1),COUNTA(B:B))+1))

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
Excel COUNTA function
The Excel COUNTA function counts the number of cells that are not empty in a range. The syntax of the COUNTA function is as below:= COUNTA(value1, [value2],…)…

September 5, 2018September 5, 2018

Sum Every Nth Row or Column

This post will guide you how to sum every nth row or column with a formula in Excel. How do I sum every nth value in a same row or column in Excel. How to sum every nth cell in a column or in a row with an Excel Formula. How to sum every second, third, or nth row or column in a range in Excel.

Sum Every Nth Row

If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function.
For example, you want to sum every 2 rows in the range B1:B6, you can write down this formula to achieve the result.

=SUM(B1:B6*(MOD(ROW(B1:B6),2)=0))

You need to type this formula into a blank cell, and press Ctrl +Shift +Enter shortcuts to change the formula as Array formula.

You can change the number 2 as you need to sum every nth row in a specific range of cells in Excel.

Sum Every Nth Column

If you want to sum every nth column in a column, you can create a formula based on the SUMPRODUCT function, the MOD function, and the Column function.

For example, you want to sum every 3rd column in range B1:G1, you can use the following formula to achieve the result.

=SUMPRODUCT(--(MOD(COLUMN(B1:G1)-COLUMN(B1)+1,3)=0),B1:G1)

Related Functions

Excel SUMPRODUCT function
The Excel SUMPRODUCT function multiplies corresponding components in the given one or more arrays or ranges, and returns the sum of those products.The syntax of the SUMPRODUCT function is as below:= SUMPRODUCT (array1,[array2],…)…
Excel SUM function
The Excel SUM function will adds all numbers in a range of cells and returns the sum of these values. You can add individual values, cell references or ranges in excel.The syntax of the SUM function is as below:= SUM(number1,[number2],…)…
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel COLUMN function
The Excel COLUMN function returns the first column number of the given cell reference.The syntax of the COLUMN function is as below:=COLUMN ([reference])….
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….

July 18, 2018

Create a Five Star Rating System

This post will guide you how to create a five star rating system in your current worksheet in excel. How do I change the five points system to five star rating in excel. How to use the conditional formatting function to create a five star rating system in excel.

Create a Five Star Rating System

The five star rating system is a very useful way of visualizing rating like you see on Amazon site.

For example, you have a list of data in your current worksheet and it contains rating values in each cell in rating column, and you want to create a five star rating system in your worksheet, just do the following steps:

#1 select the range of cells (C1:G1) and merging it as one cell, then type “Star Rating” text in this cell.

#2 type the numbers from 1 to 5 into each cells of range H1:L1

#3 type the following formula into the formula box of Cell C2, then drag the Auto Fill Handle over other cells (C2:G3) to apply this formula.

=IF($B2>=H$1,1,IF(INT($B2)=H$1-1,MOD($B2,1),0))

#4 select the range of star rating (C2:G3), go to HOME tab, click Conditional Formatting command under Styles group, then select New Rule from the drop down list. The New Formatting Rule dialog will appear.

#5 click Format all cells based on their values from Select a Rule Type list. Choose Icon Sets from the Format Style, select 3 stars icon from Icon Style. Check Show Icon Only box. Then set a full star is shown if the value is greater than or equal to 1, and a half star if the value is greater than or equal to 0.5.

#6 let’s see the result.

Related Functions

Excel IF function
The Excel IF function perform a logical test to return one value if the condition is TRUE and return another value if the condition is FALSE. The IF function is a build-in function in Microsoft Excel and it is categorized as a Logical Function.The syntax of the IF function is as below:= IF (condition, [true_value], [false_value])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….
Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…

July 3, 2018

Highlight Multiples of Specific Value

This post will guide you how to search and highlight multiple values of a apecific value in excel. How to check if a number is a multiple of a specific number and then highlight those searched values in excel.

Highlight Multiples of Specific Value

If you want to highlight numbers that are multiples of a specific number, and you need to find those numbers firstly and then you can use the conditional formatting feature to highlight it. You need to create a formula based on the MOD function.

Assuming that you want to highlight multiples of a specific number 6 in Cell E1, Just do the following steps:

#1 select the range of cells A1:C4

#2 go to Home tab, click Conditional Formatting command under Styles group, and select New Rule…menu from the drop down menu list. The New formatting Rule dialog box will appear.

#3 select Use a formula to determine which cells to format in the Select a Rule Type: section. And enter the formula =MOD(A1,$E$1)=0 into the Format values where this formula is true: text box, then click Format…. button. The Format Cells window will appear.

#4 switch to the Fill tab, select one background color that you want to highlight. Then click OK.

#5 click OK button.

Note: Conditional formatting is used to evaluate for each cell in the selected range with the MOD function to check the remainder of dividing the value in each cell, if the remainder is zero, the value should be an even multiple of the specific number.

Related Functions

Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

June 25, 2018June 25, 2018

Delete Every Other Row

This post will guide you how to delete every other row by filter command in Excel. How do I delete every other row in a selected range on a worksheet by Excel VBA Macro.

Delete Every Other Row with Filter command

To delete every other row in Excel, you need to filter alternate rows, and then select those rows, and delete them at once. So you need to create a helper column using MOD function with ROW function, just do the following steps:

#1 type the formula =MOD(ROW(),2) in one cell next to your original data, and press enter key, then drag the auto fill handler over other cells to generate 0,1 numbers in each cells.

#2 select the helper column, go to DATA tab, Click Filter command under Sort & Filter group. The drop-down filter arrows will appear in header cells.

#3 click the filter arrow in the helper column, and select the 0 check box from the filter drop down list.

#4 you will see that all “1” rows are hidden, select all the visible “0” rows, then right-click on the selection range, and click Delete Row.

#5 all rows with “0” values are removed. Then click the Filter button again on the helper column to remove auto-filter.

#6 you can delete the helper column.

Delete every other row with VBA

You can also use an Excel VBA Macro to delete every other row. Do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub DeleteEveryOtherRow()
    Dim r As Range
    Dim IR As Range

    Set IR = Application.Selection
    Set IR = Application.InputBox("Select one Range :", "Delete Every Other Row", IR.Address, Type:=8)
    Application.ScreenUpdating = False
    For i = IR.Rows.Count To 1 Step -2
        Set r = IR.Cells(i, 1)
        r.EntireRow.Delete
    Next
    Application.ScreenUpdating = True
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 Select one range that you want to delete every other row.

#7 let’s see the result.

Related Functions

Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

June 6, 2018

Transpose Multiple Columns into One Column

This post will guide you how to transpose multiple columns into a single columns with multiple rows in Excel. How do I put data from multiple columns into one column with Excel formula; How to transpose columns into single column with VBA macro in excel.

Assuming that you have a data list in range B1:D4 contain 3 columns and you want to transpose them into a single column F. just following the below two ways.

Transpose Multiple Columns into One Column with Formula

You can use the following excel formula to transpose multiple columns that contain a range of data into a single column F:

#1 type the following formula in the formula box of cell F1, then press enter key.

=INDEX($B$1:$D$4,1+INT((ROW(B1)-1)/COLUMNS($B$1:$D$4)),MOD(ROW(B1)-1+COLUMNS($B$1:$D$4),COLUMNS($B$1:$D4))+1)

#2 select cell F1, then drag the Auto Fill Handler over other cells until all values in range B1:D4 are displayed.

#3 you will see that all the data in range B1:D4 has been transposed into single column F.

Transpose Multiple Columns into One Column with VBA Macro

You can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly. Just do the following steps:

#1 click on “Visual Basic” command under DEVELOPER Tab.

#2 then the “Visual Basic Editor” window will appear.

#3 click “Insert” ->”Module” to create a new module.

#4 paste the below VBA code into the code window. Then clicking “Save” button.

Sub transposeColumns()
    Dim R1 As Range
    Dim R2 As Range
    Dim R3 As Range
    Dim RowN As Integer
    wTitle = "transpose multiple Columns"
    Set R1 = Application.Selection
    Set R1 = Application.InputBox("please select the Source data of Ranges:", wTitle, R1.Address, Type:=8)
    Set R2 = Application.InputBox("Select one destination single Cell or column:", wTitle, Type:=8)
    RowN = 0
    Application.ScreenUpdating = False
    For Each R3 In R1.Rows
        R3.Copy
        R2.Offset(RowN, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
        RowN = RowN + R3.Columns.Count
    Next
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

#5 back to the current worksheet, then run the above excel macro. Click Run button.

#6 select the source data of ranges, such as: B1:D4

#7 select one single cell in the destination Column, such as: F1

#8 let’s see the last result.

Related Functions

Excel INDEX function
The Excel INDEX function returns a value from a table based on the index (row number and column number)The INDEX function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the INDEX function is as below:= INDEX (array, row_num,[column_num])…
Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer. And the INT function rounds down, so if you provide a negative number, the returned value will become more negative.The syntax of the INT function is as below:= INT (number)…
Excel ROW function
The Excel ROW function returns the row number of a cell reference.The ROW function is a build-in function in Microsoft Excel and it is categorized as a Lookup and Reference Function.The syntax of the ROW function is as below:= ROW ([reference])….
Excel Columns function
The Excel COLUMNS function returns the number of columns in an Array or a reference.The syntax of the COLUMNS function is as below:=COLUMNS (array)….
Excel MOD function
he Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. The syntax of the MOD function is as below:=MOD (number, divisor)….

April 14, 2018September 13, 2018

Excel MOD Function

This post will guide you how to use Excel MOD function with syntax and examples in Microsoft excel.

Description

The Excel MOD function returns the remainder of two numbers after division. So you can use the MOD function to get the remainder after a number is divided by a divisor in Excel. And the returned result will have the same sign as divisor.

The MOD function is a build-in function in Microsoft Excel and it is categorized as a Math and Trigonometry Function.

The MOD function is available in Excel 2016, Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000, Excel 2011 for Mac.

Syntax

The syntax of the MOD function is as below:

=MOD (number, divisor)

Where the MOD function arguments are:

number – This is a required argument.The number that you want to get the remainder.
divisor – This is a required argument. The number that you want to divide number.

Note:

If the divisor is 0, then the MOD function will return the #DIV/0! Error.
The MOD function returns a result in the same sign as the divisor.

Excel MOD Function Examples

The below examples will show you how to use Excel MOD Function to get the remainder after number is divided by divisor.

1# to get the remainder of 6/5, enter the following formula in Cell B1.

=MOD(6,5)

2# 1# to get the remainder of -6/5, enter the following formula in Cell B1.

=MOD(-6,5)

You will see that the sign is the same as divisor.

Related Functions

Excel INT Function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer. The syntax of the INT function is as below:= INT (number)…

More Excel MOD Formula Examples

Transpose Multiple Columns into One Column
You can use the following excel formula to transpose multiple columns that contain a range of data into a single column, and you can also write an Excel VBA Macro to transpose the data of range in B1:D4 into single column F quickly…
Delete Every Other Row
To delete every other row in Excel, you need to filter alternate rows, and then select those rows, and delete them at once. So you need to create a helper column using MOD function with ROW function…
Highlight Multiples of Specific Value
If you want to highlight numbers that are multiples of a specific number, and you need to find those numbers firstly and then you can use the conditional formatting feature to highlight it. You need to create a formula based on the MOD function….
Create a Five Star Rating System
How do I change the five points system to five star rating in excel. How to use the conditional formatting function to create a five star rating system in excel…..
Sum Every Nth Row or Column
If you want to sum every nth rows in Excel, you can create an Excel Array formula based on the SUM function, the MOD function and the ROW function..….
Generate All Possible Combinations of Two Lists
You can use a formula based on the IF function, the ROW function, the COUNTA function, The INDEX function and the MOD function to get a list of all possible combinations from those two list….

March 8, 2018February 18, 2019

How to Calculate Days, Weeks, Months and Years between Two Dates

This post will teach you how to calculate days, weeks, months and years between two dates in excel. How do I count the number of days, weeks, months and years between 2 dates in excel.

Calculate days between two dates

If you want to calculate the difference in days between two dates, you can use the DATEDIF function to create an excel formula as follows:

=DATEIF(B1,B2, "D")

This formula will calculate days between two dates in cell B1 and B2, then returns the value in days.

Calculate months between two dates

If you want to calculate the difference in months between tow dates, you can also use the DATEDIF function to create the following generic formula:

=DATEDIF(B1,B2,"M")

You should note that the third argument is “M” in the DATEDIF function. So this formula returns the value in months between two dates in excel.

Calculate years between two dates

You can also use the DATEDIF function to calculate the number of years between two dates in excel, just refer to the following excel formula based on the DATEDIF function:

=DATEDIF(B1,B2,"Y")

This formula returns the value in years.

Calculate weeks between two dates

If you want to calculate the number of weeks between two dates, you just need to subtract start date from the end date and then the returned result is divided by 7. So you can write the below generic formula:

=(B2-B1)/7

This formula will return a decimal number, you can change the number format as you need.

Or you can use the INT function to rounds down to the nearest whole number or use ROUND function to round to nearest whole number.

=INT((B2-B1)/7)

=ROUND((B2-B1)/7,0)

You may be want to display the word “week” before the week number in the cell, you can do it as following steps:

1# right-click on the selected cells, then select Format Cells

2# on the Number Tab, choose Custom under Category, then select # “weeks” type.

Calculate Years, Months and Days between two dates

If you want to determine how many years, months and days between two dates, you can use the DATEDIF function to create the following complex formula:

=DATEDIF(B1,B2,"Y") & " Years, " & DATEDIF(B1,B2,"YM") & " Months, " & DATEDIF(B1,B2,"MD") & " Days"

If you do not want to use the DATEDIF function, and you can also use the following formula to achieve the same result:

=INT((TODAY()-A1)/365.25) & ” years , ” & INT(MOD((TODAY()-A1)/365.25,1)*12) & ” months and ” & INT(MOD((TODAY()-A1)/30.4375,1)*30.4375) & ” days”

Related Functions

Excel INT function
The Excel INT function returns the integer portion of a given number. And it will rounds a given number down to the nearest integer.The syntax of the INT function is as below:= INT (number)…
Excel DATEDIF function
The Excel DATEDIF function returns the number of days, months, or years between tow dates.The syntax of the DATEDIF function is as below:=DATEDIF (start_date,end_date,unit)…
Excel TODAY function
The Excel TODAY function returns the serial number of the current date. So you can get the current system date from the TODAY function. The syntax of the TODAY function is as below:=TODAY()…
Excel Round function
The Excel INT function rounds a number to a specified number of digits. You can use the ROUND function to round to the left or right of the decimal point in Excel.The syntax of the ROUND function is as below:=ROUND (number, num_digits)…