Matrix Excel formula

[JaiJana]

Hey, I am trying something with excel.. This is my desired result (of course entered manually for each cell)

I want to replicate this entirely with formulas .. In cell A1

=+IF(COLUMNS($A:A)>ROWS($1:1),COLUMNS($A:A)-1,IF(COLUMNS($A:A)=ROWS($1:1),0,ROWS($1:1)-1))

The formula I tried did not give me the result as you can see with the highlighted in orange cells has different numbers

Is there a single formula and preferrably elegant formula that gives me what I need.?

Thank you one and all.. This is my first post..

 

[Aladin Akyurek]

Welcome to mrexcel. Let's try to post these samples directly here using one of the methods listed in the following: Attachments or using this add-in:https://app.box.com/s/soezox25h3w0q5s4rcyl, not as images.

 

[XOR LX]

Hi.

=ABS(COLUMNS($A:A)-ROWS($1:1))

Regards

 

[JaiJana]

Excel 2012

	A	B	C	D	E	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #FFC000, align: right"]2[/TD]
[TD="bgcolor: #FFC000, align: right"]3[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]2[/TD]
[TD="bgcolor: #FFC000, align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #FFC000, align: right"]3[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]3[/TD]
[TD="bgcolor: #FFC000, align: right"]3[/TD]
[TD="bgcolor: #FFC000, align: right"]3[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="bgcolor: #FFC000, align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]5[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]
[TD="bgcolor: #FFC000, align: right"]5[/TD]
[TD="align: right"]0[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A1[/TH]
[TD="align: left"]=+IF(COLUMNS($A:A)>ROWS($1:1),COLUMNS($A:A)-1,IF(COLUMNS($A:A)=ROWS($1:1),0,ROWS($1:1)-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Workbook Defined Names[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Name[/TH]
[TH="align: left"]Refers To[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]A[/TH]
[TD="align: left"]=Sheet3!$A:$A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[JaiJana]

Hi.

=ABS(COLUMNS($A:A)-ROWS($1:1))

Regards

This worked splendidly... Thank you...@XOR LX ..

 

[XOR LX]

This worked splendidly... Thank you...@XOR LX ..

You're welcome!

Cheers