Excel formula to continue an absolute reference

[kileer13]

I am unsure how to word this title as its hard to explain what I am trying to do in excel terms, so sorry in advance. Ill explain what im doing in excel because I cant think of any other way to explain it. I have a column of multiple funds, and I need to add 2 more rows for each fund. So for example I want List 1 to turn into List 2 via a formula.
List 1------------List 2
242L------------242L
316L------------242L
435L------------242L
-----------------316L
-----------------316L
-----------------316L
-----------------435L
-----------------435L
-----------------435L

[NBVC]

Try:

=INDEX($A$1:$A$3,MOD(INT((ROW()-ROW($A$1))/3),3)+1)

where A1:A3 is your original list, then copy down as far as needed to get all items repeated 3 times each.

[kileer13]

Ok thanks that worked quite well. Now I am trying to expand on this by multiplying those cells by a set percentage and a set of 3 dollar amounts. Here is the formula to give you a better idea of what I am trying to accomplish. I know I cant use the absolute amounts, could I use the Index function to do something similar for this?


=IF(A7=$F$7,ROUND($H$7*$R$19,2),0)
=IF(A8=$F$7,ROUND($H$7*$R$20,2),0)
=IF(A9=$F$7,ROUND($H$7*$R$21,2),0)
=IF(A10=$F$8,ROUND($H$8*$R$19,2),0)
=IF(A11=$F$8,ROUND($H$8*$R$20,2),0)
=IF(A12=$F$8,ROUND($H$8*$R$21,2),0)
=IF(A13=$F$9,ROUND($H$9*$R$19,2),0)
=IF(A14=$F$9,ROUND($H$9*$R$20,2),0)
=IF(A15=$F$9,ROUND($H$9*$R$21,2),0)

[NBVC]

Perhaps?

Please Login or Register  to view this content.

[kileer13]

Yes works perfectly I am just trying to understand how to extend it for 43 cells, I thought the +1 at the end would extend the index as I continued but I am guessing that is not what it was actually for. I extended your index ranges but I dont really understand what the other functions are doing. Could you explain these functions a little more? And how they are giving me what I want? I can understand the index and the /3 but im lost at the mod function and int function.

[kileer13]

Ok I figured out how to extend the formula to all the cells with the Int function and I understand the formula a little more playing with it. If you could elaborate on the mod function and the subtraction of the row functions I would appreciate it!

[NBVC]

the ROW() function, like (ROW($F7)-ROW($F$7) is used as a counter... starting at 0, as you drag down the first part ROW($F7) increases by one... The MOD() function then takes the remainder of that after dividing by 3 (repeat count).. The INT() takes the whole number part of that remainder... so for every 3, the INT() result increases by one.

You can use the Formula Evaluation tool, found in Tools|Formula Auding section to check each step and see the evaluation process.