Offset Function Trouble, Please Help

R

Rabbit

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

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

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

Max

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)
 
R

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

Harlan Grove

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

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

Max

Harlan Grove said:
.. 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].
Click to expand...

Nirvanic! Thanks for sharing the masterful alternative, Harlan.
Truly the finesse of an Excel grand master ..
 
M

Max

Thanks for your reply, it was extremely useful.
Having modified it to fit my problem it now works.
Click to expand...

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

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