Help with Excel array in Index

E

Eldredge

Is it possible to create an array using the value in a cell as the first cell of the array and the value in another cell as the last cell of the array? For example, if I have a formula in D1 (or some other cell) that gives a result of $B$3, and a formula in D2 (or some other cell) that give a result of $B$158 (the results will always be in the same column if that matters); can I create an Index function in E5 (or some other cell) using $B$3 from D1 and $B$158 from D2 to create the array? Index(D1result:2result,row_number)? The results n D1 and D2 will change.

I can't seem to find a way to use the result in D1 as the first value and the result in D2 as the second value to create the array. This will be used with Index/Match if it matters.

Thanks for any help.
 
Eldredge said:
Is it possible to create an array using the value in a cell as the first cell of the array and the value in another cell as the last cell of the array? For example, if I have a formula in D1 (or some other cell) that gives a result of $B$3, and a formula in D2 (or some other cell) that give a result of $B$158 (the results will always be in the same column if that matters); can I create an Index function in E5 (or some other cell) using $B$3 from D1 and $B$158 from D2 to create the array? Index(D1result:2result,row_number)? The results n D1 and D2 will change.

I can't seem to find a way to use the result in D1 as the first value and the result in D2 as the second value to create the array. This will be used with Index/Match if it matters.

Thanks for any help.
More...

This is pretty involved, but the following cell contents will tease out a $5 premium of a |20|-delta vertical spread from a broad table of such spreads, by finding the date-series, finding the delta, and then reading over to find the spread premium. It is for OpenOffice, but what you're desiring for Excel will look very much like it.
=VLOOKUP(-J241;J164:M234;3;1)+INDIRECT(ADDRESS(163+MATCH(VLOOKUP(-J241;J164:M234;4;1);M164:M234;0)-1;12;4))

Index is a great start, but VLookUp, HLookUp, Cell, Cellcontents, Address, Match, etc will all come into play. Make yourself a little test spreadsheet with all of these pieces in working order, and then start to put them together to get what you need. PATIENCE is a virtue, here.
 
tommcginnis said:
This is pretty involved, but the following cell contents will tease out a $5 premium of a |20|-delta vertical spread from a broad table of such spreads, by finding the date-series, finding the delta, and then reading over to find the spread premium. It is for OpenOffice, but what you're desiring for Excel will look very much like it.
=VLOOKUP(-J241;J164:M234;3;1)+INDIRECT(ADDRESS(163+MATCH(VLOOKUP(-J241;J164:M234;4;1);M164:M234;0)-1;12;4))

Index is a great start, but VLookUp, HLookUp, Cell, Cellcontents, Address, Match, etc will all come into play. Make yourself a little test spreadsheet with all of these pieces in working order, and then start to put them together to get what you need. PATIENCE is a virtue, here.
More...

Thanks Tom. What I want to do seems like it should be simple - use the contents of one cell to identify the first cell of the array, and the contents of the 2nd cell to identify the last cell in the array. I've tried Cell, Cellcontents, Address, Concantenate, etc., but I haven't been able to find anything that will extract the value in the cell as the coordinate for the array. I don't give up though, so I will figure something out. Thanks again.
 
tiddlywinks said:
The CHOOSE function is probably your friend based on your description.
Try a web search for "excel non contiguous array" or similar for examples.
More...

Thanks tiddlywinks. I am looking at that. The array is actually contiguous - it is a section of one column, but which section of the column that is the array keeps moving down. I would just like to have the first cell and the last cell of the column/array be identified by the contents of two cells.
 
Just in case someone comes looking here, I found what I needed: =SUM(INDIRECT(<start cell here> & ":" & <end cell here>))

Thanks again for the help.
 
You must log in or register to reply here.
Back
Top