NAME Error (2003)

Topic

New Reply

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…

Reply | Quote

Replies

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.”

Reply | Quote

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!

Reply | Quote

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)))

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

are you suggesting i don’t have it permanently running? 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

Reply | Quote

FWIW it appears that disabling the Analysis Toolpack doesn’t immediately disable the =isodd functionality. For me, it still returns 710737927; although =isodd(25) is disabled immediately.

Reply | Quote

Same for me. Apparently the exposed functionality is disabled, but the add-in is not (completely) removed from memory until you quit Excel. I don’t know if this is a bug, or if it is intentional (in case the user decides to reenable the Toolpak during the session).

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

It is the ATP all right. Remove it, restart Excel and you get #NAME!, install the atp, restart Excel and you get the number again.

Reply | Quote

Agreed. I find it curious that different people get different numbers, but I guess it is just that – a curiosity. FWIW, iseven and yield produce the same behaviour, but I doubt I’ll be investigating any further.

Reply | Quote

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:

=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]

Reply | Quote

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

Reply | Quote

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))

Reply | Quote

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!

Reply | Quote

Oops, inadequate testing, that formula may not work correclty on negative numbers, try

=MOD(TRUNC(A1*100),2)

BTW, it’s important to know what kind of numbers you are dealing with – are your numbers never more than two decimal places (ignoring floating point inaccuracies)?

(Meanwhile I see Legare is working on the accuracy issue also, but using ROUND, which may be a better choice.)

Reply | Quote

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:


=MOD(ROUND((A1*100),0),2)


Or, in the original formula:


=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)))


Reply | Quote