Is there some kind of memory reference limit in excel?

[JPP]

I have the following code:

=IF('CCI Info'!DR105>=0.2&'CCI Info'!DR106>=0.2&'CC
Info'!DR107>=0.2&'CCI Info'!DR108>=0.2&'CCI Info'!DR109>=0.2&'CC
Info'!DR110>=0.2&'CCI Info'!DR111>=0.2&'CCI Info'!DR112>=0.2, 'CC
Info'!$DR$105&" "&'CCI Info'!$DR$106&" "&'CCI Info'!$DR$107&" "&'CC
Info'!$DR$108&" "&'CCI Info'!$DR$109&" "&'CCI Info'!$DR$110&" "&'CC
Info'!$DR$111&" "&'CCI Info'!$DR$112, “”)

And it works perfectly. But when I add line DR113 to the function, i
stops working. I have code similar to this in another part of th
worksheet, and it is MUCH longer. There is no reason this code shouldn'
work. I have used it before, and it worked until I added :

&'CCI Info'!DR113>=0.2
&" "&'CCI Info'!$DR$113

After that I get a Division/0 error. Nothing changed, and this worked i
my other cell. What's up? Does Excel have a limit on data or something
All I am trying to do is display multiple results in one cell!

 

[GS]

JPP has brought this to us :

I have the following code:

=IF('CCI Info'!DR105>=0.2&'CCI Info'!DR106>=0.2&'CCI
Info'!DR107>=0.2&'CCI Info'!DR108>=0.2&'CCI Info'!DR109>=0.2&'CCI
Info'!DR110>=0.2&'CCI Info'!DR111>=0.2&'CCI Info'!DR112>=0.2, 'CCI
Info'!$DR$105&" "&'CCI Info'!$DR$106&" "&'CCI Info'!$DR$107&" "&'CCI
Info'!$DR$108&" "&'CCI Info'!$DR$109&" "&'CCI Info'!$DR$110&" "&'CCI
Info'!$DR$111&" "&'CCI Info'!$DR$112, “â€)

And it works perfectly. But when I add line DR113 to the function, it
stops working. I have code similar to this in another part of the
worksheet, and it is MUCH longer. There is no reason this code shouldn't
work. I have used it before, and it worked until I added :

&'CCI Info'!DR113>=0.2
&" "&'CCI Info'!$DR$113

After that I get a Division/0 error. Nothing changed, and this worked in
my other cell. What's up? Does Excel have a limit on data or something?
All I am trying to do is display multiple results in one cell!!

I don't see how this formula syntax can work at all! (I'm using US Eng
version) I suggest you remove all the ampersands, wrap the 'test'
portion in 'AND()', AND wrap the 'value if true' portion in
CONCATENATE()...

=IF(AND('CCI Info'!DR105>=0.2,'CCI Info'!DR106>=0.2,'CCI
Info'!DR107>=0.2,'CCI Info'!DR108>=0.2,'CCI Info'!DR109>=0.2,'CCI
Info'!DR110>=0.2,'CCI Info'!DR111>=0.2,'CCI Info'!DR112>=0.2,'CCI
Info'!DR113>=0.2),CONCATENATE('CCI Info'!$DR$105," ",'CCI
Info'!$DR$106," ",'CCI Info'!$DR$107," ",'CCI Info'!$DR$108," ",'CCI
Info'!$DR$109," ",'CCI Info'!$DR$110," ",'CCI Info'!$DR$111," ",'CCI
Info'!$DR$112," ",'CCI Info'!DR113),“â€)

-OR-

=IF(COUNTIF('CCI Info'!DR105:'CCI Info'!DR113,">=0.2")=COUNTA('CCI
Info'!DR105:'CCI Info'!DR113),CONCATENATE('CCI Info'!$DR$105," ",'CCI
Info'!$DR$106," ",'CCI Info'!$DR$107," ",'CCI Info'!$DR$108," ",'CCI
Info'!$DR$109," ",'CCI Info'!$DR$110," ",'CCI Info'!$DR$111," ",'CCI
Info'!$DR$112," ",'CCI Info'!DR113),“â€)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

After repairing the 'paste' of your example I see that it does work as
intended. You can also enter it as an array formula (Ctrl+Shift+Enter)
it should work also! I had no problem getting the additions to work in
your original syntax structure.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

GS brought next idea :

After repairing the 'paste' of your example I see that it does work as
intended. You can also enter it as an array formula (Ctrl+Shift+Enter) it
should work also! I had no problem getting the additions to work in your
original syntax structure.

After changing the cell values so they fell below the test value, the
same results were returned (as though the test result was TRUE).
However, we know the values I tested were individually FALSE and so my
original suggested formulas behave correctly.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[joeu2004]

I have the following code: [....]
And it works perfectly.

No, it doesn't. You probably misled yourself. See below.

What's up?

Where to begin?

After that I get a Division/0 error.

The only way that IF expression would return a #DIV/0 error is if there is a
#DIV/0 error in one of the referenced cell.

Since you claim to see the error only after adding references to 'CCI
Info'!DR113, you should look there first.

=IF('CCI Info'!DR105>=0.2&'CCI Info'!DR106>=0.2&
'CCI Info'!DR107>=0.2&'CCI Info'!DR108>=0.2&
'CCI Info'!DR109>=0.2&'CCI Info'!DR110>=0.2&
'CCI Info'!DR111>=0.2&'CCI Info'!DR112>=0.2,

That is not likely to work as intended, although admittedly it depends on
what you intended <wink>.

The point is: that expression always returns TRUE(!).

So perhaps you thought it worked because you only tested situations where
you expected the TRUE result.

The crux of your mistake is the use of ampersand (&), which I presume you
intend to mean "AND". If that is the case, you should write the IF
expression as:

=IF(AND('CCI Info'!DR105>=0.2, 'CCI Info'!DR106>=0.2,
'CCI Info'!DR107>=0.2, 'CCI Info'!DR108>=0.2,
'CCI Info'!DR109>=0.2, 'CCI Info'!DR110>=0.2,
'CCI Info'!DR111>=0.2, 'CCI Info'!DR112>=0.2), ...)

where "..." represents the rest of the IF expression.

As for a "memory reference limit", the AND operation is limited to 30
parameters in Excel 2003 and earlier. The limit is 255 in Excel 2007 and
later.

As for why the expression as you wrote always returns TRUE, note the way
that it is parsed step-by-step.

1. =IF('CCI Info'!DR105 >= (0.2 & 'CCI Info'!DR106)

The comparison returns TRUE or FALSE. I will assume FALSE, since DR105 is
probably numeric. Text is always considered greater (not >=) than numbers.
But you will see that it does not matter. Continuing....

2. =IF(FALSE >= (0.2 & 'CCI Info'!DR107)

The comparison always returns TRUE because a logical value (TRUE or FALSE)
is always considered greater (not >=) than text.

3. =IF(TRUE >= (0.2 & 'CCI Info'!DR108)

Again, the comparison always returns TRUE. Skipping to the last term....

4. =IF(TRUE >= 0.2,

The comparison always returns TRUE because a logical value is always
considered greater (not >=) than numbers.

=IF(..., 'CCI Info'!$DR$105&" "&'CCI Info'!$DR$106&
" "&'CCI Info'!$DR$107&" "&'CCI Info'!$DR$108&" "&
'CCI Info'!$DR$109&" "&'CCI Info'!$DR$110&" "&
'CCI Info'!$DR$111&" "&'CCI Info'!$DR$112, "")

where "..." represents the beginning part of the IF expession.

There is nothing wrong with the concatenation as you wrote it using
ampersand (&). In particular, there is no reason to replace the use of
ampersand with the CONCATENATE function.

(On the contrary, given the 30-parameter limit in Excel 2003, there might be
good reason to prefer ampersand instead of the CONCATENATE function.)

However, if DR105R112 etc contain numbers, the concatenated text might not
appear exactly as you intended all the time. It will depend on the
precision of the values and how you want them to appear.

For example, if DR105 contains =1/3 and DR106 contains =2/3, they might
appear as 0.33 and 0.67 respectively if you format them as Number with 2
decimal places.

But DR105&" "&DR106 will appear as "0.333333333333333 0.666666666666667"
because of the greater precision of the actual values in those cells.

You might want to consider using TEXT('CCI Info'!$DR$105,"0.00"), for
example, in order to control the format in the concatenated text.

In any case, as for "a limit on data or something", the concatenated text is
limited 32,767 characters, of which only up to 1024 characters will be
displayed.