Auto fill my whole grid

[leisuredog]

I have a grid and I know the costs of the item, in all of the four corners I want excel to calculate the mean averages & fill in the other cells

 

[dave2018]

Please uplaod or post an example of your data, and specify in which cells u need the formula...

We are happy to help, but we need to know what help u need!

 

[keithmct]

this is just what I also need. Did you ever figure it out?

 

[shg]

Name a 2 x 2 range Corners and enter the corner values.

Name a rectangular range Tbl and name the top-left cell TopLeft.

Create two named formulas:

​

x Refers to: =(COLUMN() - COLUMN(Sheet1!TopLeft)) / (COLUMNS(Sheet1!Tbl) - 1)​

​

y Refers to: =(ROW() - ROW(Sheet1!TopLeft)) / (ROWS(Sheet1!Tbl) - 1)​

Then enter the formula in TopLeft and copy right and down:

=MMULT(MMULT(CHOOSE({1,2}, 1 - y, y), Corners), CHOOSE({1;2}, 1 - x, x))​

And ...

	A​	B​	C​	D​	E​	F​	G​	H​	I​	J​
1​	1.00​	0.50​
2​	0.00​	1.00​
3​
4​	1.00​	0.94​	0.89​	0.83​	0.78​	0.72​	0.67​	0.61​	0.56​	0.50​
5​	0.90​	0.86​	0.82​	0.78​	0.74​	0.71​	0.67​	0.63​	0.59​	0.55​
6​	0.80​	0.78​	0.76​	0.73​	0.71​	0.69​	0.67​	0.64​	0.62​	0.60​
7​	0.70​	0.69​	0.69​	0.68​	0.68​	0.67​	0.67​	0.66​	0.66​	0.65​
8​	0.60​	0.61​	0.62​	0.63​	0.64​	0.66​	0.67​	0.68​	0.69​	0.70​
9​	0.50​	0.53​	0.56​	0.58​	0.61​	0.64​	0.67​	0.69​	0.72​	0.75​
10​	0.40​	0.44​	0.49​	0.53​	0.58​	0.62​	0.67​	0.71​	0.76​	0.80​
11​	0.30​	0.36​	0.42​	0.48​	0.54​	0.61​	0.67​	0.73​	0.79​	0.85​
12​	0.20​	0.28​	0.36​	0.43​	0.51​	0.59​	0.67​	0.74​	0.82​	0.90​
13​	0.10​	0.19​	0.29​	0.38​	0.48​	0.57​	0.67​	0.76​	0.86​	0.95​
14​	0.00​	0.11​	0.22​	0.33​	0.44​	0.56​	0.67​	0.78​	0.89​	1.00​

Link

 

[keithmct]

Hi shg,
I did exactly the same as above and every cell has 1.7555556

edit: scratch that, I didn't have TopLeft named properly. But now I do and every cell in Tbl reads 0.8444444

 

[shg]

Post the simplest version possible of a workbook that illustrates the problem.

 

[keithmct]

grid.xlsx
	A	B	C	D	E	F	G	H	I	J
1	1.00	0.50			0.444444
2	0.00	1.00			-0.2
3
4	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
5	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
6	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
7	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
8	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
9	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
10	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
11	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
12	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
13	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
14	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444	0.8444444
15
Sheet1

Cell Formulas
Range	Formula
E1	E1	=(COLUMN()-COLUMN(grid.xlsx!TopLeft))/(COLUMNS(grid.xlsx!Tbl)-1)
E2	E2	=(ROW()-ROW(grid.xlsx!TopLeft))/(ROWS(grid.xlsx!Tbl)-1)
A4:J14	A4	=MMULT(MMULT(CHOOSE({1,2}, 1 - y, y), Corners), CHOOSE({1;2}, 1 - x, x))

Named Ranges
Name	Refers To	Cells
Corners	=Sheet1!$A$1:$B$2	A4:J14
Tbl	=Sheet1!$A$4:$J$14	E1:E2
TopLeft	=Sheet1!$A$4	E1:E2
x	=Sheet1!$E$1	A4:J14
y	=Sheet1!$E$2	A4:J14

 

[shg]

x and y should be defined in Name Manager. Their values vary by position in the table.

 

[keithmct]

How do I do that? I just typed the formula in E1 & E2 and then named them in the name box.

 

[keithmct]

or do I just type the formula in the refers to part?

edit: yes that worked. thanks for your help and prompt responses.