=3-index($c$10:$j$17;column()-2;row()-9)

[santhosh51]

=3-INDEX($C$10:$J$17;COLUMN()-2;ROW()-9) ........


whats the function of COLUMN()-2 & ROW()-9 in the above function .


It would be helpfull if the formula is fully explained

[XOR LX]

Hi,

The COLUMN and ROW functions simply return the number of the column and row respectively for the given reference, so e.g. ROW(A1)=1, COLUMN(A1)=1, ROW(X67)=67, COLUMN(X67)=24, etc.

When the reference is omitted, as in ROW(), COLUMN(), they return the row/column number of the active cell.

This is useful, for example, when constructing dynamic formulae, as in the above, so that when copied to further cells, the reference(s) - in this case arguments of the INDEX function - will change appropriately. If for example, you start with your formula in cell C10, it becomes:

=3-INDEX($C$10:$J$17;3-2;10-9) which is:

=3-INDEX($C$10:$J$17;1;1)

However, suppose this is copied down to the next row, then it will be in row 11 and will become:

=3-INDEX($C$10:$J$17;3-2;11-9) which is

=3-INDEX($C$10:$J$17;1;2)

and, if instead it was copied, not down, but to the right, it would be in column D and so become:

=3-INDEX($C$10:$J$17;4-2;10-9) which is

=3-INDEX($C$10:$J$17;2;1)

Regards