Array Constant: How do I reference each value in a formula

[notso]

I have an array constant {1000,1.2,2%} in cell A1
S = 1000
P = 1.2
R = 2%
How do I reference the array constant to create this formula:
=(S/P)-((S/P)*R)

 

[ShaneDevenshire]

Hi,

I can't duplicate your entry. First, to be an array it must be entered
starting with an =
for example ={1,2,3}
Second, you show the last argument as 2% - Excel won't accept the % in and
array entry.
Maybe you are typing {1000,1.2,2%} into the cell? If so, it is not an
array, its just a bunch of text. You could enter it as ={1000,1.2,0.02}

 

[ShaneDevenshire]

Hi again,

If you really want an array constant, store it as a range name: Choose
Insert, Name, Define, enter A for Names in workbook and on the Refers to line
enter
={1000,1.2,0.02}

From the spreadheet you can reference the different components by using INDEX:
=INDEX(A,1,1)
=INDEX(A,1,2)
=INDEX(A,1,3)

 

[Mike H]

Hi,

I'm not sure that you can have an arrray constant like this but you can like
this

{1000,1.2,0.02}

Select a1,b1,c1 and enter this in the formula bar
={1000,1.2,0.02}
Commit with Ctrl+Shift+enter and those values appear in the 3 cells
Select A1 insert|Name|Define and call the cell S
Select B1 insert|Name|Define and call the cell P
Select C1 insert|Name|Define and call the cell Q
You can't use R because that's an illegal name

Your formula
=(S/P)-((S/P)*Q)

now works but note it didn't need to be an array constant to do that you
could have simply entered those values in each cell and named them and then
you could have used 2%.

Mike