2

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 ?

2
0

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

4
  • 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
0

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

"If(b2="+",a2+20,if(b2="-",a2-20),"")

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 .