Need explanations on row(indirect())

[PATSYS]

Hi all,

In cell A11, the value is 6/1/2011
In cell B11, the value is 6/15/2011

This formula:

=ROW(INDIRECT(A11&":"&B11))

returns the following results:

{40695;40696;40697;40698;40699;40700;40701;40702;40703;40704;40705;40706;40707;40708;40709}

If I press F9.


In short, it listed all the dates between the 2 dates.

What I do not understand is how the function ROW works here. This function is supposed to return the row number of a reference according to the Help menu.

THanks

 

[Aladin Akyurek]

Hi all,

In cell A11, the value is 6/1/2011
In cell B11, the value is 6/15/2011

This formula:

=ROW(INDIRECT(A11&":"&B11))

returns the following results:

{40695;40696;40697;40698;40699;40700;40701;40702;40703;40704;40705;40706;40707;40708;40709}

If I press F9.


In short, it listed all the dates between the 2 dates.

What I do not understand is how the function ROW works here. This function is supposed to return the row number of a reference according to the Help menu.

THanks

INDIRECT reads the following...

INDIRECT("1:2")

as an address spec from 1 to 2. And

ROW(INDIRECT("1:2"))

which would return:

{1;2}

Since dates are integers, we get rows from date1 to date2 in the example you give. If you would try a date that exceeds the numbers that Excel allows, you'd get an error.

 

[T. Valko]

Hi all,

In cell A11, the value is 6/1/2011
In cell B11, the value is 6/15/2011

This formula:

=ROW(INDIRECT(A11&":"&B11))

returns the following results:

{40695;40696;40697;40698;40699;40700;40701;40702;40703;40704;40705;40706;40707;40708;40709}

If I press F9.


In short, it listed all the dates between the 2 dates.

What I do not understand is how the function ROW works here. This function is supposed to return the row number of a reference according to the Help menu.

THanks

We need some function that can return an array of numbers that represent date serial numbers.

By default the ROW function returns an array and the array can hold a number of elements from 1 to the max number of rows in a worksheet (which is Excel version dependent).

Let's assume you're using Excel 2003 which has 65536 rows in a worksheet.

The number 65536 is also the date serial number for June 5 2079.

So, in Excel versions that have 65536 rows we can use the ROW function to generate an array of dates from January 1 1900 all the way to June 5 2079.

In Excel versions 2007 and later the number of rows in a worksheet is over 1 million so we can generate dates using the ROW function well into the future!

 

[Rick Rothstein]

In cell A11, the value is 6/1/2011
In cell B11, the value is 6/15/2011

This formula:

=ROW(INDIRECT(A11&":"&B11))

returns the following results:

{40695;40696;40697;40698;40699;40700;40701;40702;40703;40704;40705;40706;40707;40708;40709}

If I press F9.

In short, it listed all the dates between the 2 dates.

What I do not understand is how the function ROW works here. This function is supposed to return the row number of a reference according to the Help menu.

The thing you need to understand is that when you enter a date in a cell, Excel does not see that as 6/1/2011 (or however it is displayed on your system)... that format is for the convience of the human reader... Excel see the date as an offset value from some "date zero". In Excel, the date "January 1, 1900" has an offset value of 1, "January 2, 1900" has an offset value of 2, and so on. Today date "July 9, 2011" has an offset value of 40733. So, when you wrote...

=ROW(INDIRECT(A11&":"&B11))

Excel substituted 40695 for the "6/1/2011" date and 40709 for the "6/15/2011" date mean the above formula became this...

=ROW(40695:40709)

Take the above into consideration with the other responses you received and I think this will all begin to make more sense to you.

 

[T. Valko]

We need some function that can return an array of numbers that represent date serial numbers.

By default the ROW function returns an array and the array can hold a number of elements from 1 to the max number of rows in a worksheet (which is Excel version dependent).

Let's assume you're using Excel 2003 which has 65536 rows in a worksheet.

The number 65536 is also the date serial number for June 5 2079.

So, in Excel versions that have 65536 rows we can use the ROW function to generate an array of dates from January 1 1900 all the way to June 5 2079.

In Excel versions 2007 and later the number of rows in a worksheet is over 1 million so we can generate dates using the ROW function well into the future!

Here's an example of how we can use this.

A1 = 1/1/2011
B1 = 7/9/2011

We want to count how many Sundays there are within those date ranges (inclusive).

=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)=7))

Returns 27

ROW(INDIRECT(A1&":"&B1)) generates the array of date serial numbers from 1/1/2011 to 7/9/2011.

--(WEEKDAY(...,2)=7) then tests to see which of the those dates are Sundays. This expression will generate an array of 1s and 0s.

SUMPRODUCT(...) adds up all the 1s to arrive at our result of 27.

 

[T. Valko]

We need some function that can return an array of numbers that represent date serial numbers.

By default the ROW function returns an array and the array can hold a number of elements from 1 to the max number of rows in a worksheet (which is Excel version dependent).

Let's assume you're using Excel 2003 which has 65536 rows in a worksheet.

The number 65536 is also the date serial number for June 5 2079.

So, in Excel versions that have 65536 rows we can use the ROW function to generate an array of dates from January 1 1900 all the way to June 5 2079.

In Excel versions 2007 and later the number of rows in a worksheet is over 1 million so we can generate dates using the ROW function well into the future!

I just realized that I might not have directly answered your question!

The ROW function can be used to evaluate those date serial numbers because of the valid range references that it can accept.

For example, these are all valid range references for the ROW function:

ROW(A1)
ROW(A1:A10)
ROW(A1:B5)
ROW(A:A)
ROW(1:1)
ROW(1:10)
ROW(45000:46000)

So:

A1 = 1/1/2011 (date serial number = 40544)
B1 = 7/9/2011 (date serial number = 40733)

ROW(INDIRECT(A1&":"&B1))

Evaluates to:

ROW(40544:40733)

You might also see something like this:

ROW(INDIRECT("1:5"))

This is doing the exact same thing:

ROW(1:5)

However, it's a bit different in that we have the the range quoted like:

ROW(INDIRECT("1:5"))

With the range quoted that will ALWAYS refer to 1:5.

If you used just ROW(1:5) and you happened to insert a new row 1 in the sheet then ROW(1:5) will update to become ROW(2:6) which could lead to a problem since it now evaluates as 2:6 rather than the intended 1:5.

Here's a simplified example of that in use.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">63</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">66</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">31</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">34</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">28</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">11</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">80</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">49</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">35</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">81</TD></TR></TBODY></TABLE>


If we wanted to get the sum of the 5 lowest numbers in the range we could do something like this:

=SUMPRODUCT(SMALL(A1:A10,ROW(INDIRECT("1:5"))))

 

[PATSYS]

Thank you all for the usual great insights.

 

[all4excel]

Wow thats a very good explanation and an example..

If I am not mistaken then this way we can actually do iterations in excel..

all4excel