Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    VALUE# error (Excel xp)

    AD813 is 21**. I want a hyphen to appear in AB813 if AD813 is less than 30. I get a VALUE# error but it works if i take the 2 asterisks out. How do i work around this? thanks

    =IF(VALUE(AD813)<30,"-",AB813)

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

    Re: VALUE# error (Excel xp)

    What do you want to do with 21**? Being "less than 30" dosn't make sense to Excel or me. --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>

  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: VALUE# error (Excel xp)

    Try

    =IF(VALUE(SUBSTITUTE(AD813,"*",""))<30,"-",AB813)
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: VALUE# error (Excel xp)

    Thank you. that works!

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

    Re: VALUE# error (Excel xp)

    I forgot to mention that you could also use 0 instead of "-", and then format the cell(s) to use "-" as the zero indicator; which has the advantage that if you need to refer to that cell in another formula you won't have to mess more with "Value" conversions.
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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