I'm having trouble putting together a spreadsheet and I believe there must be a simple way to do it (I'm using Google Sheets).

I have a starting value and an ending value, I need to calculate the intermediate values spread over 125 points. The value can increase or decrease, I just need to find a formula that returns the linear evolution of these points.

My spreadsheet is similar to the table below:

Any ideas?

Thanks!!!

CodePudding user response：

• go to File
• select Spreadsheet settings...
• select Calculation
• turn on Iterative calculation
• hit Save settings button

• paste this formula into A3

=IF(INDIRECT("A"&ROW()-1)=INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW()&":A")));1;1);
ARRAYFORMULA(TEXT(TRANSPOSE(SPLIT(REPT(INDIRECT("A"&ROW()-1)&";";COUNTA(ARRAYFORMULA(INDIRECT("A"&
ROW()-1) (ROW(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&
ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A"));));1;1)-1))-2))));";"));
"##"));ARRAYFORMULA(TEXT(INDIRECT("A"&ROW()-1) (ROW(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(
INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(
INDIRECT("A"&ROW()&":A"));));1;1)-1))-2)* (INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW(
)&":A")));1;1)-INDIRECT("A"&ROW()-1))/(ROWS(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(N(INDIRECT("A"&
ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A"));); IF(N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&
":A"));));1;1)-1)))* (ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(FILTER(INDIRECT("A"&ROW()&":A");N(
INDIRECT("A"&ROW()&":A")));1;1)))-(ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(FILTER(INDIRECT("A"&ROW()&
":A");N(INDIRECT("A"&ROW()&":A")));1;1)))* (100/(((COUNTIF(INDIRECT("A"&ROW()&":A"&INDEX(FILTER(IF(
N(INDIRECT("A"&ROW()&":A"));ROW(INDIRECT("A"&ROW()&":A")););IF(N(INDIRECT("A"&ROW()&":A"));ROW(
INDIRECT("A"&ROW()&":A"));));1;1));"<>""") 1)-1)* 100))))/ABS(MINUS(INDIRECT("A"&ROW()-1);INDEX(
FILTER(INDIRECT("A"&ROW()&":A");N(INDIRECT("A"&ROW()&":A")));1;1)));"0,0000")))

demo spreadsheet

CodePudding user response：

It's unclear whether you want whole-number values only or, if not, to what precision the intermediate values should be. But the following formula in some cell with at least 125 rows open below it will do the trick:

=ArrayFormula(ROUNDUP(SEQUENCE(126,1,6078*100,(59096-6078)/125*100)/100))

Of course, you can set the start and end values in two cells and reference the cells instead.

The reason everything is multiplied by 100 and then divided by 100 is because, most often, the intermediate raw values will include decimal portions while SEQUENCE only works with whole numbers. So multiplying by 100 and then dividing later by 100 allows the underlying numbers to go to a precision of 1/100 (or two decimal points) before ROUNDUP is applied.

If you want to see the decimal values, remove ROUNDUP( ).

If you want greater precision, increase 100 to 1000 (etc.) in each of the three locations it appears within the formula.