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