It is required to create a formula for gafik, which would indicate the range from the last 5 cells in the dynamic table.

I got something like this:

=OFFSET($N$13;COUNTA(OFFSET('Calibration Check'!$N$13;;;COUNTA('Calibration Check'!$N$13:$N$40)-1))-5;0;5)

In this case, I only manage to select 5 from the end of the cell in the dynamic table.

  • What's the question? The formula does not suit? What exactly? - Enikeyschik

1 answer 1

I only manage to select 5 from the end of the cell

No, the formula forms a range. Only in your formula the range of 5 lines needs to be shifted one line down. For example, check on the amount:

 =СУММ(СМЕЩ($N$13;СЧЁТЗ(СМЕЩ($N$13;;;СЧЁТЗ($N$13:$N$40)-1))-4;0;5))

How else can you check? Select the formula in the formula bar, press F9 . The highlighted part will be displayed as a result. For the shift function , this will be an array of values.

COUNT (ACCOUNT) counts text values ​​as well. And since the graph is based on numerical values, it is more correct to use the COUNT (ACCOUNT)

 =СМЕЩ(N13;СЧЁТ(N13:N45)-5;;5)

For a range with empty cells:

 =СМЕЩ(ИНДЕКС($N:$N;ПРОСМОТР(9E+307;$N$13:$N$40;СТРОКА($N$13:$N$40)));-4;;5)

If the formula is not in the named range, but on a sheet, it is better to remove the volatility:

 =ИНДЕКС($N:$N;СЧЁТ($N$13:$N$45)+8):ИНДЕКС($N:$N;СЧЁТ($N$13:$N$45)+12)

Non volatile for a range with empty cells:

 =ИНДЕКС(N:N;ПРОСМОТР(9E+307;N13:N40;СТРОКА(N13:N40))-4):ИНДЕКС(N:N;ПРОСМОТР(9E+307;N13:N40;СТРОКА(N13:N40)+12))

Add sheet name to ranges, if necessary.