News for hackers. Stuff that matters.
Re:Ask HN: What are the biggest problems with spreadsheets? by Bostonian on Thursday July 23, 2015 @12:19PM (#9936722)
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.

Re:Ask HN: What are the biggest problems with spreadsheets? by misterdata on Friday July 24, 2015 @03:34PM (#9944609)
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-t...

Re:Ask HN: What are the biggest problems with spreadsheets? by eggoa on Friday July 24, 2015 @01:58PM (#9943893)
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.)

Re:Ask HN: What are the biggest problems with spreadsheets? by jotux on Thursday July 23, 2015 @04:57PM (#9938464)
>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.

Re:Ask HN: What are the biggest problems with spreadsheets? by pc86 on Thursday July 23, 2015 @12:20PM (#9936732)
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.
Re:Ask HN: What are the biggest problems with spreadsheets? by ForEnglandJames on Thursday July 23, 2015 @12:59PM (#9937004)
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.