Excel Matrix

[Sal]

Hi:

Trying to create an interpolation(formula available) matrix using time
(rows , i.e 1,2..10) and the type (Item a, b, c, d) so that the
expanded matrix shows values in increments of 0.01, i.e.
1.01,1.02,1.03 etc. for each a, b, c, d. Can anyone help?

Thanks in advance

a b c d
1 1 2 3 4
2 2 4 6 8
3 6 12 18 24
4 9 18 27 36
5 14 20 30 40
6 20 22 33 44
7 30 24 36 48
8 46 26 40 53
9 68 29 43 58
10 103 32 48 64

 

[Pete_UK]

With your data and header row occupying A1:E11, put these headings in
the cells stated:

G1: Incr
H1: Locn
I1: A
J1: B
K1; C
L1: D

Then put 1.01 into G2 and fill this down to G900 in steps of 0.01 -
your last entry should be 9.99 (it must not exceed this). Then add
this formula to H2:

=MATCH(G2,$A$2:$A$11)

and copy this down - a quick way to do this is to double-click the
fill handle (the small black square in the bottom corner of the
cursor). As your original increments are simple numbers, you could
achieve this with =INT(G2), but the MATCH formula is more flexible if
you have irregular increments (like from an experiment).

Put this formula in I2:

=(INDEX($A$2:$E$11,$H2+1,COLUMN(B1))-INDEX($A$2:$E$11,$H2,COLUMN(B1)))/
(INDEX($A$2:$A$11,$H2+1)-INDEX($A$2:$A$11,$H2))*($G2-INDEX($A$2:$A
$11,$H2))+INDEX($A$2:$E$11,$H2,COLUMN(B1))

This will perform a linear interpolation, and can be copied across
into J2:L2. Then just copy I2, J2, K2 and L2 down each column by
double-clicking the fill handle.

Hope this helps.

Pete