Logical test

[WTB]

Hi all im trying to apply a logical test to a number of cells.
With an example below:



A B C Status
Sent Received Sent Received Sent Received
01/02/2009 02/03/2009 03/04/2009 05/04/2009 05/06/2009 07/07/2009 Complete
01/02/2009 02/03/2009 03/04/2009 05/04/2009 Complete
01/02/2009 02/03/2009 03/04/2009 Incomplete


I want the status column to update automatically with ‘complete’ or ‘incomplete’, so in order for it to be complete there must be a received date, however as you can see C may not necessarily be sent, so a suitable formula would take into account that the received column would only need a date if there is a date in the sent column. Does this make sense? And is it possible!? I’ve played around with the AND function, but can’t seem to get it right and to be honest I’m not convinced it’s the right function to use!

Any help would be much appreciated!

[darkyam]

=IF(ISODD(COUNT(A2:F2)),"Incomplete","Complete")

[WTB]

Hi darkyam,

Thanks for your help, i've applied that formula but it keeps returning #NAME?

Any ideas??

Thanks again

WTB

[DonkeyOte]

ISODD forms part of the Analysis ToolPak and requires activation pre XL2007 via Tools Add-Ins

Alternatively use MOD:

=IF(MOD(COUNT(A2:F2),2),"Incomplete","Complete")

[WTB]

Thanks guys, and sorry for my naivety! I'm always looking on here to help people out but its a case of everyone else been too quick or more often than not it all going over my head! So i end up always asking questions and never given anything back. I'm sure i'll get there, but I'm still learning the wonders of excel! I applied the formula you suggested DonkeyOte and it works a treat!

Thanks again for your help

WTB

[darkyam]

Sorry, I should have remembered that was part of the Analysis Toolpak. As an aside, I recommend you install it anyway, just because it has a bunch of date and other functions that are extremely useful.

[WTB]

Just found another small problem with it, if there is nothing in any of the cells it returns 'complete' For example if A is not even sent it still returns 'complete' when in actual fact its incomplete! Any way around this?

Thanks

WTB

[darkyam]

Modifying DO's solution: =IF(OR(COUNT(A2:F2)=0,MOD(COUNT(A2:F2),2)),"Incomplete","Complete")

[WTB]

Again thanks for your help darkyam, however when i apply your suggested formula it always returns incomplete even if it is complete.

Any more ideas?


Thanks

WTB

[DonkeyOte]

You could use:

=IF(MOD(MAX(1,COUNT(A2:F2)),2),"Incomplete","Complete")

Though I don't disagree with darkyam's point that ATP is very useful (esp. Date formulae) it's worth noting that any Client operating a file with ATP reliant functions requires the ATP be activated first so as to avoid #NAME? errors.
If needed you can use VBA in the open event to activate it.

In essence you should always remember that the ATP is not "distributed" as part of the workbook.

[darkyam]

It works just fine for me. The only thing I can think of is perhaps your dates are stored as text? If that's the case, DO's shouldn't have worked before, but it can be fixed by changing Count to Counta, which counts all non-blank cells in a range instead of just cells with numbers.

[darkyam]

DO, your latest formula returns incomplete unless I have no numbers filled in.

[WTB]

Thanks DonkeyOte, Similar (but ironically the opposite) to darkyam's formula when applying this one it returns every thing as 'Complete' even if its not!

Also what exactly is VBA? I'm very new to this so do struggle with some of the terms!

Thanks again


WTB

[DonkeyOte]

These posts are crossing.

In my original I put MIN whereas I meant MAX - I edited it but I suspect after you copied it.

WTB - if you can't get this to work please post a sample..

VBA is Visual Basic for Applications: http://en.wikipedia.org/wiki/Visual_...r_Applications

[WTB]

Thanks again DO, thats got it sorted!

Thanks for the VBA link i shall read into it.

For future reference what is the best way to post a sample, simply attaching a file?

Thanks

WTB

[DonkeyOte]

To attach, in the reply window click the Paperclip icon and attach the file - if you can't see the icon click on "Go Advanced"

[WTB]

Thanks! This is now officially solved!

Thanks again both of you for your time and help

All the best

WTB

[WTB]

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]

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:

Please Login or Register  to view this content.

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

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.

[WTB]

Thanks DO, the prediction of how many times it will be sent/received was just wishful thinking!

Ok so if i were to put a column in titled "No. sent/received", with simply a number 1-4 how could i link that in to the equation so it shows if its complete or not? Thanks

WTB

[DonkeyOte]

Well, if you can assume that the number of sent/received won't exceed the number you stipulate then the calculation is in reality simpler.

Let's assume the 1-4 value is entered into Col J.

Based on your file (J3:J8):

4
3
3
2
3
2

Then

K3: =IF(COUNT(C3,E3,G3,I3)<J3,"Incomplete","Complete")
copied down

would result in (K3:K8)

Complete
Complete
Incomplete
Incomplete
Incomplete
Incomplete

[WTB]

Yeah that does make the whole thing far simpler! Seems to have gone round in circles and ended up being simple! Thanks for your help DonkeyOte, much appreciated!

WTB