| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Large formula problem
Hi all I have a monster formula I could not place on one line of code so I had to split it on to two lines but now when I run the code it comes up with out of memory error! Any ideas on how to tackle this problem? Many thanks! :) -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
What's the line of code - could you post it here? And did you wrap it
properly with & _ ? Jim28 schreef: Hi all I have a monster formula I could not place on one line of code so I had to split it on to two lines but now when I run the code it comes up with out of memory error! Any ideas on how to tackle this problem? Many thanks! :) -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
ActiveCell.FormulaR1C1 = "=IF(AND(IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0),1,RC5=""Unavailable""),""-1"",IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0))+IF(AND(IF(AND(RC6<=R1C[1],RC7R1C[1]),1,0),1,RC8=""Unavailable""),""-1"",IF(AND(RC6<=R1C[1],RC7R1C[1]),1,0))+IF(AND(IF(AND(RC9<=R1C[1],RC10R1C[1]),1,0),1,RC11=""Unavailable""),""-1"",IF(AND(RC9<=R1C[1],RC10R1C[1]),1,0))+IF(AND(IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0),1,RC14=""Unavailable""),""-1"",IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0))+IF(AND(IF(AND(RC15<=R1C[1],RC16R1C[1]),1,0),1,RC17=""Unavailable""),""-1"",IF(AND(RC15<=R1C[1],RC16R1C[1]),1,0))+IF(AND(IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0),1,RC20=""Unavailable""),""-1"",IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0))+IF(AND(IF(AND(RC21<=R1C[1],RC22R1C[1]),1,0),1,RC23=""Unavailable""),""-1"",IF(AND(RC21<=R1C[1],RC22R1C[1]),1,0))+IF(AND(IF(AND(RC24<=R1C[1],RC25R1C[1]),1,0),1,RC26=""Unavailable""),""-1"",IF(AND(RC24<=R1C[1],RC25R1C[1]),1,0))" & _ "+IF(AND(IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0),1,RC29=""Unavailable""),""-1"",IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0))+IF(AND(IIF(AND(RC30<=R1C[1],RC31R1C[1]),1,0),1,RC32=""Unavailable""),""-1"",IF(AND(RC30<=R1C[1],RC31R1C[1]),1,0))+IF(AND(IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0),1,RC35=""Unavailable""),""-1"",IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0))+IF(AND(IF(AND(RC36<=R1C[1],RC37R1C[1]),1,0),1,RC38=""Unavailable""),""-1"",IF(AND(RC36<=R1C[1],RC37R1C[1]),1,0))+IF(AND(IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0),1,RC41=""Unavailable""),""-1"",IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0))+IF(AND(IF(AND(RC42<=R1C[1],RC43R1C[1]),1,0),1,RC44=""Unavailable""),""-1"",IF(AND(RC42<=R1C[1],RC43R1C[1]),1,0))" How does that look? :) -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
The formula is simply too large. Excel accepts a maximum of 1024 characters
for a formula. I counted 1600. The formula looks like it can simply be divided in half and put into two cells and summed. Say, put the first half into A1 and in B1 put "=A1 +" followed by the second half, i.e. =A1 + IF(AND(RC12<=... For something this large, I would consider going with a UDF using a loop instead. It may be a tad slower (you won't likely notice) but would be much easier to maintain. Also, your code repeats the same structure. For example: IF(AND(xxx,1,RC5=""Unavailable""),""-1"",IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0)) where xxx stands for: IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0) What is the purpose of the *1* argument inside the AND function following the xxx. It appears to be completely irrelevant. Also, the structure will either return "-1" as text if the first condition is met; else, will return either 1 or 0 depending on the whether the nested IF function returns TRUE or FALSE. So this seems like it may be an error since you will return either text ("-1") or numeric (1, 0). Are you sure this is what you want? Just my $0.02 worth. Regards, Greg AND(xxx,1,RC5=""Unavailable"") "Jim28" wrote: ActiveCell.FormulaR1C1 = "=IF(AND(IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0),1,RC5=""Unavailable""),""-1"",IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0))+IF(AND(IF(AND(RC6<=R1C[1],RC7R1C[1]),1,0),1,RC8=""Unavailable""),""-1"",IF(AND(RC6<=R1C[1],RC7R1C[1]),1,0))+IF(AND(IF(AND(RC9<=R1C[1],RC10R1C[1]),1,0),1,RC11=""Unavailable""),""-1"",IF(AND(RC9<=R1C[1],RC10R1C[1]),1,0))+IF(AND(IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0),1,RC14=""Unavailable""),""-1"",IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0))+IF(AND(IF(AND(RC15<=R1C[1],RC16R1C[1]),1,0),1,RC17=""Unavailable""),""-1"",IF(AND(RC15<=R1C[1],RC16R1C[1]),1,0))+IF(AND(IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0),1,RC20=""Unavailable""),""-1"",IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0))+IF(AND(IF(AND(RC21<=R1C[1],RC22R1C[1]),1,0),1,RC23=""Unavailable""),""-1"",IF(AND(RC21<=R1C[1],RC22R1C[1]),1,0))+IF(AND(IF(AND(RC24<=R1C[1],RC25R1C[1]),1,0),1,RC26=""Unavailable""),""-1"",IF(AND(RC24<=R1C[1],RC25R1C[1]),1,0))" & _ "+IF(AND(IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0),1,RC29=""Unavailable""),""-1"",IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0))+IF(AND(IIF(AND(RC30<=R1C[1],RC31R1C[1]),1,0),1,RC32=""Unavailable""),""-1"",IF(AND(RC30<=R1C[1],RC31R1C[1]),1,0))+IF(AND(IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0),1,RC35=""Unavailable""),""-1"",IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0))+IF(AND(IF(AND(RC36<=R1C[1],RC37R1C[1]),1,0),1,RC38=""Unavailable""),""-1"",IF(AND(RC36<=R1C[1],RC37R1C[1]),1,0))+IF(AND(IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0),1,RC41=""Unavailable""),""-1"",IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0))+IF(AND(IF(AND(RC42<=R1C[1],RC43R1C[1]),1,0),1,RC44=""Unavailable""),""-1"",IF(AND(RC42<=R1C[1],RC43R1C[1]),1,0))" How does that look? :) -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
Thanks for that Greg! I wish to return a -1 numeric entry and not text! Thanks for pointing that one out for me! What is a UDF? -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
A UDF stands for "User Defined Function" which is very similar to a macro but
returns a value instead of executing a set of instructions. It typically receives arguments like most worksheet functions. It crunches these (if they exist) and comes up with a result depending on how it is written. You can then reference the UDF in a cell the same as any function. A very simple example follows: Function NegIfTextFound(c As Range, negtext As String) As Integer NegIfTextFound = IIf(c = negtext, -1, 1) End Function If you paste the above to a standard code module you can then reference it in a cell the same as any function. It requires a cell reference argument (e.g. D9) and a text argument (e.g. "Unavailable"); and based on analysis of these arguments returns a result to the cell -- in this case either -1 or 1. For example, if you want to return to cell B10 either -1 or 1 depending on whether the cell two columns to the right and one row up contains "Unavailable" or not, you would enter into B10: =NegIfTextFound(D9, "Unavailable") The same relative behaviour exists if you copy/paste or AutoFill. Instead of a UDF, it seems that what you are doing could be greatly simplified by using either a "canned" array function such as Sumproduct or a standard array function. I suggest that you post to the WorksheetFunction ng instead and describe what you want to do. I expect you will get a much simpler (shorter) formula. I could likely sort it out myself from your formula but it's a bit much of a headache for a Saturday night. As you undoubtedly noticed, I use A1 reference style and am not comfortable with R1C1. Best regards, Greg "Jim28" wrote: Thanks for that Greg! I wish to return a -1 numeric entry and not text! Thanks for pointing that one out for me! What is a UDF? -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
I have to say, that formula is ridiculous. It has errors in it as far as I
can see (such as IF(AND(IF(AND(cond1,cond2),1,0),1,cond3), what does the second 1 mean/do?, there is an IIF in there which is VBA not Excel, etc.), but worst of all it is totally unmaintainable. Your only hope is to re-design the spreadsheet to get rid of this monstrosity. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jim28" wrote in message ... ActiveCell.FormulaR1C1 = "=IF(AND(IF(AND(RC3<=R1C[1],RC4R1C[1]),1,0),1,RC5=""Unavailable""),""-1"",I F(AND(RC3<=R1C[1],RC4R1C[1]),1,0))+IF(AND(IF(AND(RC6<=R1C[1],RC7R1C[1]),1, 0),1,RC8=""Unavailable""),""-1"",IF(AND(RC6<=R1C[1],RC7R1C[1]),1,0))+IF(AND (IF(AND(RC9<=R1C[1],RC10R1C[1]),1,0),1,RC11=""Unavailable""),""-1"",IF(AND( RC9<=R1C[1],RC10R1C[1]),1,0))+IF(AND(IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0), 1,RC14=""Unavailable""),""-1"",IF(AND(RC12<=R1C[1],RC13R1C[1]),1,0))+IF(AND (IF(AND(RC15<=R1C[1],RC16R1C[1]),1,0),1,RC17=""Unavailable""),""-1"",IF(AND (RC15<=R1C[1],RC16R1C[1]),1,0))+IF(AND(IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0 ),1,RC20=""Unavailable""),""-1"",IF(AND(RC18<=R1C[1],RC19R1C[1]),1,0))+IF(A ND(IF(AND(RC21<=R1C[1],RC22R1C[1]),1,0),1,RC23=""Unavailable""),""-1"",IF(A ND(RC21<=R1C[1],RC22R1C[1]),1,0))+IF(AND(IF(AND(RC24<=R1C[1],RC25R1C[1]),1 ,0),1,RC26=""Unavailable""),""-1"",IF(AND(RC24<=R1C[1],RC25R1C[1]),1,0))" & _ "+IF(AND(IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0),1,RC29=""Unavailable""),""-1" ",IF(AND(RC27<=R1C[1],RC28R1C[1]),1,0))+IF(AND(IIF(AND(RC30<=R1C[1],RC31R1 C[1]),1,0),1,RC32=""Unavailable""),""-1"",IF(AND(RC30<=R1C[1],RC31R1C[1]),1 ,0))+IF(AND(IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0),1,RC35=""Unavailable""),"" -1"",IF(AND(RC33<=R1C[1],RC34R1C[1]),1,0))+IF(AND(IF(AND(RC36<=R1C[1],RC37 R1C[1]),1,0),1,RC38=""Unavailable""),""-1"",IF(AND(RC36<=R1C[1],RC37R1C[1]) ,1,0))+IF(AND(IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0),1,RC41=""Unavailable""), ""-1"",IF(AND(RC39<=R1C[1],RC40R1C[1]),1,0))+IF(AND(IF(AND(RC42<=R1C[1],RC4 3R1C[1]),1,0),1,RC44=""Unavailable""),""-1"",IF(AND(RC42<=R1C[1],RC43R1C[1 ]),1,0))" How does that look? :) -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
I have to say, that formula is ridiculous. It has errors in it as far a I can see (such as IF(AND(IF(AND(cond1,cond2),1,0),1,cond3), what doe the second 1 mean/do?, there is an IIF in there which is VBA not Excel etc.), but worst of all it is totally unmaintainable. Your only hope is to re-design the spreadsheet to get rid of this monstrosity. You made me laugh with your comments! Very true I am sure but I had t giggle! I love the bit about ridiculous and monstrosity! Hilarious! Ok with the help of the legend also known as Matt Vidas I have th following: Code ------------------- ActiveCell.FormulaR1C1 = _ "=IF(AND(RC3<=R1C[1],RC4R1C[1]),IF(RC5=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC6<=R1C[1],RC7R1C[1]),IF(RC8=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC9<=R1C[1],RC10R1C[1]),IF(RC11=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC12<=R1C[1],RC13R1C[1]),IF(RC14=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC15<=R1C[1],RC16R1C[1]),IF(RC17=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC18<=R1C[1],RC19R1C[1]),IF(RC20=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC21<=R1C[1],RC22R1C[1]),IF(RC23=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC24<=R1C[1],RC25R1C[1]),IF(RC26=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC27<=R1C[1],RC28R1C[1]),IF(RC29=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC30<=R1C[1],RC31R1C[1]),IF(RC32=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC33<=R1C[1],RC34R1C[1]),IF(RC35=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC36<=R1C[1],RC37R1C[1]),IF(RC38=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC39<=R1C[1],RC40R1C[1]),IF(RC41=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC42<=R1C[1],RC43R1C[1]),IF(RC44=""Unavailable"",""-1"",1),0)" ------------------- -- Jim2 ----------------------------------------------------------------------- Jim28's Profile: http://www.excelforum.com/member.php...fo&userid=3575 View this thread: http://www.excelforum.com/showthread.php?threadid=55529 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Large formula problem
I suggest correcting the "-1" (text) to -1 (integer). I have it working but
Excel is making the conversion based on a forced interpretation ("-1" to -1) because of the summation. This is inefficient and adds to the complexity. You can easily make the change using Edit Replace. However, I still don't like it because it's way too large for what it's doing. I suggest either the appended UDF or an array formula. Without having your wb I can't test the UDF and so there's a good chance its incorrect. It was written in a hurry and is an interpretation of your formula. If it works and you want to use it, test it rigorously under all possible scenarios first. Note that, for your formula and the appended UDF, the result can range between -14 and 14 and changes by -2 for each "Unavailable". This seems strange. Are you sure this is what you want? Assuming you are unfamiliar with VBA and code modules: To use the UDF, open the VBE (Alt + F11) and add a standard code module (Insert Module). Then paste it to the code module. UDF follows: Function JimsFunct(c As Range, negtext As String) As Integer Dim i As Integer, v2 As Integer Dim v1 As Double Dim rw As Long, col As Long Application.Volatile True rw = c.Row: col = c.Column: v1 = c.Value: v2 = 0 For i = col + 2 To col + 41 Step 3 If Cells(rw + 3, i - 2) <= v1 And Cells(rw + 3, i - 1) v1 Then v2 = v2 + IIf(Cells(rw + 3, i) = negtext, -1, 1) End If Next JimsFunct = v2 End Function Then in the appropriate cell, enter the formula with apprpriate cell reference, e.g.: =JimsFunct(C1, "Unavailable") Hope all goes well. Regards, Greg "Jim28" wrote: I have to say, that formula is ridiculous. It has errors in it as far as I can see (such as IF(AND(IF(AND(cond1,cond2),1,0),1,cond3), what does the second 1 mean/do?, there is an IIF in there which is VBA not Excel, etc.), but worst of all it is totally unmaintainable. Your only hope is to re-design the spreadsheet to get rid of this monstrosity. You made me laugh with your comments! Very true I am sure but I had to giggle! I love the bit about ridiculous and monstrosity! Hilarious! Ok with the help of the legend also known as Matt Vidas I have the following: Code: -------------------- ActiveCell.FormulaR1C1 = _ "=IF(AND(RC3<=R1C[1],RC4R1C[1]),IF(RC5=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC6<=R1C[1],RC7R1C[1]),IF(RC8=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC9<=R1C[1],RC10R1C[1]),IF(RC11=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC12<=R1C[1],RC13R1C[1]),IF(RC14=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC15<=R1C[1],RC16R1C[1]),IF(RC17=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC18<=R1C[1],RC19R1C[1]),IF(RC20=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC21<=R1C[1],RC22R1C[1]),IF(RC23=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC24<=R1C[1],RC25R1C[1]),IF(RC26=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC27<=R1C[1],RC28R1C[1]),IF(RC29=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC30<=R1C[1],RC31R1C[1]),IF(RC32=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC33<=R1C[1],RC34R1C[1]),IF(RC35=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC36<=R1C[1],RC37R1C[1]),IF(RC38=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC39<=R1C[1],RC40R1C[1]),IF(RC41=""Unavailable"",""-1"",1),0)" & _ "+IF(AND(RC42<=R1C[1],RC43R1C[1]),IF(RC44=""Unavailable"",""-1"",1),0)"] -------------------- -- Jim28 ------------------------------------------------------------------------ Jim28's Profile: http://www.excelforum.com/member.php...o&userid=35752 View this thread: http://www.excelforum.com/showthread...hreadid=555297 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem with LARGE function | Excel Worksheet Functions | |||
| Problem with a large formula | Excel Discussion (Misc queries) | |||
| Problem with a large formula | Excel Discussion (Misc queries) | |||
| Big problem with large spreadsheet. | Excel Discussion (Misc queries) | |||
| Solver - problem too large | Excel Worksheet Functions | |||
Hybrid Mode
