Array Function
Is there a function that will create an array based on values in two
other cells? Give: A1 = 3 A2 = 10 Create: A3 = {10,10,10} If either A1 or A2 changes, then the result of the array in A3 would change. Thanks. - John |
Array Function
Try something like this:
A1: (number of array elements) A2: (value to assign each element) B1: =IF(ROW(A1:INDEX(A:A,A1,1)),A2) Note: To commit that array formula hold down the [Ctrl][Shift] keys and press [Enter]. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "John Michl" wrote: Is there a function that will create an array based on values in two other cells? Give: A1 = 3 A2 = 10 Create: A3 = {10,10,10} If either A1 or A2 changes, then the result of the array in A3 would change. Thanks. - John |
Array Function
Ron Coderre wrote...
Try something like this: A1: (number of array elements) A2: (value to assign each element) B1: =IF(ROW(A1:INDEX(A:A,A1,1)),A2) Note: To commit that array formula hold down the [Ctrl][Shift] keys and press [Enter]. .... Since you're entering this formula only into cell B1, when A1 is a nonnegative number it'd always return the same result as the much simpler =A2. When A1 is anything else, it'd return #VALUE!. The only way to make it an array result is to enter it into multiple cells at the same time as an array formula. If the OP needed a term that could be used in longer formulas, it could be reduced to =A2*ROW(A1:INDEX(A:A,A1))^0 |
Array Function
Slight problem, Ron and Harlan. In cell A1 I have a formula that
determines the number of elements. Your formulas do not work if there is a formula in cell A1. If I replace the formula with an actual value, your formulas do work. I'm not sure understand why your formulas work so I can't troubleshoot or modify. What purpose do the ROW and INDEX functions play in your solutions? - John |
Array Function
Well, now it is working but I still don't understand how it works.
- John |
Array Function
The ROW and INDEX functions are used to coerce Excel into creating a
sequential array of values (from 1 through the limit you entered in A1. You can see how they work by doing this: 1)Edit the cell 2)Select this part of the formula: ROW(A1:INDEX(A:A,A1,1)) 3)Press the [F9] key If A1 contains 4, you'll see this: {1;2;3;4} Press the [Esc] key to cancel the edits. Change the value of A1 and repeat those steps. Does that help? *********** Regards, Ron XL2002, WinXP-Pro "John Michl" wrote: Slight problem, Ron and Harlan. In cell A1 I have a formula that determines the number of elements. Your formulas do not work if there is a formula in cell A1. If I replace the formula with an actual value, your formulas do work. I'm not sure understand why your formulas work so I can't troubleshoot or modify. What purpose do the ROW and INDEX functions play in your solutions? - John |
Array Function
Thanks, that helps.
Now, how can I reference this in a chart data range. If I edit the formula and press F9, I see the array. However, I'm trying to use this as Values in a Source Data range in a chart. In the chart, I'll edit the Values and select the cell with the array but only the first value appears in the chart. If I type in the array ={1;2;3;4} in the Values field, it displays fine. - John |
All times are GMT +1. The time now is 12:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com