I have a spreadsheet with some calculations in it, and I want to make a plot of what happens to one cell’s value as another changes (from 0 to 100). I have been searching for about 45 minutes and cant find any addons or functions that achieve this. It is not practical to make a copy of the data for every step because there are a bunch of interim calculations that rely on the value.
Is this possible easily, or do I need to restructure my spreadsheet to make 100 copies of the formulas?
Here is a simplified version, I want to plot the value of J2 as A2 changes from 0 to 100. The formulas are not exact, as the real sheet is significantly more complex, but it is the same idea.
https://docs.google.com/spreadsheets/d/1nGx0smnBU8J9MU4wlKStSgPe2poVIvYENevvnG1a5AY
10
Plotting Cells using Sheet Formula
I created a solution using Sheet Formula based on your spreadsheet.
How the Formula Works
- Cell
A2contains the input that specifies the number of outputs to be calculated.
Formula:
=LET(a,SEQUENCE(A2 + 1),b,BYROW(a,LAMBDA(x,(((x-1)*D3/5)+C3)-(((x-1)*D5/3)+C5))),c,BYROW(a,LAMBDA(y,y-1)),HSTACK(c,b))
Input: 10
| input | diff |
|---|---|
| 0 | -14 |
| 1 | 2.666666667 |
| 2 | 19.33333333 |
| 3 | 36 |
| 4 | 52.66666667 |
| 5 | 69.33333333 |
| 6 | 86 |
| 7 | 102.6666667 |
| 8 | 119.3333333 |
| 9 | 136 |
| 10 | 152.6666667 |
Reference:
- LET
- SEQUENCE
- BYROW
- LAMBDA
- HSTACK