Offset Function Trouble, Please Help
 

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 ()_()

Offset Function Trouble, Please Help
 

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.

Offset Function Trouble, Please Help
 

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.

Offset Function Trouble, Please Help
 

Here's one play ..

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

Assuming the source table is in the "upper part" 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 "Formula is":
Cond1: =ROW(A1)<COLUMN(A1)
Cond2: =ROW(A1)=COLUMN(A1)
Cond3: =ROW(A1)COLUMN(A1)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Rabbit" wrote in message
oups.com...
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.
.....
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 ()_()

Offset Function Trouble, Please Help
 

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.

Offset Function Trouble, Please Help
 

Max wrote...
Here's one play ..
....
Assuming the source table is in the "upper part" 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)
....

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].

Offset Function Trouble, Please Help
 

"Harlan Grove" wrote:
.. 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].

Nirvanic! Thanks for sharing the masterful alternative, Harlan.
Truly the finesse of an Excel grand master ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Offset Function Trouble, Please Help
 

"Rabbit" wrote
Thanks for your reply, it was extremely useful.
Having modified it to fit my problem it now works.

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

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

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