Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Array Function
Well, now it is working but I still don't understand how it works.
- John |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Please add an INTERPOLATE function. For vector or array data. | Excel Worksheet Functions | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
need some help with an array function | Excel Discussion (Misc queries) | |||
Using AND function within an array formula | Excel Worksheet Functions | |||
Array Function with VLOOKUP | Excel Worksheet Functions |