Logical test

**WTB** · 02-08-2010, 06:44 AM

Hi all, sorry to bring this one back up, but I still can’t quite get it right! This time I’ve attached a sample which will hopefully make it easier to figure out.

I have 8 columns 4 that could contain sent dates and 4 that could contain received dates.
I am trying to achieve a ‘Progress’ column at the end to tell me if it is ‘complete’ or ‘in complete’.
If something is sent it must be received to complete.
Now the problem seems to be that each row is not necessarily send and received 4 times, so it could be sent and received 3 times or even just the once. With the formula that DonkeyOke kindly gave me it tells me if all have been sent and received, however when it is sent out to two people at the same time the formula returns complete!
The other problem is that if something is sent and then received, and is held before it goes out again it returns complete, when in actual fact it may need to be sent/received a further 3 times, now I guess the only way I can solve this is to predetermine how many times it is going to be sent/received. Which is not ideal as in some cases this will be hard to predict, so I was wondering if anyone had any ideas? I just want to make the whole thing as user friendly as possible!

Thanks in advance for any help

WTB

**DonkeyOte** · 02-08-2010, 07:05 AM

Originally Posted by WTB

when it is sent out to two people at the same time the formula returns complete!

You can account for this by modifying the approach such that:

K3: =IF(OR(COUNT(B3:I3)=0,(COUNT(B3,D3,F3,H3)-COUNT(C3,E3,G3,I3))>0),"Incomplete","Complete")
copied down

If the reality is that you have more than 4 columns of each to account for you can revert to a MOD based SUMPRODUCT - if not it's not really worthwhile

Originally Posted by WTB

The other problem is that if something is sent and then received, and is held before it goes out again it returns complete, when in actual fact it may need to be sent/received a further 3 times, now I guess the only way I can solve this is to predetermine how many times it is going to be sent/received. Which is not ideal as in some cases this will be hard to predict, so I was wondering if anyone had any ideas?

If you don't know this in advance yourself it will obviously prove very difficult for XL to calculate this ...

In short you will have to stipulate the expected no. of returns in advance - as a general rule of thumb XL isn't great when it comes to guessing.