My task is to implement a basic spreadsheet program with formulas that support basic math, conditional summation, and other operators. What is the best way to represent it in memory?
The requirement is to make the spreadsheet as large and fast as possible, probably 100mn rows. It can run on a server with unusual specs, but the goal is to minimize the time of operations, especially when a single cell is updated that affects many cells. I suspect this means pre-processing formulas so they aren't parsed every time a single value is updated and the computation needs to cascade through affected cells.
To keep it simple, I'm ignoring all subtleties in e.g. excel. Just a grid of numbers and formulas. Strings are optional.
You might consider looking at some of the popular spreadsheet implementations that exist (I know there are some in frontend land) and seeing what gyrations they've gone through for representation and things like computation/etc on the way to Excel parity.
For example SheetJS is quite widely used and permissively licensed so reviewing that code (and accrediting them appropriately depending on use) may be a decent place to draw inspiration.
It's possible that there's no "best" way to do it, but is instead a nest of tradeoffs in terms of productivity, performance or a bunch of other factors.
[EDIT] - Also, in general before someone could answer what might be the "best" way to do it, they need to know what you're optimizing for -- space/performance/ergonomics/etc.