5 ms·

In spreadsheets you often repeat the same formula many times. In a programming language you would not write C2 = B2/B1 - 1 C3 = B3/B2 - 1 C4 = B4/B3 - 1 ad

In spreadsheets you often repeat the same formula many times. In a programming language you would not write C2 = B2/B1 - 1 C3 = B3/B2 - 1 C4 = B4/B3 - 1 ad infinitum. You would store the B's and C's in arrays and write a loop, or use array operations in languages that supported them. In Excel, if you have stock prices in column B, you compute stock returns in column C by entering "=B2/B1 - 1" in cell C2 and copying the formula for the whole column. But nothing stops you from inadvertently changing the formula in cell C100.

pc86 11y ago
Not exactly a fix to what you're referring to, but if the data is formatted as a table the formula is copied to every cell automatically.
- ForEnglandJames 11y ago
  Have used exactly this feature in a freelancing gig to help contain data entry/formula copying errors. I just wish it were less obtuse to set up.
jotux 11y ago
>But nothing stops you from inadvertently changing the formula in cell C100. I'm not sure how recent this feature is but excel will actually warn you in you change a formula in the middle of a series of cells that all use the same formula. I was getting "inconsistent formaula" warnings just yesterday when I was trying to make a spreadsheet.
eggoa 11y ago
You can use array formulas to make one formula operate on many values at once. In your example, there'd be one same formula in C2:C4 which would be {=B2:B4/B1:B3-1}. When I had to use Excel a lot I would follow this pattern as much as possible. (As a side effect, every normal Excel user I worked with hated maintaining my workbooks.)
misterdata 11y ago
Use Excel's tables feature, and it will more or less force a column to contain the same formula for each row (or a numeric value). In these formulas you can refer to other cells in a row by column name. https://support.office.com/en-ca/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-ca/article/Overview-of-Excel-t...

pc86 11y ago

Not exactly a fix to what you're referring to, but if the data is formatted as a table the formula is copied to every cell automatically.

ForEnglandJames 11y ago
Have used exactly this feature in a freelancing gig to help contain data entry/formula copying errors. I just wish it were less obtuse to set up.

jotux 11y ago

>But nothing stops you from inadvertently changing the formula in cell C100. I'm not sure how recent this feature is but excel will actually warn you in you change a formula in the middle of a series of cells that all use the same formula. I was getting "inconsistent formaula" warnings just yesterday when I was trying to make a spreadsheet.

eggoa 11y ago

You can use array formulas to make one formula operate on many values at once. In your example, there'd be one same formula in C2:C4 which would be {=B2:B4/B1:B3-1}. When I had to use Excel a lot I would follow this pattern as much as possible. (As a side effect, every normal Excel user I worked with hated maintaining my workbooks.)

misterdata 11y ago

Use Excel's tables feature, and it will more or less force a column to contain the same formula for each row (or a numeric value). In these formulas you can refer to other cells in a row by column name. https://support.office.com/en-ca/article/Overview-of-Excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c https://support.office.com/en-ca/article/Overview-of-Excel-t...