Results 1 to 14 of 14

Thread: [RESOLVED] LOGICAL TEST??? Please help!

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Resolved [RESOLVED] LOGICAL TEST??? Please help!

    Hello,

    I am desperately struggling to find a solution to this problem i am having...

    Here is what i want to achieve in plain english:

    If J2 Does NOT contain "Exam Cancelled" or "Further possession required" then N2 equals B2 plus 28 days.

    If J2 is blank then N2 is blank.
    If B2 is blank then N2 is blank.

    Ideally, in addition, if B2 is beyond todays date (ie date has passed) then N2 will again equal B2 plus 28 days

    Can anybody provide me with the logical test formula for this which i can copy into each cell in N2?

    Please be gentle

    Any efforts will be appreciated.
    Many thanks.
    Sam.
    Attached Images Attached Images  

  2. #2
    PowerPoster Spoo's Avatar
    Join Date
    Nov 2008
    Location
    Right Coast
    Posts
    2,656

    Re: LOGICAL TEST??? Please help!

    Sam

    Two questions:
    1. How do you want to do this?
      • using VB6
      • using an Excel VBA macro
      • creating a formula for N2 and just copy and paste in col N?
    2. You say "copy into each cell in N2"
      • sorry, but that doesn't really make any sense
      • do you mean "into each cell in col N"?
    Spoo

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    Hello Spoo,

    I really have no preference... whichever way is possible. I was attempting to write a logical test with limited success but a macro may well be more suitable. My apologies,i meant copy into column N, not N2.

    Kind regards,
    Sam

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    Is this even possible?

  5. #5
    I don't do your homework! opus's Avatar
    Join Date
    Jun 2000
    Location
    Good Old Europe
    Posts
    3,863

    Re: LOGICAL TEST??? Please help!

    I have a question on the following statement:
    Ideally, in addition, if B2 is beyond todays date (ie date has passed) then N2 will again equal B2 plus 28 days
    I would understand it that way, if B2>Date it doesn't matter what is in J2, N2 will allways be B2+28!


    For the rest the Excel formula would look like this:
    Code:
    =IF(OR($J2="";$B2="");"";IF(NOT(OR($J2="Exam Cancelled";$J2="Further possession required"));$B2+28;""))
    Note: In your example the entries in column J do never match "Further possession required" nor ="Exam Cancelled"!
    You're welcome to rate this post!
    If your problem is solved, please use the Mark thread as resolved button


    Wait, I'm too old to hurry!

  6. #6
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: LOGICAL TEST??? Please help!

    logic is always possible , you should look at what you want and try to push the truth values about, until you see the full truth, try asking some of the questions in reverse, it may help you formulate a sometimes unusual but neat test...

    if you are asking about the possibilities of a logic statement then the code above may be quite daunting as it involves a complete understanding not just of logic but the workings of the if()..

    do you understand the code above?

    here to help

  7. #7
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: LOGICAL TEST??? Please help!

    i played with you logic..

    Code:
    If J2 Does NOT contain "Exam Cancelled" or "Further possession required" then N2 equals B2 plus 28 days. 
    
    If J2 is blank then N2 is blank.
    If B2 is blank then N2 is blank.
    
    Ideally, in addition, if B2 is beyond todays date (ie date has passed) then N2 will again equal B2 plus 28 days
    and got this
    Code:
    n=b+28 when b>now and when j is not "Exam Cancelled" or "Further possession required" 
    and special case when j="" or b="" then n=""
    or this
    Code:
    when b="" or j="" or j="Exam Cancelled" or "Further possession required"  and  b<=now then n="" otherwise n=b+28
    that should all fit into an if()

    the stucture of if() is

    if(test,action if true,action if false)

    so the outcome part is easy

    if(test,"",b+28)

    the test is the complex bit

    or() takes values to test separated by commas and and does like wise so the embedding of an or and the and is important...

    we have or(b="" , j="", j="Exam Cancelled",j= "Further possession required" )
    and "and(b<=now)"
    the and goes on the outsidelike this
    and(thing1,thing2)

    so AND(b<=now,or(b="" , j="", j="Exam Cancelled",j= "Further possession required" ))

    can you see the logic of the structure?

    so finally we have an if() like...

    if(AND(b<=now,or(b="" , j="", j="Exam Cancelled",j= "Further possession required" )),"",b+28)

    you will need to as the rowof course so that you reference an individual cell

    final code is then


    Code:
    in cell n2 put =if(AND(b2<=now,or(b2="" , j2="", j2="Exam Cancelled",j2= "Further possession required" )),"",b2+28)
    some of you may have seen an error here the now is infact a function and needs to be now() - so the final piece of code is...


    Code:
    in cell n2 put =if(AND(b2<=now(),or(b2="" , j2="", j2="Exam Cancelled",j2= "Further possession required" )),"",b2+28)
    there is nothing in this code to make the result of b2+28 look like a date so it expects the column it is in to be formatted as a date

    hope that helps

    remember to rate posts that help and close the thread using the tools on the bar at the top
    Last edited by incidentals; Feb 16th, 2012 at 09:46 AM.

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    Thankyou Opus and Incidentals for your time... i am albeit very slowly starting to understand how it all works. i consider myself fairly computer literate but this is (literally) like another language to me!

    I am getting this error (see attached pic) when i enter this into the cell. since i dont doubt your programming knowledge, i suspect is a silly mistake at my end. What do i need to change in order for this to work? The date column is currently set to ^date^ in the format cells category.

    Also, this file will need to be stored and opened from our (very slow) server and so the smaller the filesize can be the better.... Any ideas on how to reduce the size with all these formulas in? i read somewhere if you apply formula or fomatting to a column as opposed to copying to each cell it might reduce the size of the file?

    Kind regards,
    Sam
    Attached Images Attached Images  

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    P.S i am using excel 2003.


    Regards,
    Sam

  10. #10
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: LOGICAL TEST??? Please help!

    the now should be now() or date


    now is a function and should have its empty brackets after it

    date is the new name for this function there are a selection of date and time functions, you should look up these in the help

    sorry went too quickly with the short cut pcode

    here to help

    i just double checked it works fine, do not forget to make the column n format date
    Last edited by incidentals; Feb 16th, 2012 at 09:38 AM.

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    Thank you Incidentals, there is no longer an error but its not giving me exactly what i want, it is showing a date in N2 even though the date has not yet passed. I have been playing around with it trying to make it work but still cant make much sense of it.

    Could you please take another look at it, I may not have explained correctly?

    This is what im trying to achieve,

    1. if column J does not equal "cancelled" and does not equal "possession" and column B is blank or date in column B has not yet passed then column N is blank

    2. if column b date has passed and column J does not equal "cancelled" and does not equal "possession" then date in column N is date in column B +28 days

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    SOMETHING like this, but i cant get this to work

    Code:
    IF(OR(J<>"Cancelled - See Comments", J<>"Possesion Req'd - See Comments",B<NOW(),"")AND(B>=NOW(),J<>"Cancelled - See Comments", J<>"Possesion Req'd - See Comments"),B+28))

  13. #13

    Thread Starter
    Lively Member
    Join Date
    Jan 2012
    Posts
    98

    Re: LOGICAL TEST??? Please help!

    im so proud of myself! i manged to (sort of) understand it and get it to work!
    Thankyou for all your time and explanations everyone... i finally used this code:

    Code:
    =IF(OR(B2>TODAY(),B2="",J2="Cancelled - See Comments",J2="Possesion Req'd - See Comments"),"",B2+28)

  14. #14
    Frenzied Member
    Join Date
    Nov 2010
    Posts
    1,470

    Re: [RESOLVED] LOGICAL TEST??? Please help!

    its late in the day i know but a note for future stuff...

    you tried to use

    if(or(a,b,c)and(d,e,f),action if true,action if false)

    the problem with that structure is the "and" cannot be between the things

    you link if (a AND b) then

    but you write

    if and(a,b)

    so your if (a or b) and (c and d)

    should be written

    if and(or(a,b),and(c,d)

    its a little odd all the front loading of operators ,but thats how it is here!

    here to help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width