Home › Forums › Discussion Forum › Number of nested IIF statements possible?
Tagged: error, IIF, Microsoft Project 2007, nested, possible number
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
Thanks for the helpful suggestions Rod, I appreciate it! I went with a VBA macro and it is working.
Larry Schiff