0

I want to indicate a range of an array in rows and columns, but I don't want to have to type one by one, is there any way to indicate the array row and column using the formulas: "ROW ()" AND "COL () ?? because it is hard work to type the range manually, the matrix has to be of the type that is in the image. I tried to indicate the ranges and could not get the correct sequence of numbers, okay?

enter image description here

  • 1
    No, it's not possible. Please share your final objective and we might suggest a better workaround – Máté Juhász Dec 10 '19 at 4:53
  • is offset() the function your are looking for? I mean : =offset(C11,rows(C11:C15),columns(C11:D15)) please have a try and share the outcome... – p._phidot_ Dec 10 '19 at 8:08
  • correction, it should be : =offset(C11,0,0,rows(C11:C15),columns(C11:D11)) – p._phidot_ Dec 10 '19 at 9:58
  • I debugged your formula by pressing "F9" and the result gave {0 \ 0; 0 \ 0,0 0 \ 0,0 0 \ 0}, in case it was given the correct number sequence: {1 \ 2; 3 \ 4,5 \ 6,7 7 \ 8,9 9 \ 10}, e agora? – Elienay Junior Dec 10 '19 at 14:27
  • I need to debug the formula (F9) to give the sequence {1 \ 2; 3 \ 4; 5 \ 6; 7 \ 8; 9 \ 10} – Elienay Junior Dec 10 '19 at 14:28
0

Use:

{=ROW(C11:D15)+COLUMN(C11:D15)-(13-(ROW()-11))}

adjusted for the language base of your copy of Excel.

The difficulty for your desire is the hard-to-define-a-pattern for reducing the results of the row and column values you are adding. In the simple version shown in the picture, just subtracting 13, say, gives {1\2;2\3... instead of {1\2;3\4...

You need a reducing amount to subtract so you need to keep reducing the "13" by 1 for each row you carry the formula down. Subtracting the rising quantity ROW()-11 does this.

| improve this answer | |
  • His formula brought me: {3 \ 4; 4 \ 5; 5 \ 6; 6 \ 7; 7 \ 8} – Elienay Junior Dec 18 '19 at 19:47
  • I think I'll end up defining a helper table with the sequence numbers at the positions I want and then fetch it with the formula "Index ()" and pull the values ​​in the correct sequence. Because I want her to bring me the break when debugging the formula in (F9) – Elienay Junior Dec 18 '19 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.