Hi all, I have the following nested IIF statement but I suspect it is a clumsy beginners effort and would like to know if this can be parred down and perhaps extended beyond the 11 periods it covers, Thanks, Cliff
AvgDnPen=IIf((DnPenSum/DnPenCount > 0),DnPenSum/DnPenCount, IIf(Ref(DnPenSum/DnPenCount,-1)> 0,Ref(DnPenSum/DnPenCount,-1), IIf(Ref(DnPenSum/DnPenCount,-2)> 0,Ref(DnPenSum/DnPenCount,-2), IIf(Ref(DnPenSum/DnPenCount,-3)> 0,Ref(DnPenSum/DnPenCount,-3), IIf(Ref(DnPenSum/DnPenCount,-4)> 0,Ref(DnPenSum/DnPenCount,-4), IIf(Ref(DnPenSum/DnPenCount,-5)> 0,Ref(DnPenSum/DnPenCount,-5), IIf(Ref(DnPenSum/DnPenCount,-6)> 0,Ref(DnPenSum/DnPenCount,-6), IIf(Ref(DnPenSum/DnPenCount,-7)> 0,Ref(DnPenSum/DnPenCount,-7), IIf(Ref(DnPenSum/DnPenCount,-8)> 0,Ref(DnPenSum/DnPenCount,-8), IIf(Ref(DnPenSum/DnPenCount,-9)> 0,Ref(DnPenSum/DnPenCount,-9), IIf(Ref(DnPenSum/DnPenCount,-10)> 0,Ref(DnPenSum/DnPenCount,-10), Ref(DnPenSum/DnPenCount,-11)))))))))))); PS its related to the Elder SafeZone stop calc's.
