I saw this formula =OFFSET(R15,ROW()-20,F140) in sample excel sheet I am learning with
however I don’t know what the minus in the row part is used for , I understand the the offset function referencing r15 and column count is whatever figure is in f140 , what I don’t know is what row()-20 does ..
kindly explain
>Solution :
ROW() in this formula is simply returning the row number of the current cell. If you put =ROW() in cell X2022, it will return 2022. Using that as an example: the formula is effectively saying:
"look 2002 (2022-20) rows below R15 and
F140columns left(- value)
or right(+ value) and fetch me that value."
When copied down to the next row, the formula would say:
"look 2003 (2023-20) rows below R16 and
F141columns left(- value)
or right(+ value) and fetch me that value."