1. ## 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. ## 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."

3. ## 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. ## 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)))

5. ## 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. ## 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]

7. ## Re: NAME Error (2003)

Download my Name Manager to find out what is going on behind the scenes with names.

8. ## 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. ## 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.

10. ## Re: NAME Error (2003)

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

11. ## 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. ## 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. ## 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))

14. ## 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. ## 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>

Page 1 of 2 12 Last

#### Posting Permissions

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