Arrays and names

  • Thread starter Thread starter The Cube
  • Start date Start date
T

The Cube

Hi all.
Using XL2002 but needs to be XL97 compatible.
With ActiveCell Transactions!F6
I have defined a local named range Transactions!Test
refers to
=(Assets!Category_Range=Transactions!$E6)
Assets!Category_Range is a name that refers to an absolutely defined single
column of data
The relative reference to row 6 in the above is intentional but probably not
relevant for what follows.

Evaluating the array Transactions!Test produces something like this:

False;
False;
True;
False;
True;
True;
False;
<etc>

This is correct and predicted behaviour.

I want to create another local named range Transactions!Result derived from
Transactions!Test
that produces an array of the same dimensions as Transactions!Test and which
when evaluated would produce something like this (using the same data as
above):

0;
0;
1;
1;
2;
3;
3;

The rules governing Transactions!Result are:
(1) First element of Transactions!Result should be:
(a) 1 if the corresponding (ie first) element of Transactions!Test
is True,
(b) else 0
(2) Each element of Transactions!Result after the first should be equal to
the sum of the value of the previous element in Transactions!Result and
(a) 1 if the corresponding element in Transactions!Test is True
(b) else 0

The solution needs to make no use of cells in worksheets.

Can someone please help me with the definition of named range
Transactions!Result?

Thanks

-Cube
 
The Cube said:
I have defined a local named range Transactions!Test
refers to
=(Assets!Category_Range=Transactions!$E6) ....
Evaluating the array Transactions!Test produces something like this:

False;
False;
True;
False;
True;
True;
False;
<etc>

This is correct and predicted behaviour.

I want to create another local named range Transactions!Result derived from
Transactions!Test that produces an array of the same dimensions as
Transactions!Test and which when evaluated would produce something like this
(using the same data as above):

0;
0;
1;
1;
2;
3;
3;
Click to expand...
....

So convert the boolean values to the standard integer values (TRUE = 1,
FALSE = 0), then derive an array of sequential sums? If so, define another
name, Transactions!TestSeq, referring to

=ROW(INDIRECT("1:"&ROWS(Transactions!Test)))

Then define Transactions!Result as

=MMULT(--(Transactions!TestSeq>=TRANSPOSE(Transactions!TestSeq)),
--Transactions!Test)
 
Harlan Grove said:
...

So convert the boolean values to the standard integer values (TRUE = 1,
FALSE = 0), then derive an array of sequential sums? If so, define another
name, Transactions!TestSeq, referring to

=ROW(INDIRECT("1:"&ROWS(Transactions!Test)))
Click to expand...

Excel is having a problem evaluationg the above name.
In the test case Transactions!Test has just 4 elements, so I tried
Transactions!RowSeq, referring to =ROWS(Transactions!Test)
and it accurately and predictably evaluates this to 4. So far so good.

