Julie,

You would do well to build a truth table, with the answers included in the table. The macro below
will create the truth table in columns L,M,N, and O , from rows 1 to 27, and will put a formula in
cell K3 that will replace your formula. You will need to change the values in column O from
ZStringOption01 etc. to your desired values - the truth table is based on TRUE for 0, FALSE for <0,
and 0 for 0.

HTH,
Bernie
MS Excel MVP

Option Base 1
Option Explicit
Sub TryNow()
Dim myC As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim myArr As Variant

myC = 1
myArr = Array(True, False, 0)
For i = 1 To 3
For j = 1 To 3
For k = 1 To 3
Cells(myC, 12).Value = myArr(i)
Cells(myC, 13).Value = myArr(j)
Cells(myC, 14).Value = myArr(k)
Cells(myC, 15).Value = "ZStringOption" & Format(myC, "00")
myC = myC + 1
Next k
Next j
Next i

Range("K3").FormulaR1C1 = _
"=INDEX(R1C15:R27C15,SUMPRODUCT((IF(RC[-3]=0,0," & _
"RC[-3]0)=R1C12:R27C12)*(IF(RC[-2]=0,0,RC[-2]0)" & _
"=R1C13:R27C13)*(IF(RC[-1]=0,0,RC[-1]0)" & _
"=R1C14:R27C14)*ROW(R1C15:R27C15)))"
End Sub


"Julie" wrote in message
...
Please help with the below formula

=IF(AND(H30,I30,J30),"Loyal$$$"),IF(AND(H30,I3 0,J3=0),"Retained$$0"),IF(AND(H30,I=0,J30),"Ret urning$0$"),IF(AND(H30,I3=0,J3=0),"New$00"),IF(AN D(H3=0,I30,J30),"Lapsed0$$"),IF(AND(H3=0,I30,J3 =0),"Lapsed0$0"),IF(AND(H3=0,I3=0,J30),"Lost00$") ,IF(AND(H3=0,I3=0,J3=0),"NoSales000"),IF(AND(H3=0, I3=0,J3<0),"ZDead00N"),IF(AND(H3<0,I3<0,J3<0),"ZDe adNNN"),IF(AND(H3<0,I3=0,J3=0),"ZDeadN00"),IF(AND( H3=0,I3<0,J3<0),"ZDead0NN"),IF(AND(H3<0,I3=0,J3<0) ,"ZDeadN0N"),IF(AND(H3=0,I3<0,J3=0),"ZDead0N0"),IF (AND(H3<0,I30,J30),"ZLaspedN$$"),IF(AND(H3<0,I3 0,J3<0),"ZLapsedN$N"),IF(AND(H3<0,I30,J3=0),"ZLap sedN$0"),IF(AND(H3<0,I3<0,J30),"ZLostNN$"),IF(AND (H3<0,I3=0,J30),"ZLostN0$"),IF(AND(H3=0,I3<0,J30 ),"ZLost0N$"),IF(AND(H3=0,I30,J3<0),"ZRecovering0 $N"),IF(AND(H30,I3<0,J30),"ZRecovering$N$"),IF(A ND(H30,I3<0,J3=0),"ZRecovering$N0"),IF(AND(H30,I 3<0,J3<0),"ZRecovering$NN"),IF(AND(H30,I3=0,J3<0) ,"ZRecovering$0N"),IF(AND(H30,I30,J3<0),"ZRetain ed$$N")