Fill out 'complete correlation table"

[mikesal57]

Hi All...

I need the correlation table to be completely fill out so that I can ADD the full columns to do a few functions.
Is there a way to copy and transpose the numbers over?

Thxs

 

[BrianGGG]

Can you provide an example of what you want to happen ?

 

[mikesal57]

This is the normal result you get after you compile info...

I need those blank boxes filled in ...its just the same numbers on the other side of the 1's

https://voxeu.org/article/synchronisation-business-cycles

 

[BrianGGG]

Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

	A	B	C	D	E
50		Australia	Belgium	Finland	France
51	Australia	1
52	Belgium	0.97	1
53	Finland	0.97	0.98	1
54	France	0.93	0.95	0.97	1

<tbody>
</tbody>

Summary​

Here's a table that completes the blank spaces along with formulas.
I created the formula in one cell and just pasted it down and across:

	A	B	C	D	E
57		Australia	Belgium	Finland	France
58	Australia	1	0.97	0.97	0.93
59	Belgium	0.97	1	0.98	0.95
60	Finland	0.97	0.98	1	0.97
61	France	0.93	0.95	0.97	1

<tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula B58 =IF(NOT(ISBLANK(B51)),B51,OFFSET($B$51,COLUMNS($B$51:B51)-1,ROWS($B$51:B51)-1)) C58 =IF(NOT(ISBLANK(C51)),C51,OFFSET($B$51,COLUMNS($B$51:C51)-1,ROWS($B$51:C51)-1)) D58 =IF(NOT(ISBLANK(D51)),D51,OFFSET($B$51,COLUMNS($B$51:D51)-1,ROWS($B$51:D51)-1)) E58 =IF(NOT(ISBLANK(E51)),E51,OFFSET($B$51,COLUMNS($B$51:E51)-1,ROWS($B$51:E51)-1)) B59 =IF(NOT(ISBLANK(B52)),B52,OFFSET($B$51,COLUMNS($B$51:B52)-1,ROWS($B$51:B52)-1)) C59 =IF(NOT(ISBLANK(C52)),C52,OFFSET($B$51,COLUMNS($B$51:C52)-1,ROWS($B$51:C52)-1)) D59 =IF(NOT(ISBLANK(D52)),D52,OFFSET($B$51,COLUMNS($B$51:D52)-1,ROWS($B$51:D52)-1)) E59 =IF(NOT(ISBLANK(E52)),E52,OFFSET($B$51,COLUMNS($B$51:E52)-1,ROWS($B$51:E52)-1)) B60 =IF(NOT(ISBLANK(B53)),B53,OFFSET($B$51,COLUMNS($B$51:B53)-1,ROWS($B$51:B53)-1)) C60 =IF(NOT(ISBLANK(C53)),C53,OFFSET($B$51,COLUMNS($B$51:C53)-1,ROWS($B$51:C53)-1)) D60 =IF(NOT(ISBLANK(D53)),D53,OFFSET($B$51,COLUMNS($B$51:D53)-1,ROWS($B$51:D53)-1)) E60 =IF(NOT(ISBLANK(E53)),E53,OFFSET($B$51,COLUMNS($B$51:E53)-1,ROWS($B$51:E53)-1)) B61 =IF(NOT(ISBLANK(B54)),B54,OFFSET($B$51,COLUMNS($B$51:B54)-1,ROWS($B$51:B54)-1)) C61 =IF(NOT(ISBLANK(C54)),C54,OFFSET($B$51,COLUMNS($B$51:C54)-1,ROWS($B$51:C54)-1)) D61 =IF(NOT(ISBLANK(D54)),D54,OFFSET($B$51,COLUMNS($B$51:D54)-1,ROWS($B$51:D54)-1)) E61 =IF(NOT(ISBLANK(E54)),E54,OFFSET($B$51,COLUMNS($B$51:E54)-1,ROWS($B$51:E54)-1)) <tbody> </tbody>

