Forum Discussion

Jrm92's avatar
Jrm92
Copper Contributor
Apr 02, 2020

Offset

When there are different cells containing numerical data how do I get the data from the latest 5 cells?   I think it's offset but how to arrange this?   The value of the latest 5 cells should be ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 02, 2020

Jrm92 

For the model as here

it could be

=SUM(INDEX(2:2,1,AGGREGATE(14,6,1/ISNUMBER(2:2)*COLUMN(2:2),{5,4,3,2,1})))

AGGREGATE returns positions of the latest non-texts in row #2, INDEX takes values from these position and finally we SUM them. 

Resources