Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Proper IF syntax (Excel 2000)

    I figure I could beat my head against a wall for a few hours OR I could come here and get a quick answer to my problem. I want the cell to be blank if B5 is blank, and if B5 is not blank, I want a formula to be evaluated. The current formula displays the formula, and not the result. Here it is:

    IF (B5="","",((B5-B4)*C4)+((E5-E4)*F4)+((H5-H4)*I4)+((K5-K4)*L4)+((N5-N4)*O4)+((Q5-Q4)*R4)+((T5-T4)*U4))

    I'm guessing it's a simple mistake I'm making; can anyone help me?

    Thanks!

  2. #2
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Chicago, Illinois, USA
    Posts
    187
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Re: Proper IF syntax (Excel 2000)

    Try this
    IF(isblank(B5)=true,"",((B5-B4)*C4)+((E5-E4)*F4)+((H5-H4)*I4)+((K5-K4)*L4)+((N5-N4)*O4)+((Q5-Q4)*R4)+((T5-T4)*U4))

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Proper IF syntax (Excel 2000)

    =IF(ISBLANK(B5),"",((B5-B4)*C4)+((E5-E4)*F4)+((H5-H4)*I4)+((K5-K4)*L4)+((N5-N4)*O4)+((Q5-Q4)*R4)+((T5-T4)*U4))

    works for me. Perhaps one of the source cells has text instead of a value? If you are still having problems, post the sheet as an attachment.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    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: Proper IF syntax (Excel 2000)

    A point of clarification:
    A cell will not be "blank" if it has a formula in it. It can have a null and "look blank", but it is not blank.

    There is no difference in display, but there is a big difference if you try to chart with that range. In a chart:

    Blanks are skipped and the line is "broken"
    Text is plotted as a "zero"
    #N/A error is ignored, but the line continues
    Other errors are plotted as "zero"

    Steve

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper IF syntax (Excel 2000)

    Three things:

    1- You must have an equal sign in front of the IF for this to be a formula. Without the "=", it is just a string that will display as typed.

    <pre>=IF(B5="","",((B5-B4)*C4)+((E5-E4)*F4)+((H5-H4)*I4)+((K5-K4)*L4)+((N5-N4)*O4)+((Q5-Q4)*R4)+((T5-T4)*U4))
    </pre>


    2- There should not be a space between IF and the left paren.

    3- Make sure that the cell is not formatted as Text when the formula is entered. If the cell is formatted as Text, then what you enter will be just a text string and display as typed.
    Legare Coleman

  6. #6
    Lounger
    Join Date
    Jan 2001
    Location
    Arcadia, California, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Proper IF syntax (Excel 2000)

    Thanks for all your help! The formula that worked was

    =IF(B5=0,"",((B5-B4)*C4)+((E5-E4)*F4)+((H5-H4)*I4)+((K5-K4)*L4)+((N5-N4)*O4)+((Q5-Q4)*R4)+((T5-T4)*U4))

    The omission of the '=' at the beginning, the space between the IF and the open paren and the condition B5="" were all a problem. Without your help it would have taken me hours and I might never have figured it out. Thanks again!

Posting Permissions

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