Fixing a formula - need to find & replace a single digit

[Burrgogi]

I have a spreadsheet that I created long ago. One of the cells contains this formula which I need to fix:

=SUM(B12-B11)/ABS(B11)

It's referencing the wrong row, so I need to correct it to this:

=SUM(B11-B10)/ABS(B10)

In essence, all the row numbers need to be decreased by 1.

If it was just this single cell, it would be alright to do manually but it compares 2024 data with 2023 data so I've got 12 months to fix. Plus the data goes back to 2016 with the same comparisons so there are a total of 110 formulas that I need to fix like this.

I had the idea of replacing the equal sign with the '#' symbol. Basically convert the formula to a text string so I could do a simple text string 'find & replace' operation.

But I'm lost as to how to fix this in the most efficient manner.

 

[dreid1011]

Find and Replace can look in formulas (unless it's not an option in 2010):

 

[Burrgogi]

Find and Replace can look in formulas (unless it's not an option in 2010):
View attachment 111772

Yes, I well aware that option exists. My challege is how to fix all 110 formulas in the manner I described. The columns that are in the formula are correct. However all the row numbers need to be shifted up by 1.

 

[dreid1011]

Yes, I well aware that option exists. My challege is how to fix all 110 formulas in the manner I described. The columns that are in the formula are correct. However all the row numbers need to be shifted up by 1.

I'm not sure I understand the disconnect here... do a find/replace on all the formulas and change the row 11 to row 10, then do a second find/replace on all the formulas and change the row 12 to row 11. Unless of course, that sample formula is not the same in all cells and the row numbers are not all 11 and 12?

 

[dreid1011]

Are the formulas in one column, multiple columns, or not consistent (all over the sheet)?

 

[Burrgogi]

Are the formulas in one column, multiple columns, or not consistent (all over the sheet)?

All over the sheet.

What I had in mind when I posted this thread was a find & replace operation that could ignore the column letters and just decrease all the row numbers by 1.
Example:
=SUM(#5-#4)/ABS(#4)
=SUM(#4-#3)/ABS(#3)

But that wasn't working out at all.

After I posted, I had an idea that came to me. Using my aforementioned method (replacing = with #) I copied all the cells containing the erroneous formula and pasted them one row lower from the original spot. Then I replaced # with the equal sign, making them formulas again. From there, it was just a matter of fixing just the oldest year only so I only had to fix 12 of them manually.

 

[dreid1011]

All over the sheet.

What I had in mind when I posted this thread was a find & replace operation that could ignore the column letters and just decrease all the row numbers by 1.
Example:
=SUM(#5-#4)/ABS(#4)
=SUM(#4-#3)/ABS(#3)

But that wasn't working out at all.

After I posted, I had an idea that came to me. Using my aforementioned method (replacing = with #) I copied all the cells containing the erroneous formula and pasted them one row lower from the original spot. Then I replaced # with the equal sign, making them formulas again. From there, it was just a matter of fixing just the oldest year only so I only had to fix 12 of them manually.

Ah, okay then, sounds like you got it resolved.