Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET vs INDEX(MATCH(...)) | Excel Discussion (Misc queries) | |||
using OFFSET(INDEX(MATCH))) | Excel Discussion (Misc queries) | |||
INDEX, MAX, OFFSET, MATCH | Excel Worksheet Functions | |||
index match offset? | Excel Worksheet Functions | |||
Index, Match, Offset? Not sure which to use | Excel Worksheet Functions |