0

I have an excel document with formulas in it that are referencing items the same space away. For example:

=C9/C10
=P9/P10
=AC9/AC10
=AP9/AP10
=BC9/BC10
=BP9/BP10

But excel isn't picking up on this pattern. Any ideas on how excel can recognize this?

New contributor
user1061917 is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
  • Are you dragging down or accross? – Scott Craner Jul 12 at 17:19
1

If you are dragging down use:

=INDEX($9:$9,,(ROW(1:1)-1)*13+3)/INDEX($10:$10,,(ROW(1:1)-1)*13+3)

If you are dragging accross:

=INDEX($9:$9,,(COLUMN(A:A)-1)*13+3)/INDEX($10:$10,,(COLUMN(A:A)-1)*13+3)

This just does simple math to return the correct column number to the INDEX.

0

If you use the dynamic R1C1 reference everywhere (i.e. if you did Copy Paste Formula it would work successfully) it might make more sense to make a named range that is actually a calculation and does this for you. Here's how to accomplish:

  • Click on a cell you want this calculation to happen (cell d3 in my example)
  • Click on the tab FORMULAS
  • Click on Name Manager
  • Click on New Name
  • Click on the cells you would use to make the calculation (this is where you would normally specifiy a named range)
  • Make sure to take out the $ signs (this might require some surgical clicking as using key arrows will create more references).
  • Name it and click OK.
  • Now whenever you insert that calculation, just type =yourNamedRang and it will execute the calculation as if you had pasted copyPaste Formula into that cell.

See Example

Final result:

illustration

New contributor
PGSystemTester is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.

Your Answer

user1061917 is a new contributor. Be nice, and check out our Code of Conduct.

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.