Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: Multiple IF Function

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Can anyone tell me what is wrong with this function. I'm trying to setup an "Or" scenario, where the value in less than OR equal to.

    =IF(B4
    Thanks

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 09:11, Adrae wrote:
    Can anyone tell me what is wrong with this function. I'm trying to setup an "Or" scenario, where the value in less than OR equal to.

    =IF(B4
    Thanks
    =(B4<=Sheet1!E4)*WIE!$F$6


  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Kent, the Garden of England, that no one has bothered to weed.
    Posts
    65
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    try this, though I might be Completely wrong.

    =IF(B4<=Sheet1!E4,WIE!$F$6,0))




  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Well the error is gone, but the value is 0 no matter what. The values is Sheet1!E4 & B4 are dates formatted as MM/DD/YY. Could this be the problem, and if so, any ideas how to fix it?

    If B4 is equal to or less than (comes before on calendar)E4 then I want to pull a specific value from another cell, else 0.

    Thanks

  5. #5
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-26 09:22, Adrae wrote:
    Well the error is gone, but the value is 0 no matter what. The values is Sheet1!E4 & B4 are dates formatted as MM/DD/YY. Could this be the problem, and if so, any ideas how to fix it?

    If B4 is equal to or less than (comes before on calendar)E4 then I want to pull a specific value from another cell, else 0.

    Thanks [img]/board/images/smiles/icon_smile.gif[/img]
    Try whether this works:

    =(B4+0<=Sheet1!E4+0)*WIE!$F$6



  6. #6
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That works beautifully!! Thanks

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF( B4 =< Sheet1!E4 , WIE!$F$6 , 0)

    The "=<" means "equals to or less than".

    [ This Message was edited by: Cosmos75 on 2002-03-26 10:06 ]

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Chicago, IL USA
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    What about a "between" function. For example:

    If E4 is between the dates 1/01/02 and 1/31/02 then value should be "x"

    Any thoughts?

    Thanks

  9. #9
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    359
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You can do this two ways

    put your two reference dates in A1 (01/01/2002) and A2 (01/31/2002) and the cell you are looking at in E3

    =IF(AND(E3 < A2 , E3 > A1),"OK","Not in range")

    Or figure out the numerical value of the date by formating A1:A2 as numbers and use those number to replace the A2 and A3 in your formula.

    In this case 01/01/2002 = 37257 and 01/31/2002 = 37278.

    =IF(AND(E3<37287,E3>37257),"OK","Not in range")

    Hope this helps.

    [ This Message was edited by: Cosmos75 on 2002-03-26 10:34 ]

  10. #10
    Board Regular
    Join Date
    Feb 2002
    Posts
    255
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    =IF(B4
    You're missing a parentheses after the second if, it should be:

    =IF(B4
    This reads,
    If B4 Otherwise If b4=sheet1 e4, then WIE f6
    Otherwise 0

    You should use a <= in this statement, but it is possible to nest if's you just have to have the write syntax. Here's an example of between:

    =IF(A4>=10,IF(A4<=30,"True","False"),"False")
    This will print "True" if the number is between 10 and 30 and "False" otherwise.

    It works like this:
    You can think of it as being If(condition,then, else)
    If the condition a4>=10 is true then it goes to the then section and evaluates the next if. If a4<+30 is true it goes to then and returns "True". The then section of the initial if then becomes true and the cell prints true. It it's false, the then condition of the initial if becomes false and it prints "False". If the condition of the intial if is false it goes to the else section and prints "False".

    I hope that makes sense and is not too confusing. You can nest up to 7 ifs in this manner.

    Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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