I then tried
Transactions!RowTemp, referring to =ROWS(INDIRECT("1:4")
and it would not evaluate that.

It WOULD evaluate:
Transactions!RowTry2, referring to = ROWS(Transactions!$1:$4)
and this comes out as
1;
2;
3;
4;

Assuming that this is what we are trying to achieve, the next question is
how to substitute a variable for the number 4 appearing in that name, being
the result evaluated by Transactions!RowSeq, referring to
=ROWS(Transactions!Test)

I tried
=ROWS(INDIRECT("Transactions!$1:$"&Transactions!RowSeq))
but it goes back to "unable to evaluate ..." again
Then define Transactions!Result as

=MMULT(--(Transactions!TestSeq>=TRANSPOSE(Transactions!TestSeq)),
--Transactions!Test)
Click to expand...

-Cube
 
The Cube said:
Excel is having a problem evaluationg the above name.
Click to expand...

Then is Transactions!Test a worksheet-level name or is it a workbook-level
name? Do you have Transition Formula Evaluation enabled?

....
I then tried
Transactions!RowTemp, referring to =ROWS(INDIRECT("1:4")
and it would not evaluate that.
Click to expand...

The formula you show would throw a syntax error due to unmatched
parentheses. If your formula actually were =ROWS(INDIRECT("1:4")) then it
should (and does on my system) return the scalar (single value) 4.
It WOULD evaluate:
Transactions!RowTry2, referring to = ROWS(Transactions!$1:$4)
and this comes out as
1;
2;
3;
4;
Click to expand...

Not a chance. ROWS only returns a single number. Do you really mean ROW
(withoit the S)?
Assuming that this is what we are trying to achieve, the next question is
how to substitute a variable for the number 4 appearing in that name, being
the result evaluated by Transactions!RowSeq, referring to
=ROWS(Transactions!Test)

I tried
=ROWS(INDIRECT("Transactions!$1:$"&Transactions!RowSeq))
but it goes back to "unable to evaluate ..." again
Click to expand...
....

Then something's wrong with Excel on your machine. What version of Excel are
you using?

For Transactions!TestSeq try this:

=ROW(INDIRECT("1:"&TEXT(ROWS(Transactions!Test),"0")))
 
Harlan Grove said:
Then is Transactions!Test a worksheet-level name or is it a workbook-level
name? Do you have Transition Formula Evaluation enabled?
Click to expand...
Thanks for taking the time to respond. All names are definitely
worksheet-level names.

I did not have Transition Formula Evaluation enabled. Should I have? I
have tried with it enabled and it does not appear to make a difference.
...

The formula you show would throw a syntax error due to unmatched
parentheses. If your formula actually were =ROWS(INDIRECT("1:4")) then it
should (and does on my system) return the scalar (single value) 4.
Click to expand...
Correct about the typo, sorry about that. I also get a scalar value 4
Not a chance. ROWS only returns a single number. Do you really mean ROW
(withoit the S)?
Click to expand...

It certainly doesn't help you to help me when my post is riddled with typos.
I confirm that it should be ROW(..) not ROWS(..)

...

Then something's wrong with Excel on your machine. What version of Excel are
you using?
Click to expand...

XL2002 SP2 on each of two machines that display the same problem. Neither
reports any outstanding upgrades when I visit Microsoft's Office Update
site.
For Transactions!TestSeq try this:

=ROW(INDIRECT("1:"&TEXT(ROWS(Transactions!Test),"0")))
Click to expand...

Nope, that bombs out as well.
I am using Jan Karel Pieterse's "Name Manager" to check the evaluation of
the names, and it is this add-in that reports "unable to evaluate ...". Are
you familiar with it? It seems otherwise stable.

Sorry about the typos

-Cube
 
CORRECTION

Harlan Grove said:
"The Cube" <[email protected]> wrote...

The formula you show would throw a syntax error due to unmatched
parentheses. If your formula actually were =ROWS(INDIRECT("1:4")) then it
should (and does on my system) return the scalar (single value) 4.
Click to expand...

I started a new workbook that contains nothing other than a name rowSRange
that refers to
=ROWS(INDIRECT("1:4"))
and another name rowRange
that refers to
=ROW(INDIRECT("1:4"))

Neither name would evaluate. I tried them both at local worksheet level and
at workbook level, with Transation Formula Evaluation enabled and disabled,
and it made no difference.

-Cube
 
...
...
I started a new workbook that contains nothing other than a name rowSRange
that refers to
=ROWS(INDIRECT("1:4"))
and another name rowRange
that refers to
=ROW(INDIRECT("1:4"))

Neither name would evaluate. I tried them both at local worksheet level and
at workbook level, with Transation Formula Evaluation enabled and disabled,
and it made no difference.
Click to expand...

Transition formula evaluation should be DISABLED. I had asked because when this
setting is disabled, Excel automatically converts text and numbers back & forth
as appropriate in context, so "A"&1 would evaluate to "A1" and --("1"&"."&"5")
would evaluate to 1.5. With this setting enabled, mixing text and numbers
results in #VALUE! errors.

If =ROWS(INDIRECT("1:4")) doesn't evaluate on your system, then your system is
screwed up. If the 'Name Manager' add-in you're using can't evaluate these
expressions, then it's got a bug. INDIRECT("1:4") is a valid range reference, so
ROW(INDIRECT("1:4")) and ROWS(INDIRECT("1:4")) are both valid expressions that
*DO* evaluate to {1;2;3;4} and 4, respectively, in Excel environments not
running this add-in.

This idiom appears very often in dynamic named range construction. See the
Google Groups Search result for

http://www.google.com/groups?as_epq=ROW(INDIRECT(&as_ugroup=*excel*

Try unloading this add-in and using these defined names, or use Excel's own
Insert > Name > Define... mechanism to define them.
 
BINGO!

I defined Transactions!TestSeq referring to:
=ROW(OFFSET(Transactions!$A$1,0,0,ROWS(Transactions!Test),1))

That seemed to work, as did the final solution, although why I could not get
your original solution to work remains a mystery to me.

Why the double negatives in the final solution, by the way?

MMULT(--(Transactions!TestSeq>=TRANSPOSE(Transactions!TestSeq)),
--Transactions!Test)

It certainly doesn't work without the double negatives.

I am a bit concerned about is whether the use of TRANSPOSE() limits the
number of rows that I can have in TestSeq to the number of columns that
there are in a worksheet (ie 256). Could you clarify that for me?

Thanks

-Cube
 
Harlan Grove said:
...
..

Transition formula evaluation should be DISABLED. I had asked because when this
setting is disabled, Excel automatically converts text and numbers back & forth
as appropriate in context, so "A"&1 would evaluate to "A1" and --("1"&"."&"5")
would evaluate to 1.5. With this setting enabled, mixing text and numbers
results in #VALUE! errors.

If =ROWS(INDIRECT("1:4")) doesn't evaluate on your system, then your system is
screwed up. If the 'Name Manager' add-in you're using can't evaluate these
expressions, then it's got a bug. INDIRECT("1:4") is a valid range reference, so
ROW(INDIRECT("1:4")) and ROWS(INDIRECT("1:4")) are both valid expressions that
*DO* evaluate to {1;2;3;4} and 4, respectively, in Excel environments not
running this add-in.

This idiom appears very often in dynamic named range construction. See the
Google Groups Search result for

http://www.google.com/groups?as_epq=ROW(INDIRECT(&as_ugroup=*excel*

Try unloading this add-in and using these defined names, or use Excel's own
Insert > Name > Define... mechanism to define them.
Click to expand...

Harlan, thanks again. I am going to go back over your posts and other help
and get to the bottom of this. In the meantime I have worked out a solution
that works on my machine and on the machine at my office that displays (to
me) the same problem, and that is to define Transactions!TestSeq to refer
to:

=ROW(OFFSET(Transactions!$A$1,0,0,ROWS(Transactions!Test),1))

That seemed to work, as did the final solution, although why I could not get
your original solution to work remains a mystery to me.

Why the double negatives in the final solution, by the way?

MMULT(--(Transactions!TestSeq>=TRANSPOSE(Transactions!TestSeq)),
--Transactions!Test)

It certainly doesn't work without the double negatives.

I am a bit concerned about is whether the use of TRANSPOSE() limits the
number of rows that I can have in TestSeq to the number of columns that
there are in a worksheet (ie 256). Could you clarify that for me?

Thanks

-Cube
 
Sorry about the double-posting. My pc getting confused between outlook and
outlook express. I send it by outlook. PC bombs out with error message. I
re-compose in Outlook Express and send from that program, and Outlook
Express picks up the bombed out message from Outlook and sends that as well.
Sheesh!
 
...
...
=ROW(OFFSET(Transactions!$A$1,0,0,ROWS(Transactions!Test),1))
Click to expand...
...

It's equivalent, but not quite as robust. If you effectively delete cell
Transactions!A1, this'll evaluate to a #REF! error.
Why the double negatives in the final solution, by the way?

MMULT(--(Transactions!TestSeq>=TRANSPOSE(Transactions!TestSeq)),
--Transactions!Test)
Click to expand...

They convert boolean (TRUE/FALSE) values to numeric (1/0, respectively). MMULT
only works with numeric values.
I am a bit concerned about is whether the use of TRANSPOSE() limits the
number of rows that I can have in TestSeq to the number of columns that
there are in a worksheet (ie 256). Could you clarify that for me?
Click to expand...

Experiment. Enter both the following array formulas.

=SUM(ROW(1:1000)^2)

and

=MMULT(TRANSPOSE(ROW(1:1000)),ROW(1:1000))

They should give the same result (333,833,500). You will run into limitations,
but not until you reach a bit more than 5,000 rows.
 
<snip ... lots of helpful stuff>

I think I have sorted out the problem re. formula not "evaluating". The
problem was indeed with the add-in. Thanks.

-Cube
 
Back
Top