This is an analysis calculation sheet to get information from just copying raw data into another Excel sheet rest in the file. My purpose is to zero the data copying and calculation manually rather than the raw data. I've developed this system completely. However, I want to minimize the column that helps them get the final result. For that, I must shrink several formulas distributed in several columns into to single column. My project works are described below,
As shown in the above image(This is the last part of the data set, Data range set to H11:H96), I want to filter the values above 1.86, which is dynamic. As an example, 1.86 should be detected by the formula. I can detect it with the below formula. The concept of detecting the value is, that the cell value is larger than the immediate above cell value.
=FILTER(H11#, (H11# > INDEX(H11#, ROW(H11#)-ROW(H11)))*(ROW(H11#) > 11))
This will return all values larger than the above cell value. As example This is located in Cell K11(Spill range).
To get the position of the last value from the array, I've used the below formula,
=MATCH(INDEX(K11#,COUNTA(K11#)),H11#,0)
Then I used the Offset function to get the all data above 1.86(Dynamic value).
=OFFSET(H11,,,MATCH(INDEX(K11#,COUNTA(K11#)),H11#,0)-1).
This will return all data above 1.86(Dynamic value), which are located in H11:H94. This is my task and from this point, it starts my further development to minimize the helping columns. To achieve that I merged the helping formulas with final formula. But it gives an error. Can yo guys help me to understand the mistake that I've made? Or can you suggest any other way to achieve this.
=OFFSET(J11,,,MATCH(INDEX(FILTER(J11#, (J11# > INDEX(J11#, ROW(J11#)-ROW(J11)))(ROW(J11#) > 11)),COUNTA(FILTER(J11#, (J11# > INDEX(J11#, ROW(J11#)-ROW(J11)))(ROW(J11#) > 11)))),J11#,0)-1)