Forum Discussion
Second to last value from table with condition
Hi experts Can you help me with the below? I have a list of cash values in a table, example as follows by a rotating week number (1-4). A ------------ B 1 ----------- 40 2------------- ...
You can try these formulas.
=IFERROR(LARGE(IF(($D$1412:$D$1761=$M$1769)*($Z$1412:$Z$1761<>"")*($L$1412:$L$1761<>""),ROW($1412:$1761)-1411),ROW(1:1))+1411,"")This is the formula for the row of the n-th to last value.
=IFERROR(INDEX($Z$1412:$Z$1761,LARGE(IF(($D$1412:$D$1761=$M$1769)*($Z$1412:$Z$1761<>"")*($L$1412:$L$1761<>""),ROW($1412:$1761)-1411),ROW(1:1))),"")This is the formula that returns the corresponding value.
The formulas are in cells L1771 and M1771 and are filled down.
Hi, Once again I need your help. In fact my file has data in the rows above the data rows for which formula was applied. Because of this the row number keeps changing as I continue to add some data. Is there any way that the row number in the formula keeps changing relative to the data range of the formula. The screenshot of row number in the formula is circled for your easy reference as also the Excel file in which you did suggest the formula. OliverScheurich
.png){kind=link}