software

I'm trying to get a table to invert the top triangle of numbers into
the bottom whilst changing the values (1 to 0 and 0 to 1). A basic
simple table of what I am doing is given below.

1234567
AX100101
B X01001
C X0100
D X010
E X00
F X1

The above is what is entered in the table, then I need it to invert it
automatically (so that if I change one of the above values it will
change the other at the same time). What I want is to end up with a
table that looks like this:

1234567
AX100101
B0X01001
C11X0100
D101X010
E0101X00
F11101X1
G001110X

I hope these show as intended (due to font used). If someone could
please help me with this problem I would be incredibly grateful. After
searching though help files I think that the OFFSET function could be
used for this but am not entirely sure how. If any other method is
simple please let me know. Thank you for your time.

Rabbit ()_()Sorry, I'd like to make an ammendment to the above tables, they are
supposed to read ABCD... across the top and 1234... downwards a la
Excel layout. Don't mean to cause confusion, was just not thinking.Sorry, I'd like to make an ammendment to the above tables, they are
supposed to read ABCD... across the top and 1234... downwards a la
Excel layout. Don't mean to cause confusion, was just not thinking.Here's one play ..

A sample construct is available at:
www.savefile.com/files/1547175
Invert Top Triangle n Change Values_rabbit_wks.xls

Assuming the source table is in the quot;upper partquot; of A1:G7
and is fully populated with either values: 1, 0
(blank cells within the source, if any, will be equivalent to zeros)

In A2, copied down to A7:
=IF(OFFSET($A$1,,ROW(A1),)=0,1,0)

In B3, copied down to B7:
=IF(OFFSET($B$2,,ROW(A1),)=0,1,0)

In C4, copied down to C7:
=IF(OFFSET($C$3,,ROW(A1),)=0,1,0)

In D5, copied down to D7:
=IF(OFFSET($D$4,,ROW(A1),)=0,1,0)

In E6, copied down to E7:
=IF(OFFSET($E$5,,ROW(A1),)=0,1,0)

In F7:
=IF(OFFSET($F$6,,ROW(A1),)=0,1,0)

------
Note tha A1:G7 is conditionally formatted
using quot;Formula isquot;:
Cond1: =ROW(A1)lt;COLUMN(A1)
Cond2: =ROW(A1)=COLUMN(A1)
Cond3: =ROW(A1)gt;COLUMN(A1)
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Rabbitquot; gt; wrote in message oups.com...
gt; Sorry, I'd like to make an ammendment to the above tables, they are
gt; supposed to read ABCD... across the top and 1234... downwards a la
gt; Excel layout. Don't mean to cause confusion, was just not thinking.
.....
gt; I'm trying to get a table to invert the top triangle of numbers into
gt; the bottom whilst changing the values (1 to 0 and 0 to 1). A basic
gt; simple table of what I am doing is given below.
gt;
gt; 1234567
gt; AX100101
gt; B X01001
gt; C X0100
gt; D X010
gt; E X00
gt; F X1
gt;
gt; The above is what is entered in the table, then I need it to invert it
gt; automatically (so that if I change one of the above values it will
gt; change the other at the same time). What I want is to end up with a
gt; table that looks like this:
gt;
gt; 1234567
gt; AX100101
gt; B0X01001
gt; C11X0100
gt; D101X010
gt; E0101X00
gt; F11101X1
gt; G001110X
gt;
gt; I hope these show as intended (due to font used). If someone could
gt; please help me with this problem I would be incredibly grateful. After
gt; searching though help files I think that the OFFSET function could be
gt; used for this but am not entirely sure how. If any other method is
gt; simple please let me know. Thank you for your time.
gt;
gt; Rabbit ()_()
Thanks for your reply, it was extremely useful. Having modified it to
fit my problem it now works. Although to be honest I don't know why you
conditionally formatted it (it isn't something I usually do). But
thanks again, really appreciative.

Rabbit.Max wrote...
gt;Here's one play ..
....
gt;Assuming the source table is in the quot;upper partquot; of A1:G7
gt;and is fully populated with either values: 1, 0
gt;(blank cells within the source, if any, will be equivalent to zeros)
gt;
gt;In A2, copied down to A7:
gt;=IF(OFFSET($A$1,,ROW(A1),)=0,1,0)
gt;
gt;In B3, copied down to B7:
gt;=IF(OFFSET($B$2,,ROW(A1),)=0,1,0)
....

Could avoid volatile functions and enter this all at one time. Select
A1:G7, press [F5], click on Special..., select blanks, click OK, type
the formula

=1-INDEX($A$1:$G$7,COLUMNS($A$1:A$1),ROWS($A$1:$A2))

and press [Ctrl] [Enter].quot;Harlan Grovequot; wrote:
gt; .. Could avoid volatile functions and enter this all at one time.
gt; Select A1:G7, press [F5], click on Special,
gt; select blanks, click OK, type the formula:
gt; =1-INDEX($A$1:$G$7,COLUMNS($A$1:A$1),ROWS($A$1:$A2))
gt; and press [Ctrl] [Enter].

Nirvanic! Thanks for sharing the masterful alternative, Harlan.
Truly the finesse of an Excel grand master ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---
quot;Rabbitquot; wrote
gt; Thanks for your reply, it was extremely useful.
gt; Having modified it to fit my problem it now works.

You're welcome !
Do try as well Harlan's marvellous alternative

gt; .. Although to be honest I don't know why you
gt; conditionally formatted it (it isn't something I usually do).

It's usually something I'd do for diagonal matrix presentation lt;ggt;,
but of course it's just an optional window dressing thrown in here ..
--
Max
Singapore
savefile.com/projects/236895
xdemechanik
---