LinkBack URL
About LinkBacks
I have this formula in row K10 to render values in column G27 to G37, just wondering what does the function COLUMNS($A:A) mean?
=IF($D$14="Yes",INDEX(Sheet2!$G$27:$G$37,COLUMNS($A:A)),0
I have this formula in row K10 to render values in column G27 to G37, just wondering what does the function COLUMNS($A:A) mean?
=IF($D$14="Yes",INDEX(Sheet2!$G$27:$G$37,COLUMNS($A:A)),0
COLUMNS($A:A) =1
changes to
COLUMNS($A:B) =2
then
COLUMNS($A:C) =3
and so on as the formula is filled across
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
thanks martin but why do i need numbers 1,2,3,etc. in the formula?
INDEX(Sheet2!$G$27:$G$37,1)=g27
INDEX(Sheet2!$G$27:$G$37,2)=g28
INDEX(Sheet2!$G$27:$G$37,3) =g29 and so on
Your formula contains INDEX, operating on a range of Sheet2!$G$27:$G$37. The syntax for that function is INDEX(range, row, column), although column is redundant here because you range is only one column wide. So in the first instance, INDEX(Sheet2!$G$27:$G$37,COLUMNS($A:A)) will return the first element of that range, i.e. G27, in the second instance INDEX(Sheet2!$G$27:$G$37,COLUMNS($A:B)) will return the second element, i.e. G28.
So, as the formula is copied across, the COLUMNS() part will change, and so the INDEX part will return values from successive rows.
Hope this helps.
Pete
EDIT: Oops, Martin hadn't replied when I started my explanation.
Last edited by Pete_UK; 11-03-2012 at 02:44 PM.
Thanks Pete and Martin
please correct me if i misunderstood your explaination:
so in the function COLUMNS ($A:A) the first A refers to the first column in the array and the second A refers to the first row in the array; likewise($A:B) indicates first (and only) column in array and B second row in array and so on?
Thanks
No. The function COLUMNS() returns the number of columns there are in the range supplied to it. So, COLUMNS($A:A) returns the number 1, whereas COLUMNS($A:B) returns the number 2 - rows have nothing to do with it, so COLUMNS($A1:C$10) for example would return the number 3. The $ symbol in front of the A prevents it from changing if it is copied across, so it is always "anchored" to that column as the start of the range. The other parameter, A without the $ symbol, is free to change as it is copied across, so it becomes $A:B, $A:C, $A:D etc in successive columns, thus returning 2, 3, 4 and so on. It is a quick and easy way of incrementing a number as a formula is copied across, and in conjunction with INDEX it will return the 1st, 2nd, 3rd, 4th etc element of the range which is being indexed.
Hope this helps.
Pete
I see! Thanks Pete. How will the formula change if the range is in a row and i want to return the values in a column?
The opposite of column() is row().
I don't know if this is an answer to your question.
Otherwise:
1) Wait for an reaction of other forummembers.
or
2) Post an example of your Excel file, without confidentional information, AND the desired result.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
thanks oeldere, attached is the sheet: What should go in red cell I22?
Put this formula in I22:
=INDEX($D$9:$M$9,ROWS($1:1))
then copy it down.
NOTE that you do not need to commit with with CSE - it is not an array formula. The same applies to the formula you have in D9.
Hope this helps.
Pete
=index($p$8:$p$17,rows($a$1:a1))
See the attached file.
I see PeteUk and Martindwilson have already give you an anser.
@rentb23
It would be nice to the ones who respond on your question,
if you respond (feed back)
(and after that marking the question as solved),
are the given solution usefull?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks