Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX / MOD() question..
hi, not sure if need use of MOD, or INDEX / OFFSET..
I have 10 double columns of numbers, only need the 1st col of every other column (lo & hi figures), to figure into a portion of a different formula. will need to be able to externally (by a fixed/ absolute cell) select the 1st column, from every other (2) column set of 20 columns. will only use one column for the -end formula I need. (specifically, FE - FX need to be able to select 1 column, out of every other column starting at col FE, FG FI FK..), have a fixed cell where designating number of columns to offset: $C$2 not sure how to apply, but think MOD is needed? thanks have 2nd portion of formula figured out, 1st portion X: trying to grab data from 2 'LO' / hi columns (out of 20 lo-hi columns): End Fromula: =X (every other col by $C$2: FE FG FI FK..) /OFFSET(EF9,0,$C$2-1)%-100 would answer be something like: SUMPRODUCT(INDEX(MOD( ... /OFFSET(EF9,0,$C$2-1)%-100 some examples of similar formula's: =SUM(--(MOD(COLUMN($FE9:$FX9),2)MOD(COLUMN($FE9),2)),$FE 9:$FX9) =SUMPRODUCT(--(MOD(COLUMN($FE9:$FX9),2)=MOD(COLUMN($FE9),2)),$FE 9:$FX9,$FF9:$FW9) INDEX: cumulative sum of x days percent change. =IF(J9=0,"",SUMPRODUCT(EF9:INDEX(EF9:EX9,1,$C$2)/EG9:INDEX(EG9:EY9,1,$C$2)%-100)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
INDEX / MOD() question..
Different way of putting it..
=INDEX(FE450:FX450,1,$C$2) works for single rows, but double row sets of 2 columns - numbers lo & hi. 1 in $C$2 correctly gets 1st col. need 2 to select 3rd col (2nd 'lo' in 2nd set of 2 columns) etc thru 10 sets avail of 20 columns. "Nastech" wrote: hi, not sure if need use of MOD, or INDEX / OFFSET.. I have 10 double columns of numbers, only need the 1st col of every other column (lo & hi figures), to figure into a portion of a different formula. will need to be able to externally (by a fixed/ absolute cell) select the 1st column, from every other (2) column set of 20 columns. will only use one column for the -end formula I need. (specifically, FE - FX need to be able to select 1 column, out of every other column starting at col FE, FG FI FK..), have a fixed cell where designating number of columns to offset: $C$2 not sure how to apply, but think MOD is needed? thanks have 2nd portion of formula figured out, 1st portion X: trying to grab data from 2 'LO' / hi columns (out of 20 lo-hi columns): End Fromula: =X (every other col by $C$2: FE FG FI FK..) /OFFSET(EF9,0,$C$2-1)%-100 would answer be something like: SUMPRODUCT(INDEX(MOD( ... /OFFSET(EF9,0,$C$2-1)%-100 some examples of similar formula's: =SUM(--(MOD(COLUMN($FE9:$FX9),2)MOD(COLUMN($FE9),2)),$FE 9:$FX9) =SUMPRODUCT(--(MOD(COLUMN($FE9:$FX9),2)=MOD(COLUMN($FE9),2)),$FE 9:$FX9,$FF9:$FW9) INDEX: cumulative sum of x days percent change. =IF(J9=0,"",SUMPRODUCT(EF9:INDEX(EF9:EX9,1,$C$2)/EG9:INDEX(EG9:EY9,1,$C$2)%-100)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Match question. | Excel Worksheet Functions | |||
Index() & Row() Question | Excel Discussion (Misc queries) | |||
Match or Index Question | Excel Worksheet Functions | |||
Index question | Excel Worksheet Functions | |||
Match + Index(?) Question | Excel Discussion (Misc queries) |