Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    '#VALUE!' Error...using as text (XP)

    I'm trying to automate a spreadsheet that allows me to just copy and paste the data into it, and then it calculates everything from there.

    I have formulas based on formula results, and sometimes the results are #VALUE!.

    I understand that "#VALUE!" is invalid, but the cause of the "#VALUE!" result is always the same, which essentially is a result I'm looking for.

    What I need to do is just convert "#VALUE!" to another value (doesn't matter if it's text or numerical).

    I can't get any formulas to do that (I've used IF and FIND).

    Without the use of a macro, is there any way I can do this?

  2. #2
    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...using as text (XP)

    Unless you are manipulating text, try =ISNUMBER(reference). It will return TRUE for any valid numeric result or FALSE for anything else, and you can work your dependent result from there.
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: '#VALUE!' Error...using as text (XP)

    You could employ the ISERROR function with the following format

    =IF(ISERROR(formula),"",formula),)

    e.g.if you have a formula =(A1*B1) use =IF(ISERROR(A1*B1),"-",A1*B1). If at least one of A1 or B1 is text, A1*B1 would return a #VALUE error which with this formula would be replaced by a dash (minus sign).

    Andrew C

  4. #4
    2 Star Lounger
    Join Date
    Oct 2001
    Location
    Not in KC anymore
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: '#VALUE!' Error...using as text (XP)

    Thanks.

    This did the trick.

  5. #5
    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: '#VALUE!' Error...using as text (XP)

    FYI,
    <post#=333168>post 333168</post#> has some code to change all the formulas containing errors to the "form" suggested by Andrew.

    Steve

Posting Permissions

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