Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2
CPU Threads: 2; OS Version: Linux 4.4; UI Render: default;
Locale: en-US (en_US.UTF-8); Calc: group
The following formula is a simple array lookup and linear interpolation where the look up value is in the same sheet as the formula and the array is in a sheet named i7.
The formula piece i7.$L$1:$L$15 is converted to ‘i7’!$L$1:$L$15 as it should.
The formula piece “i7.K” is not converted.
Perhaps I there is a better way to say “i7.K”&MATCH(…)?
Formula in Calc:
=INDIRECT(“i7.K”&MATCH(B22,i7.$L$1:$L$15,1))+(B22-INDIRECT(“i7.L”&MATCH(B22,i7.$L$1:$L$15,1)))*(INDIRECT(“i7.K”&MATCH(B22,i7.$L$1:$L$15,1)+1)-INDIRECT(“i7.K”&MATCH(B22,i7.$L$1:$L$15,1)))/(INDIRECT(“i7.L”&MATCH(B22,i7.$L$1:$L$15,1)+1)-INDIRECT(“i7.L”&MATCH(B22,i7.$L$1:$L$15,1)))
Formula when saved as xls:
=INDIRECT(“i7.K”&MATCH(B22,‘i7’!$L$1:$L$15,1))+(B22-INDIRECT(“i7.L”&MATCH(B22,‘i7’!$L$1:$L$15,1)))*(INDIRECT(“i7.K”&MATCH(B22,‘i7’!$L$1:$L$15,1)+1)-INDIRECT(“i7.K”&MATCH(B22,‘i7’!$L$1:$L$15,1)))/(INDIRECT(“i7.L”&MATCH(B22,‘i7’!$L$1:$L$15,1)+1)-INDIRECT(“i7.L”&MATCH(B22,‘i7’!$L$1:$L$15,1)))