Okay I've updated the sheet. It now shows two calculations. Better language to make clear what those are would be welcome.
Calc 1: Expand vdev when it's full to threshold; expand to final width then rewrite data: How much space did we gain.
Calc 2 : Expand vdev when it's full to threshold; expand by just 1 drive; expand again when vdev is full to threshold; keep doing this until final width, then rewrite data: How much space did we gain.
The space gain with calc 2 is larger because there is more data to be rewritten that was not written with the final parity to disk ratio.
This now uses a "spaceLoop" function. I don't know whether this comes across when making a copy. In case it does not:
Code:function spaceLoop(initial, final, size, parity, threshold) { var spacesaved = initial * size * threshold * ((parity / initial) - (parity / final)); for (var drives = initial + 1; drives < final; drives++) { spacesaved += size * threshold * ((parity / drives) - (parity / final)); } return spacesaved; }
Based on the equation in my last post (still waiting for moderator approval at this time), you should be able to use the following formulas instead. The first bit uses the equation I posted directly, which is a little simpler but algebraically equivalent to what you had. The other avoids your custom function, although summing the reciprocals is still annoyingly awkward (I'm not a spreadsheet engineer, is there a better way?).
Code:
[Cell B14]: =$B$4*B15 [Cell B15]: =$B$6*$B$5*($B$8-$B$7)/$B$8 [Cell B19]: =$B$4*B20 [Cell B20]: =$B$6*$B$5*SUM(ARRAYFORMULA(DIVIDE(1,SEQUENCE(1,$B$8-$B$7,$B$7+1,1))))