Help with: INDEX() / MOD ?

[Nastech]

trying to select every other 1st column, of 2 columns sets.
20 columns, 10 sets. only use one at a time for formula trying to make.
using fixed cell $C$2 to enter 1-10. (first col is a low number, 2nd col is
hi).
selecting 1 column at a time works for the following example.

=INDEX(E49:X9,1,$C$2)

but for example, need 2 to select column G, 3 column I ..
do I need use of / get help with MOD() ?
thanks.

 

[Sheeloo]

What do you want to achieve?

First tell us what you want to do then how you are trying to achieve it...

 

[T. Valko]

Try this:

=INDEX(range,row,C2*2-1)

 

[Nastech]

was trying to figure out: MOD() INDEX() or OFFSET(),
have been experimenting, not good with MOD.

STILL NEED HELP with PROBLEM 2 below,
buy may have figured 1st formula out.
since working with double columns, did not realize answer might be worker
cell $C$2 would just have =($C$2*2)-1
sound right??

problem EC for EF, not for any other offset.
=IF(E9=0,"",EC9/OFFSET(EF9,0,$C$2-1)%-100)

answer: ($C$3 has: =($C$2*2)-1 note: EF & FE 2 different areas
=IF(E471=0,"",IF($C$2=1,EC9/EF9,
OFFSET(FE9,0,$C$3-1)/OFFSET(EF9,0,$C$2-1))%-100)

(percent change equation)

PROBLEM 2:
orignal needs use of MOD() ? to use the FROM/TO of each of the back up
columns FE to FX (or E thru X here)

=IF(E9=0,"",ED9/EC9%-100)

NEEDS to be something like:
=IF(E9=0,"",IF($C$2=1,ED9/EC9%-100,
MOD/INDEX.. ?? needs:

If C2 =
2: FF/FE division
3: FH/FG
4: FJ/FI
5: FL/FK
6: FN/FM
7: FP/FO
8: FT/FS
9: FV/FU
10: FX/FW

too much? sorry.. really need the help, thanks.

--------------------
ORIGINAL PROB INFO: solved by work around..
have history data need to access
columns E thru X is 20 columns of 10 sets of 2 columns, each 2 columns is 1
days values range. need to work only the 1st column (selectively from
entering 1-10 in $C$2).

where starting with 20 columns, the following formula would select 1 of each
of all 20 columns by using entry of 1 thru 20.

=INDEX(E49:X9,1,$C$2)

but want to select 1 day at a time / (choice of consider 2 columns as each
choice, 1 thru 10, and get the first column). the following is a line up of
what need for results..

1 gets col E of EF
2 gets G of GH
3 I of IJ
4 K KL
5 M MN
6 O OP
7 Q QR
8 S ST
9 U UV
10 W WX

 

[ShaneDevenshire]

Hi,

When you say "select" what do you mean?, formulas do not "select" ranges,
they uses ranges to calculate.

Suppose you want to return the value on the first row of every other column
that you indicate by your entry in cell C2, then the formula would be:

=INDEX(E9:X49,1,$C$2*2-1)

but if you want to do something like sum the whole column then the formula
would be

=SUM(INDEX(E9:X49,,$C$2*2-1))


If this helps, then please click the Yes button.

 

[Nastech]

(other slight question below)
thanks both !!, would seem to be the right thing.. have to test later, but
wondered if wasn't something alittle simpler than Mod stuff..

as far as selection term goes, I just meant the selection that would occur
from $C$2

what was working on last hour would be a (still not right I think, but
correction on % change formula portion trying to convey, to anyone who
trying to follow, is partial? correction from previous post), is:

=IF(E454=1,IF($C$2=1,EC454/EF454%,
OFFSET(FE454,0,$C$3)/OFFSET(EF454,0,$C$2-1)%),100)-100

Question is this something seen before for part came up with for 1st to IF's,
concept for cascading "IF's", have been using for much longer formula's
have saved much space for many common variables. only pay back I got
thanks.

 

[Nastech]

Thanks much, that should work, other:

(other slight question below)
thanks both !!, would seem to be the right thing.. have to test later, but
wondered if wasn't something alittle simpler than Mod stuff..

as far as selection term goes, I just meant the selection that would occur
from $C$2

what was working on last hour would be a (still not right I think, but
correction on % change formula portion trying to convey, to anyone who
trying to follow, is partial? correction from previous post), is:

=IF(E454=1,IF($C$2=1,EC454/EF454%,
OFFSET(FE454,0,$C$3)/OFFSET(EF454,0,$C$2-1)%),100)-100

Question is this something seen before for part came up with for 1st to IF's,
concept for cascading "IF's", have been using for much longer formula's
have saved much space for many common variables. only pay back I got
thanks.

 

[T. Valko]

Not sure what you're asking but here's my thoughts:

=IF(E454=1,IF($C$2=1,EC454/EF454%,OFFSET(FE454,0,$C$3)/OFFSET(EF454,0,$C$2-1)%),100)-100

IF(....,IF(.....

That is the same as IF(AND(.....),

If E454=1 AND $C$2=1 then perform the division EC454/EF454% - 100

If E454=1 AND $C$2<>1 then perform the division of the offsets - 100

If E454<>1 result is 100 - 100

That last part is redundant so you can just do this:

=IF(E454=1,IF($C$2=1,EC454/EF454%,OFFSET(FE454,0,$C$3)/OFFSET(EF454,0,$C$2-1)%)-100,0)

 

[ShaneDevenshire]

Hi,

Sorry I don't follow the new question. If you are asking have we seen ideas
where using a control cell like C2 and C3 to reduce the complexity of a much
longer formula - I can only speak for me, personally I use a lot of tricks to
keep formulas shorter and simplier as do many of the other contributors.
There is always something new to learn and I think most of the contributors
are always looking for such goodies.


I'm glad we have been able to help on the other questions.

 

[Nastech]

neat.. didn't catch the -100,0
was mixing a few subjects, should have shown expanded on IF( IF(
we both -get the and(.. what mean, if you didn't see.. (should have
included dif example for if(if is amount of sub-items that get anded to the
first item(s) cascade AND's for saving space in longer versions:

=IF(AND(C9=1,N9=1,EE9>DW9),IF(BV9<$AU$4,IF(BZ9<$AU$6,IF(U9=1,4,3),2),1),0)

=IF(J9=1,IF(($P$2=1)*(BV9<$AU$4),IF(($P$3=1)*(BZ9<$AU$6),2,1),0),0)

=IF(AND(D9=1,Q9=1,U9=1,HJ9=0,IF($R$3>0,DV9>$AA$7,TRUE)),IF(P9=1,IF((BN9=0)*(O9=1),1,2),3),9)


has to be in a topic - subtopic fashion, allows combining other columns same
stuff,
use greater / less than, to meet requirements. (not refering to $C$2/$C$3..
my fault).

 

[Nastech]

neat.. didn't catch the -100,0
was mixing a few subjects, should have shown expanded on IF( IF(
we both -get the and(.. what mean, if you didn't see.. (should have
included dif example for if(if is amount of sub-items that get anded to the
first item(s) cascade AND's for saving space in longer versions:

=IF(AND(C9=1,N9=1,EE9>DW9),IF(BV9<$AU$4,IF(BZ9<$AU$6,IF(U9=1,4,3),2),1),0)

=IF(J9=1,IF(($P$2=1)*(BV9<$AU$4),IF(($P$3=1)*(BZ9<$AU$6),2,1),0),0)

=IF(AND(D9=1,Q9=1,U9=1,HJ9=0,IF($R$3>0,DV9>$AA$7,TRUE)),IF(P9=1,IF((BN9=0)*(O9=1),1,2),3),9)


has to be in a topic - subtopic fashion, allows combining other columns same
stuff,
use greater / less than, to meet requirements. (not refering to $C$2/$C$3..
my fault).

 

[Nastech]

neat.. didn't catch the -100,0
was mixing a few subjects, should have shown expanded on IF( IF(
we both -get the and(.. what mean, if you didn't see.. (should have
included dif example for if(if is amount of sub-items that get anded to the
first item(s) cascade AND's for saving space in longer versions:

=IF(AND(C9=1,N9=1,EE9>DW9),IF(BV9<$AU$4,IF(BZ9<$AU$6,IF(U9=1,4,3),2),1),0)

=IF(J9=1,IF(($P$2=1)*(BV9<$AU$4),IF(($P$3=1)*(BZ9<$AU$6),2,1),0),0)

=IF(AND(D9=1,Q9=1,U9=1,HJ9=0,IF($R$3>0,DV9>$AA$7,TRUE)),IF(P9=1,IF((BN9=0)*(O9=1),1,2),3),9)


has to be in a topic - subtopic fashion, allows combining other columns same
stuff,
use greater / less than, to meet requirements. (not refering to $C$2/$C$3..
my fault).