May I suggest you read post#26 and make up your mind as to EXACTLY what you want.
Here we go...from the top for cell D57
The scenarios if D3 Is Pending:
IF D3=PENDING and D4=PENDING and D5=PENDING then D57 =PENDING
IF D3 = PENDING and D4=Yes and D5=PENDING then D57= PENDING
IF D3=PENDING and D4=No and D5=PENDING then D57 = PENDING
IF D3=PENDING and D4=PENDING and D5=Yes then D57=PENDING
If D3=PENDING and D4=PENDING and D5=No then D57=PENDING
IF D3=PENDING and D4=Yes and D5=YES then D57 = PENDING
If D3=PENDING and D4=No and D5=No then D57 = PENDING
If D3=PENDING and D4=No and D5=Yes then D57= PENDING
If D3=PENDING and D4=Yes and D5=No then D57=PENDING
So ANY scenario where D3 is PENDING...REGARDLESS what D4 and D5 have, D57 should always be PENDING. Why? Because I have not yet told the formula if the estimate will use a ST (straight time) billing rate or an OT (over time billing rate)...the ST and OT choice is the drop down in D3. I may know my D4 is a Yes and My D5 is a NO, but since I don't know if the job will be ST or OT, I want to leave it PENDING. Im not committing to a billing rate in the D3 scenarios'above.
Moving on.....
We'll call these the ST scenarios:
IF D3= ST and D4=Yes and D5=PENDING then D57=ST-LEAD/ASB
If D3=ST and D4=No and D5=PENDING then D57=PENDING
(I believe this is one where I was confusing myself)
IF D3 = ST and D4=PENDING and D5=YES then D57=ST-LEAD/ASB
If D3=ST and D4=PENDING and D5=No then D57 is PENDING (
I believe this is one where I was confusing myself)
IF D3=ST and D4=NO and D5=NO then D57=ST
If D3=ST and D4=YES and D5=YES then D57=ST-LEAD/ASB
IF D3=ST and D4=Yes and D5=No then D57=ST-LEAD/ASB
If D3=ST and D4=No and D5=Yes then D57=ST-LEAD/ASB
So any scenario where D3 Is ST and ANY D4 & D5 combination that contains at least one YES, then the ST-LEAD/ASB billing rate is chosen. Why? Because If the job contains lead OR contains asbestos then the LEAD/ASB billing rate has to be chosen. Thats why if I have a combo of a D4=NO and a D5=PENDING, then D57=PENDING because I have determined by this point the job does not contain lead but I'm waiting on the asbestos results...so I do not want to commit to a billing rate just yet...so im leaving it PENDING until I get that asbestos result in. Also, it is why I CAN commit to a LEAD/ASB rate if either D4 or D5 is a YES....so if I know the job contains lead, I chose YES in D4...then I know automatically I will be using the ST-LEAD/ASB rate regardless what the asbestos results come back...and visa-versa..if I KNOW the job contains asbestos I chose YES in D5 and may have D4 as PENDING
(because i haven't gotten lead results back yet) the billing rate automatically goes to ST-LEAD/ASB...regardless what those lead results are. Simply put, as long as D3 has an ST (or an OT) I can commit to a billing rate if I have a YES in either D4 or D5....I can't commit to a billing rate If I have a D4 NO and a D5 PENDING or a D4 PENDING and a D5 NO...so If I can't commit to a billing rate just yet, then D57 should remain as PENDING.
We'll call these the OT scenarios:
Same rules apply as right above ..just change all the ST's to OT's
IF D3= OT and D4=Yes and D5=PENDING then D57=OT-LEAD/ASB
If D3=OT and D4=No and D5=PENDING then D57=PENDING
(I believe this is one where I was confusing myself)
IF D3 = OT and D4=PENDING and D5=YES then D57=OT-LEAD/ASB
If D3=OT and D4=PENDING and D5=No then D57 is PENDING (
I believe this is one where I was confusing myself)
IF D3=OT and D4=NO and D5=NO then D57=OT
If D3=OT and D4=YES and D5=YES then D57=OT-LEAD/ASB
IF D3=OT and D4=Yes and D5=No then D57=OT-LEAD/ASB
If D3=OT and D4=No and D5=Yes then D57=OT-LEAD/ASB
So again, any scenario where D3 Is OT and ANY D4 & D5 combination that contains at least one YES, then the OT-LEAD/ASB billing rate is chosen.
I hope this clears up any misunderstandings....it helped me understand it better. I think where I was screwing myself up and everyone else was, I kept writing what I was wanting and it was hard to keep track and also the scenarios where i listed I was confusing myself, I was saying one thing but meaning something else...once I wrote out the senario I was like "wait a minute....somëthing isn't isn't right here"...I literally said the combinations out loud to to myself about 5 times and then it clicked..i had that ÄHHH HAAAA"moment.
I am so sorry for any confusion and I appreciate you zand others helping me....What I laid about above is what I am looking for.