I would like to use "+" or "- " in referencing a cell eg

=i(10+23) = i33 .

So that I don't have to increment the cell reference manually .

Any help would be great ?


You can use the INDEX function, combined with ROW() and COLUMN(). Here are two simple examples:

  • Left: =INDEX(A:A, 5) returns the contents of the fifth cell in column A.
  • Right: =INDEX(A:A, ROW()+5) returns the contents of the cell five rows down in column A. All cells in italic use the same formula.

enter image description here enter image description here

  • Thanks , let me try that and feedback May 14 '20 at 12:24
  • Tried it out , not quiet what I had in mind . What I'd like to know if there is a way to increment or change by a factor of X , eg I would like to add 30 on to the cell id my statement is =y(103+30) , which I'd like to equal =y133 . Is there a function which can do that ? May 14 '20 at 16:37
  • Check out these function =sequence() with =indirect() ?
    – pjmg
    May 14 '20 at 17:18
  • @JonathanPop I hope my second example works better.
    – Glorfindel
    May 14 '20 at 17:31

I'm not entirely following the request without a test sheet, but it sounds like you're just trying to add or subtract 20 from a value depending on if you input a "+" or a "-".

If that's the case, you can use the two columns next to your data. In the first, you could manually input your "+" or "-". In the column next to it, you can have an if statement saying


By doing this and dragging the formula down, each cell will move based off of your plus or minus.

If this doesn't answer your question, could you please share a test sheet?

This site is temporarily in read only mode and not accepting new answers.

Not the answer you're looking for? Browse other questions tagged .