<tbody>
</tbody>

The formula pattern is:
If a cell has data in it, carry it forward
If not, reverse the columns and rows to get the new answer


What do you think?


BrianGGG

 

[mikesal57]

Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

	A	B	C	D	E
50		Australia	Belgium	Finland	France
51	Australia	1
52	Belgium	0.97	1
53	Finland	0.97	0.98	1
54	France	0.93	0.95	0.97	1

<tbody>
</tbody>

Summary​

Here's a table that completes the blank spaces along with formulas.
I created the formula in one cell and just pasted it down and across:

	A	B	C	D	E
57		Australia	Belgium	Finland	France
58	Australia	1	0.97	0.97	0.93
59	Belgium	0.97	1	0.98	0.95
60	Finland	0.97	0.98	1	0.97
61	France	0.93	0.95	0.97	1

<tbody>
</tbody>

Summary​

Worksheet Formulas Cell Formula B58 =IF(NOT(ISBLANK(B51)),B51,OFFSET($B$51,COLUMNS($B$51:B51)-1,ROWS($B$51:B51)-1)) C58 =IF(NOT(ISBLANK(C51)),C51,OFFSET($B$51,COLUMNS($B$51:C51)-1,ROWS($B$51:C51)-1)) D58 =IF(NOT(ISBLANK(D51)),D51,OFFSET($B$51,COLUMNS($B$51:D51)-1,ROWS($B$51:D51)-1)) E58 =IF(NOT(ISBLANK(E51)),E51,OFFSET($B$51,COLUMNS($B$51:E51)-1,ROWS($B$51:E51)-1)) B59 =IF(NOT(ISBLANK(B52)),B52,OFFSET($B$51,COLUMNS($B$51:B52)-1,ROWS($B$51:B52)-1)) C59 =IF(NOT(ISBLANK(C52)),C52,OFFSET($B$51,COLUMNS($B$51:C52)-1,ROWS($B$51:C52)-1)) D59 =IF(NOT(ISBLANK(D52)),D52,OFFSET($B$51,COLUMNS($B$51:D52)-1,ROWS($B$51:D52)-1)) E59 =IF(NOT(ISBLANK(E52)),E52,OFFSET($B$51,COLUMNS($B$51:E52)-1,ROWS($B$51:E52)-1)) B60 =IF(NOT(ISBLANK(B53)),B53,OFFSET($B$51,COLUMNS($B$51:B53)-1,ROWS($B$51:B53)-1)) C60 =IF(NOT(ISBLANK(C53)),C53,OFFSET($B$51,COLUMNS($B$51:C53)-1,ROWS($B$51:C53)-1)) D60 =IF(NOT(ISBLANK(D53)),D53,OFFSET($B$51,COLUMNS($B$51:D53)-1,ROWS($B$51:D53)-1)) E60 =IF(NOT(ISBLANK(E53)),E53,OFFSET($B$51,COLUMNS($B$51:E53)-1,ROWS($B$51:E53)-1)) B61 =IF(NOT(ISBLANK(B54)),B54,OFFSET($B$51,COLUMNS($B$51:B54)-1,ROWS($B$51:B54)-1)) C61 =IF(NOT(ISBLANK(C54)),C54,OFFSET($B$51,COLUMNS($B$51:C54)-1,ROWS($B$51:C54)-1)) D61 =IF(NOT(ISBLANK(D54)),D54,OFFSET($B$51,COLUMNS($B$51:D54)-1,ROWS($B$51:D54)-1)) E61 =IF(NOT(ISBLANK(E54)),E54,OFFSET($B$51,COLUMNS($B$51:E54)-1,ROWS($B$51:E54)-1)) <tbody> </tbody>

<tbody>
</tbody>

The formula pattern is:
If a cell has data in it, carry it forward
If not, reverse the columns and rows to get the new answer


What do you think?


BrianGGG

Thxs sooooooo much....worked like a charm...

Mike