• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

column()

sachar

Member
Dear Troublesuiter,

With reference to the attachment, can you brief me the logic of this formula?
 

Attachments

  • Copy of Offset Examples.xlsx-L-10_MATCH.xlsx
    20.8 KB · Views: 0
Hi ,

Your worksheet has dates in the range A4 : A8.

You want that these dates should be transposed to the cells L3 : P3.

When we want to access the value in A4 , we can use the formula :

=INDEX($A:$A , 4)

When we want to access the value in A5 , we can use the formula :

=INDEX($A:$A , 5)

Similarly , we can access the values in A6 , A7 and A8.

If we want to make the formula so that we can drag it across columns , we have to ensure that the second parameter changes from 4 to 5 to 6 to 7 to 8 automatically.

The easiest way is to use the COLUMN() function , since this will change automatically when a formula is dragged across.

When we enter a formula in cell L3 , we want the index number to be 4 ; since the COLUMN() function will return 12 when it is used in any cell in column L ( since column L is the 12th column ) , to get 4 all we need to do is subtract 8.

When this formula is dragged across to column M , the COLUMN() function will return the value 13 , and subtracting 8 from this will give us 5 ; in column N we will get 6 , in column O we will get 7 , and in column P we will get 8.

Narayan
 
Dear Narayan,

Thanks to solve the problem, in this formula may, I know the cause of applied IF formula with "",""?


=IF(INDEX($A:$A,COLUMN()-8)="","",INDEX($A:$A,COLUMN()-8))
 
Dear Narayan,

In the following formula, please help me to get it the sense of "","" in the IF formula.

=IF(INDEX($A:$A,COLUMN()-8)="","",INDEX($A:$A,COLUMN()-8))
 

Attachments

  • Copy of Offset Examples.xlsx-L-10_MATCH.xlsx
    21 KB · Views: 0
The use of the unqualified ROW() (or COLUMN()) functions is not very rigorous.

The very fact that such constructions often need to be offset by the addition/subtraction of some constant (-8 here), a constant which would most likely need to be redetermined should the range(s) in question change, is one issue.

The other is that such set-ups are not immune to error following row/column insertions within the range.

Far better is to use ROWS (or COLUMNS) in such situations, which suffer neither of these two drawbacks.

See here for more:

http://excelxor.com/2014/08/25/row-vs-rows-for-consecutive-integer-generation/

Regards
 
Dear,
Please, I have a simple question, what is the meaning of here "","" in this IF formula?

=IF(INDEX($A:$A,COLUMN()-8)="","",INDEX($A:$A,COLUMN()-8))
 
You are mixing two things. The function checks if INDEX results in empty string. If its empty then it returns "" (empty string) or else returns the results from INDEX function.
 
Back
Top