Fill out 'complete correlation table"

M

mikesal57

Board Regular
Joined
Jul 6, 2011
Messages
193
Office Version
  1. 2016
Platform
  1. Windows
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
 
Last edited:

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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

synchronisation-business-cycles
https://voxeu.org/article/synchronisation-business-cycles
 
Last edited:
Upvote 0
Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</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:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]57[/TD]
[TD="align: right"][/TD]

[TD="align: center"]58[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.93[/TD]

[TD="align: center"]59[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]60[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]

[TD="align: center"]61[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​

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




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
 
Last edited:
Upvote 0
BrianGGG said:
Not 100% sure I know what the end result needs to be, but here's a possibility:

Given a table like this:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]50[/TD]
[TD="align: right"][/TD]

[TD="align: center"]51[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]52[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]53[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]54[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</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:

ABCDE
AustraliaBelgiumFinlandFrance
Australia
Belgium
Finland
France

<tbody>
[TD="align: center"]57[/TD]
[TD="align: right"][/TD]

[TD="align: center"]58[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]0.93[/TD]

[TD="align: center"]59[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]0.95[/TD]

[TD="align: center"]60[/TD]

[TD="align: right"]0.97[/TD]
[TD="align: right"]0.98[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0.97[/TD]

[TD="align: center"]61[/TD]

[TD="align: right"]0.93[/TD]
[TD="align: right"]0.95[/TD]
[TD="align: right"]0.97[/TD]
[TD="align: right"]1[/TD]

</tbody>
Summary​

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




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
Click to expand...


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

Mike
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Solved
Conditional calculation of added columns - seeking a cleaver solution.
Replies
9
Views
257
dreid1011
dreid1011
R
  • Question Question
Need help with random number generating
Replies
2
Views
561
royland27
R
Q
  • Question Question
Macro needed to insert 4 rows in table, copy data from above, copy data from another sheet and paste special (transpose values) to those new rows...re
Replies
1
Views
271
sijpie
sijpie
S
  • Question Question
VBA Help- Need to create VBA Function from worksheet
Replies
9
Views
692
scotty1978
S
L
  • Solved
Insert formulas to new lines in table
Replies
3
Views
152
Ltuk85
L

Forum statistics

Threads
1,223,893
Messages
6,175,242
Members
452,623
Latest member
russelllowellpercy

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top