How to Get the Maximum Value in Continuous Non-null Cells

 

We have Excel file book1.xlsx:

Max

2020/12/1

2020/12/2

2020/12/3

2020/12/4

2020/12/5

2020/12/6

1755

6666

1438

1688

3295

3060

2780

2650

345

12

4

19

3

B2:G2 contains continuous non-null values 1755 6666 1438 in recent 3 days.

Put the maximum value in A2.

B3: G3 contains continuous non-null values 3060 2780 2650 in recent 3 days.

Put the maximum value in A3.

B4:G4 contains continuous non-null values 345 12 4 in recent 3 days.

Put the maximum value in A4.

 

The expected result is as follows:

Max

2020/12/1

2020/12/2

2020/12/3

2020/12/4

2020/12/5

2020/12/6

6666

1755

6666

1438

1688

3060

3295

3060

2780

2650

345

345

12

4

19

3

Directions:

1.      Start esProc (Download esProc installation package and free DSK edition license HERE. You will be prompted to load the license file when you run esProc for the first time).

2. Open Option>add-ins in Excel and click Go button to load add-ins ExcelRaq.xll. The add-in file is located in [esProc installation directory \bin] directory. Refer to related Excel documentation if you don’t know how to do the loading.

3.      Now enter a formula in A2 to get the result. The formula is =esproc("=?1.group@o(~!=null).select@z1(~.len()>=?2 && ~(1)).to(-?2).max()",$B2:$G2,3). The formula can be reused in other cells to do subsequent computations. Parameter 1 represents all data in the current row; parameter 2 means non-null values in continuous n days.