Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Is there some kind of memory reference limit in excel?
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!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there some kind of memory reference limit in excel?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there some kind of memory reference limit in excel?
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there some kind of memory reference limit in excel?
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Is there some kind of memory reference limit in excel?
"JPP" wrote:
I have the following code: [....] And it works perfectly. No, it doesn't. You probably misled yourself. See below. JPP wrote: What's up? Where to begin? JPP wrote: 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. JPP wrote: =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. JPP wrote: =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 DR105:DR112 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Different kind of cell reference. | Excel Programming | |||
Reference Name - Limit? | Excel Discussion (Misc queries) | |||
Limit (not memory) consumed by Range Names, UDF calls, VB code, st | Excel Programming | |||
What kind of memory does not enough? | Excel Discussion (Misc queries) | |||
does Excel have memory limit of data Array? | Excel Programming |