Number of nested IIF statements possible?

Home Forums Discussion Forum Number of nested IIF statements possible?

Viewing 6 reply threads
  • Author
    Posts
    • #4849
      Hayam Wahdan
      Keymaster

      I created a big nested IIF statement (41 levels) in MSProj 2007 Standard edition and I get an error statement. When I cut the statement down a lot, to like 3 or 4 levels (which is about where it begins highlighting the supposed error), it works fine. What I’m trying to do is calculate a work estimate based upon information contained in the task name (specific 4 character budget numbers). Each of 40 different budget numbers belong to one of 5 different estimating values. I’m trying to write a VBA macro, but it is much harder to write to have the same functionality as the 41 level nested IIF formula. Note: I’m using the Number1 and Number2 fields. The formula is for the Number2 field. The Number1 field just has numeric values ranging from 1 to 250, based upon the task. Nested IIF formala for reference: IIf([Name] Like “*6833*”,[Number1]*90/60, IIf([Name] Like “*6834*”,[Number1]*60/60, IIf([Name] Like “*6501*”,[Number1]*50/60, IIf([Name] Like “*6817*”,[Number1]*50/60, IIf([Name] Like “*6818*”,[Number1]*50/60, IIf([Name] Like “*6511*”,[Number1]*50/60, IIf([Name] Like “*6810*”,[Number1]*50/60, IIf([Name] Like “*6502*”,[Number1]*40/60, IIf([Name] Like “*6835*”,[Number1]*40/60, IIf([Name] Like “*6840*”,[Number1]*40/60, IIf([Name] Like “*6841*”,[Number1]*40/60, IIf([Name] Like “*6842*”,[Number1]*40/60, IIf([Name] Like “*6843*”,[Number1]*40/60, IIf([Name] Like “*6870*”,[Number1]*40/60, IIf([Name] Like “*6203*”,[Number1]*30/60, IIf([Name] Like “*6205*”,[Number1]*30/60, IIf([Name] Like “*6206*”,[Number1]*30/60, IIf([Name] Like “*6210*”,[Number1]*30/60, IIf([Name] Like “*6520*”,[Number1]*30/60, IIf([Name] Like “*6530*”,[Number1]*30/60, IIf([Name] Like “*6815*”,[Number1]*30/60, IIf([Name] Like “*6826*”,[Number1]*30/60, IIf([Name] Like “*6831*”,[Number1]*30/60, IIf([Name] Like “*6836*”,[Number1]*30/60, IIf([Name] Like “*6837*”,[Number1]*30/60, IIf([Name] Like “*6875*”,[Number1]*30/60, IIf([Name] Like “*6932*”,[Number1]*30/60, IIf([Name] Like “*6937*”,[Number1]*30/60, IIf([Name] Like “*6820*”,[Number1]*20/60, IIf([Name] Like “*6830*”,[Number1]*20/60, IIf([Name] Like “*6845*”,[Number1]*20/60, IIf([Name] Like “*6850*”,[Number1]*20/60, IIf([Name] Like “*6401*”,[Number1]*10/60, IIf([Name] Like “*6402*”,[Number1]*10/60, IIf([Name] Like “*6403*”,[Number1]*10/60, IIf([Name] Like “*6410*”,[Number1]*10/60, IIf([Name] Like “*6420*”,[Number1]*10/60, IIf([Name] Like “*6604*”,[Number1]*10/60, IIf([Name] Like “*6621*”,[Number1]*10/60, IIf([Name] Like “*6706*”,[Number1]*10/60, IIf([Name] Like “*6860*”,[Number1]*10/60, [Number1]*30/60)))))))))))))))))))))))))))))))))))))))))

       

       

      Thanks and I really appreciate any help you can provide! Larry Schiff

    • #6046
      Hayam Wahdan
      Keymaster
      I think formulae are limited to 256 characters! Your solution is to either convert the formula to VBA code (but you would have to run it to refresh the custom field). Alternatively you are going to have to simplify the formula and break it into 4 or 5 different custom text fields that either return NA or teh answer. A further field will then return the result of any of the earlier fields not displaying NA. Personally I prefer VBA as it would be much easier to maintain and update.
      Rod Gill
    • #6054
      Hayam Wahdan
      Keymaster
      Wow!  A very IFFY propsition, Larry!  I agree, this is going to be way too long as I’m pretty sure the 256 character limit applies.  A VBA macro is probably the best option because of this limitation.  If the budget numbers were in ranges that might help to cut down the length, but I’m guessing that’s not the case.
      Matthew Davis
    • #6086
      Hayam Wahdan
      Keymaster
      LOL and thanks Matthew!   VBA is the direction I went and it works.

      Larry Schiff
    • #6085
      Hayam Wahdan
      Keymaster

      Thanks for the helpful suggestions Rod, I appreciate it!   I went with a VBA macro and it is working.

       

      Larry Schiff

    • #6087
      Hayam Wahdan
      Keymaster
      After getting a lot of help from my company’s MS Project product manager, I customized the following VBA macro.  He also recommended that I pick up a copy of Rod Gill’s "VBA Programming for Microsoft Office Project " which is now on my Christmas list.
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      Sub Calc_Number2()
      Dim intCounter As Integer
      Dim intArraySize As Integer
      intArraySize = 2 ‘intArraySize is the number of elements in the arrays
      ‘ Arrays strTest and intMult must be = intArraySize
      Dim strTest(2) As Variant
      Dim intValue(2) As Integer
      Dim strTaskName As Variant
      ‘ This is where you define the test (strTest) condition and values(intValue)
      ‘ Note: if you have two test conditions that are true the last test condition
      ‘ will be the one that modifies Number2
      strTest(1) = "*ab*"
      intValue(1) = 50
      strTest(2) = "*bc*"
      intValue(2) = 30
      Dim Tsk As Task
      ‘ Tsk is an object that contains all task fields for a given task.  The For – Next
      ‘ statement loops through every Project task and performs the test and calculations
      For Each Tsk In ActiveProject.Tasks
      intCounter = 1
      If Not Tsk Is Nothing Then ‘Do not process blank tasks.  Keeps macro from blowing up.
      Do Until intCounter > intArraySize
      If Tsk.Name Like strTest(intCounter) Then
      Tsk.Number2 = (Tsk.Number1 * intValue(intCounter)) / 60
      End If
      intCounter = intCounter + 1
      Loop
      End If
      Next Tsk
      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      A big thank you to Joe Peltier for the above code!
    • #6129
      Hayam Wahdan
      Keymaster
      WOW never know you could do so many nested IIF statements. Thanks you for sharing
      Teresa Alsdurf
Viewing 6 reply threads
  • You must be logged in to reply to this topic.