Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    IF AND Help (2002)

    I want to return "Capital" if the value in e38 is >= 2500 and return "Expense" if the value is between 0 and 2499.99. My poor attempt to do this is below. Can someone advise me on my error. This returns a false.

    Thanks.

    =IF(E38>=2500, "Capital", IF(E38<>0, AND(if E38 <=2499.99, "Expense", "")))

  2. #2
    Lounger
    Join Date
    Jun 2003
    Posts
    42
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF AND Help (2002)

    Try this:

    =IF(E38>=2500,"Capital",IF(AND(E38<2500,E38>=0),"E xpense","entered negative number"))

    --cat

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF AND Help (2002)

    Or slightly shorter

    =IF(E38>=2500,"Capital",IF(E38>=0,"Expense","enter ed negative number"))

  4. #4
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF AND Help (2002)

    Edited by HansV to present data in table format.

    Ok... Thanks... Another question.
    <table border=1><td></td><td align=center>A</td><td align=center>B</td><td align=center>C</td><td align=center>D</td><td align=center>E</td><td align=center>F</td><td align=center>G</td><td align=center>H</td><tr><td align=center>1</td><td align=right>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IF AND Help (2002)

    To prevent confusion, how do the "or"s and "and"s relate? The way you write it, the "official" interpretation would be

    Bargain Purchase Option is Yes, or Transfer Ownership is Yes, or if Lease Term... is Yes, or (NPV... is Yes and useful life is greater than or equal 3 years and amount is greater than or equal to 2500)

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF AND Help (2002)

    If you have boolean expressions giving true/false in cols B thru g:

    <pre>=if(or(b41:G41),"Capital", "Expense")</pre>


    If they are "Yes"/"No" text values and just values, it is more complicated:

    <pre>=if(or(b41="Yes", c41="Yes", D41>75%, E41>90%,F41>=3, G41>=2500),"Capital", "Expense")</pre>


    Steve

  7. #7
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF AND Help (2002)

    Steve/Hans/Cindy, thanks for the help. This almost accomplishes what I want - but fails to do one thing. If G41 is blank (no entry) I would like this to evaluate to a "" (blank space vs. a capital or expense). Is this possible?

    IF((AND(OR(B41="Yes", C41="Yes", D41= "Yes", E41="Yes",F41>="Yes"), B41<>"", C41<>"", D41<>"", E41<>"",F41<>"", G41>=2500)),"Capital", "Expense")

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: IF AND Help (2002)

    =if(G41="","",IF((AND(OR(B41="Yes", C41="Yes", D41= "Yes", E41="Yes",F41>="Yes"), B41<>"", C41<>"", D41<>"", E41<>"",F41<>"", G41>=2500)),"Capital", "Expense") )

    Steve

  9. #9
    3 Star Lounger
    Join Date
    Mar 2004
    Location
    North Carolina, USA
    Posts
    268
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: IF AND Help (2002)

    Thanks.

Posting Permissions

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