How do I create an array in excel using the "coordinate" values of each cell?

Question

Sorry if this is trivial, but I can't seem to get this to work. I have a column of values, say from A2 to A10, and a row of values, say from B1 to P1. I want to fill out the values of the array from B2 to P10 using a formula that references the values in the "x coordinate" and "y coordinate" of each cell in the array: for example, to fill out C5, I need to reference the "axis" values in A5 and C1. (If my formula were simple multiplication, this would give a multiplication table).

I have not been able to make it such that each column keeps every "column coordinate" from B1 to P1 constant, or such that each row keeps every "row coordinate" from A2 to A10 constant. I must be missing something. Thanks for the help.

Answer 1

You need to make the column absolute in one reference and the row absolute in the other.

In your example of a times table:

=$A2*B$1

This would go in B2.

Now as it is dragged/copied to the field the correct column and row will remain absolute($) and the other will change.

Answer 2

If you have SPILL functionality ("dynamic arrays") in your version of Excel, you can do the following and not need the copying to other cells:

=A2:A10*B1:P1

There would be no need for {CSE} entry unless your version does not have the SPILL functionality.

This would also have the benefit that the full array could then be addressed as a dynamic array rather than by address (B2# rather than B2:P10) in other formulas. I mention that NOT because it's easier to write or what-have-you, but because some functions will operate on a dynamic range differently (advantageously "differently") than a directly addressed range. Their outputs are different... not just things like the automatic adjustment of what's addressed and such.

Personally, and for decades, I avoided using {CSE} for such things, using Scott Craner's copy and paste to fill because the {CSE} approach has/had drawbacks I did not care for. (The same person that would sneer at you for merging two cells would lock 10,000 cells with a {CSE} formula! Lordy...) But it's an approach with the occasional benefit that might matter to what you need this for, maybe, so you could go that route. Just can't suggest it.