Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    If Function Surpress False (v97)

    Using the If function with no instructions for the false value, returns the word false if the true condition is not met. Generally, I would put - Value if False "" - to produce a blank, but this cell is a precedent for a Std. Dev. calculation and when it returns a blank, it causes the dependant cell to return an error. If you use 0 for the Value if false the Std Deviation calculation is inaccurate. The user would prefer NOT to have the word "false" to show ... is there another way to surpress it? Thanks for the help.

    Chance

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If Function Surpress False (v97)

    Chance, As the attached worksheet shows, STDEV is not affected by "". There must be some other problem. Can you give us more details? --Sam
    Attached Files Attached Files
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

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

    Re: If Function Surpress False (v97)

    Using "" for the False condition should do what you want, as long as you don't put a space between those quotes.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Silicon Valley, California, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Surpress False (v97)

    You could apply a custom number format, like:

    <pre>0.00;-0.00;0.00;""
    </pre>


    This would make negative, positive and zero values show up with two decimals, and text to not appear at all (which is the case with TRUE and FALSE).

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Surpress False (v97)

    All text is ignored by the STDEV function, including True and False boolean values.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: If Function Surpress False (v97)

    I am not familiar with the STDEV function, but the original poster said he was getting an error from whatever function/formula he was using.
    Legare Coleman

  7. #7
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: If Function Surpress False (v97)

    Legare, that could be it: STDEV & STDEVP will give correct results because they skip text and logicals, but STDEVA and STDEVPA would give different (hesitate to say wrong) results because they include text and logicals. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  8. #8
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Surpress False (v97)

    Thank you Jan, Jim, Lagare and Sam ... I was mistaken on the formulas as it isn't my worksheet but rather a clients. I needed to get ahold of the worksheet .....Here are the two formulas that are involved:

    =IF(COUNT(B153:I302),STDEV(B153:I302)) if this formula contains the "" it produces an error in the formula shown below

    =IF(COUNT(D13),SQRT((D1^2+D2^2+D3^2)/COUNT(D13)),"")

    However, returning the false in the first formula produces an accurate calculation in the second. The user doesn't want the "false" to appear in the first cell, if possible.

    I truely value the input from you all and I apoligize for being unclear in my first post.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Surpress False (v97)

    Maybe he should use a conditional format on the first formula, making the font colour white when the cell's value equals FALSE?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    Star Lounger
    Join Date
    Jan 2001
    Location
    WNC USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: If Function Surpress False (v97)

    Jan ... thanks ... I should have thought of that ... yes, that works for his purposes. Thank you so much for rattling this sometimes fuzzy brain. I guess I was trying to make it more complicated than necessary

Posting Permissions

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