OFFSET, INDEX, MOD.. ??
 

081013 OFFSET, INDEX, MOD.. ??

hi, have formula's, that wish to reference previous days values (adjustable),
with 2 formula's/ differenct setup, thanks in advance, 2nd formula problem
way below... similar

e.g.: formula 1: (where EC ED are lo-hi values, EF previous day value)
=IF(E9=0,"",EC9/EF9%-100)

change to:
=IF(E9=0,"",EC9/OFFSET(EF9,0,$C$2-1)%-100)
works if copy lo/hi values back to 2 columns: EC ED, but history values are
copy-paste values to new columns

(e.g.: GB to GK, 10 cols of 5 days lo-hi, every two cols NEED 1ST COL "Lo"
value in place of EC), or:

1 / today: EC/EF
2: GB/EG
3: GD/EH
4: GF/EI
5: GH/EJ

-----------------

2ND FORMULA:
=IF(E9=0,"",ED9/EC9%-100) start for day 1 (lo-hi)
2: GB/GC
3: GD/GE
4: GF/GH
5: GI/GJ



other examples of formulas that work trying to use as examples:

for x days cumulative %ch
=IF(J9=0,"",SUMPRODUCT(EF9:INDEX(EF9:EX9,1,$DM$6)/EG9:INDEX(EG9:EY9,1,$DM$6)%-100))

1 day at a time previous %ch
=IF(J9=0,"",OFFSET( EE9,0,$C$2-1)/OFFSET(EF9,0,$C$2-1)%-100)

adding every other column
=SUM(--(MOD(COLUMN($AP9:$BH9),2)MOD(COLUMN($AP9),2)),$AP 9:$BH9)