HELP with INDIRECT formula

[D_Spark]

Hello

Can anyone support me and figure out why the formula below is not working?
What I want is for the formula to work, dependant on what value is entered in cell O2 on a worksheet


=MODE(INDIRECT("C"&ROW()-o2)&":"&INDIRECT("C"&ROW()-1))

 

[Yongle]

Try

=MODE(OFFSET(C1,ROW()-1-O2,0):OFFSET(C1,ROW()-2,0))

 

[Yongle]

I should have said that the above formula exactly replaces your formula
(ie do not amend C1 - references within the formula are relative to C1)

 

[pgc01]

Also, non-volatile, try:

=MODE(INDEX(C:C,ROW()-O2):INDEX(C:C,ROW()-1))