array formulas - Google Sheet | ArrayFormula - Get next smaller value of COL

Question

Google Sheets. I want to get the next smaller value of the current value in COL A.

I've tried this...

=MAX( FILTER(INDIRECT("A" & ROW()-1 );MAX(A:A)) )

Looks good for the moment if the values in COL A are sorted. The formula above is needed to pasted in each field.

"but", I want to use ARRAYFORMULA() ... I'm trying a long time (months) with breaks ...

That's one of my last tests.

=ArrayFormula(IF(ROW(G:G)=1;"Trip";  IF(ROW(G:G)<3;"0";  MAX( FILTER(INDIRECT("A" & ROW() );MAX(A:A)) )  ))

I've already tried VLOOKUP, too. But maybe I'm at the wrong way.

Unfortunately I didn't found a solution which match my case.

Does anybody can solve my issue? Or give me a hint to can solve this by my-self?

UPDATE Jan 26, 2021

Here we go... I've created a dummy sheet based on original values but cut not needed cols.

https://docs.google.com/spreadsheets/d/1yI0UEdZ3aKU03ElPchUuAPcmnAoMmCyXWhZBcu2Hv3g/edit#gid=0

Col A - is the "current value" Col G+H - are some tries with ArrF

Col J - is working but not with ArrF - Shows the diff to the last value. Yes this can also be done without INDIRECT() and Co. But I want to try the basic logic.

Col K - show the last value.

Currently Col A is sorted. But if not the diff isn't working. I have added some values from above (grey marked) to simulate.

The goal should be to get the next smaller value of "current" COL A using ArrayFormula.

question from:https://stackoverflow.com/questions/65890034/google-sheet-arrayformula-get-next-smaller-value-of-col

Answer 1

Use OFFSET

See the docs: OFFSET

=
{
    "Last";
    0;
    ArrayFormula( OFFSET(A3:A, -1, 0) )
}

=
{
    "Diff";
    0;
    ArrayFormula( A3:A100 - OFFSET(A3:A100, -1, 0) )
}

I changed the structure slightly so that it would be contained within arrays. That way you don't need the IF statements for the headers, for example.

I also did not use the whole column notation A:A partly because the array already has A1 and A2 covered. I tried A3:A but that didn't work for the diff column, because it always says it needs more rows. Probably because it needs to reference a row that is not on the same row, if that makes sense.

Refs

• Arrays
• OFFSET

UPDATE

Due to international settings you may need to have your functions written in this way:

=
{
    "Last";
    0;
    ArrayFormula( OFFSET(A3:A; -1; 0) )
}

=
{
    "Diff";
    0;
    ArrayFormula( A3:A100 - OFFSET(A3:A100; -1; 0) )
}