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

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

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

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

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

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

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

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

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

Large formula problem
 

Alternative UDF follows. My first one assumed that the row containing the
numbers and text was fixed at 3 columns below the cell containing the target
value:

Function JimsFunct(c As Range, negtext As String) As Integer
Dim i As Integer
Dim v1 As Double, v2 As Integer
Dim rw As Long, col As Long

With Application
.Volatile True
rw = .Caller.Row
End With
col = c.Column: v1 = c.Value: v2 = 0
For i = col + 2 To col + 41 Step 3
If Cells(rw, i - 2) <= v1 And Cells(rw, i - 1) v1 Then
v2 = v2 + IIf(Cells(r, i) = negtext, -1, 1)
End If
Next
JimsFunct = v2
End Function

Large formula problem
 

Correct typo:
v2 = v2 + IIf(Cells(r, i) = negtext, -1, 1)
should be:
v2 = v2 + IIf(Cells(rw, i) = negtext, -1, 1)


"Greg Wilson" wrote:

Alternative UDF follows. My first one assumed that the row containing the
numbers and text was fixed at 3 columns below the cell containing the target
value:

Function JimsFunct(c As Range, negtext As String) As Integer
Dim i As Integer
Dim v1 As Double, v2 As Integer
Dim rw As Long, col As Long

With Application
.Volatile True
rw = .Caller.Row
End With
col = c.Column: v1 = c.Value: v2 = 0
For i = col + 2 To col + 41 Step 3
If Cells(rw, i - 2) <= v1 And Cells(rw, i - 1) v1 Then
v2 = v2 + IIf(Cells(r, i) = negtext, -1, 1)
End If
Next
JimsFunct = v2
End Function