Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    NAME Error (2003)

    I've tried and I've tried to figure out what's wrong with my formula, but I can't seem to figure it out. Any help would be much appreciated! Thanks beforehand...

    =IF(A1<0,IF(ISODD(A1*100),ROUND(A1/2,2)+0.01,ROUND(A1/2,2)),IF(ISODD(A1*100),ROUND(A1/2,2)-0.01,ROUND(A1/2,2)))

    It's saying it contains unrecognized text. Any clue...

  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: NAME Error (2003)

    Could it be because with ISODD():

    "If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in."
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NAME Error (2003)

    Thank you, thank you, thank you. I'm new to the Excel world, previous web developer turned analyst. Do you know if it would be possible to do this formula a different way to avoid having to install the Analysis ToolPak? Thanks beforehand for any feedback!

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

    Re: NAME Error (2003)

    Test this to see that it returns the same results:

    =IF(A1<0,IF(MOD(A1*100,2),ROUND(A1/2,2)+0.01,ROUND(A1/2,2)),IF(MOD(A1*100,2),ROUND(A1/2,2)-0.01,ROUND(A1/2,2)))
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NAME Error (2003)

    Does anybody know why,if i type "=isodd" (no brackets - I was playing and hit the enter key too soon) in a cell, I get the result "-154468285"? another addin using this as a defined name perhaps (I do have hyperion essbase installed, which does do some odd things on occasion,) or some other explanation?

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

    Re: NAME Error (2003)

    John: I believe that your formula fails for some values in A1 because of rounding errors. For example, if I set A1=1.10 your formula shows it as odd. I think that the following works a bit more reliably:

    [code]
    =IF(A1<0,IF(NOT((INT((A1*100)/2)*2)=(A1*100)),ROUND(A1/2,2)+0.01,ROUND(A1/2,2)),IF(NOT((INT((A1*100)/2)*2)=(A1*100)),ROUND(A1/2,2)-0.01,ROUND(A1/2,2)))
    [/pre]
    Legare Coleman

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

    Re: NAME Error (2003)

    Download my Name Manager to find out what is going on behind the scenes with names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NAME Error (2003)

    You could turn off all add-ins temporarily to see what happens.
    You don't happen to have a defined name isodd?

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

    Re: NAME Error (2003)

    If I do that, I get -486801401. The only addin I have installed is the analysis toolpak, and no defined names.
    Legare Coleman

  10. #10
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NAME Error (2003)

    Very impressive. Now I have to figure this thing out Thanks again!

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NAME Error (2003)

    are you suggesting i don't have it permanently running? <img src=/S/grin.gif border=0 alt=grin width=15 height=15> I checked with the version i had, and have just checked with the most recent version on your website - all add-ins disabled, I get no names in the workbook and =isodd(25) returns #value, but =isodd still returns -154468285. curious

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: NAME Error (2003)

    Interesting! But no idea what it signifies. All functions from the Analysis Toolpak (at least, the sample I tried) display this behavior. Perthaps is the internal code number for the function or something like that.

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

    Re: NAME Error (2003)

    You are correct. FWIW

    MOD(INT(A1*100),2)

    returning 0 or 1 as it's FALSE or TRUE equivalent, appears to return the same as

    NOT((INT((A1*100)/2)*2)=(A1*100))
    -John ... I float in liquid gardens
    UTC -7ąDS

  14. #14
    2 Star Lounger
    Join Date
    Mar 2002
    Posts
    164
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: NAME Error (2003)

    True, that even simplifies it even more. Thanks again. My hope was to simplify this down, seemed like an awfully lot of code, and you guys more than helped. Best!

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

    Re: NAME Error (2003)

    Actually, I think both of our formulas could still have a problem if the rounding error is down instead of up. With a little more thought, I thin that this is better:

    <code>
    =MOD(ROUND((A1*100),0),2)
    </code>

    Or, in the original formula:

    <code>
    =IF(A1<0,IF(MOD(ROUND((A1*100),0),2),ROUND(A1/2,2)+0.01,ROUND(A1/2,2)),IF(MOD(ROUND((A1*100),0),2),ROUND(A1/2,2)-0.01,ROUND(A1/2,2)))
    </code>
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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