EOMONTH not recognized in nested formula

[Roccofan]

Hello,

I thought this was just a problem with one workbook, but I just experienced it again. When I have the EOMONTH function as part of another formula I get a #VALUE error message. The formula I'm using is:

=IF(Q3>0,eomonth($X$1,(X3-1)),0)

My first indication that something was wrong was that the function wasn't capitalized in the formula.

Let me know if you have any suggestions.

RF

 

[Ian Mac]

On 2002-10-01 10:45, Roccofan wrote:
Hello,

I thought this was just a problem with one workbook, but I just experienced it again. When I have the EOMONTH function as part of another formula I get a #VALUE error message. The formula I'm using is:

=IF(Q3>0,eomonth($X$1,(X3-1)),0)

My first indication that something was wrong was that the function wasn't capitalized in the formula.

Let me know if you have any suggestions.

RF

EOMONTH is part of the Analysis ToolPak
you need to go to Tools>Add-In's and check the box next to it.

 

[Ian Mac]

On 2002-10-01 10:49, Ian Mac wrote:

On 2002-10-01 10:45, Roccofan wrote:
Hello,

I thought this was just a problem with one workbook, but I just experienced it again. When I have the EOMONTH function as part of another formula I get a #VALUE error message. The formula I'm using is:

=IF(Q3>0,eomonth($X$1,(X3-1)),0)

My first indication that something was wrong was that the function wasn't capitalized in the formula.

Let me know if you have any suggestions.

RF

EOMONTH is part of the Analysis ToolPak
you need to go to Tools>Add-In's and check the box next to it.

Edit: No it isn't, sorry for my hasty-ness!!

Edit: Cripes it's been a long day, yes it is, just when I turned mine off the formula still works???

I'm affraid I can't help you, apart from have you tried copying it to another workbook (blank) to see if there is something wrong with the one your working in?
Just a thought.

_________________
Share the wealth!!
Ian Mac
This message was edited by Ian Mac on 2002-10-01 10:53

 

[Roccofan]

Ian,

Thanks for the reply, but I'm well aware of the Analysis ToolPak. Any other suggestions would be greatly appreciated.

RF

 

[Andrew Poulsom]

You need to install the Analysis ToolPak (Tools, Add-Ins).

 

[Roccofan]

I just closed the workbook and restarted Excel and the formula works. What I did before that was disable a Macro. The macro was to run whenever the sheet was activated. Let me know if I'm going way out here, but I think the macro wasn't giving the formula a chance to run.

What do you think?

RF

 

[IML]

I don't know if this is it, but whenever I open a second instance of excel, the tool pak is not installed. If this is what happened to you, you could replace the tool pak dependand
=EOMONTH(A1,B1)
with the equivilent
=DATE(YEAR(A1),MONTH(A1)+B1+1,0)

 

[Andrew Poulsom]

If I use the EOMONTH function without the Analysis ToolPak the formula returns #VALUE! - strange I would have expected #NAME!. Anyway, if I then install the Add-In the formula still returns #VALUE! even with calculation set to Automatic. Pressing F9 resolves it. So it is a sort of volatility issue.

Perhaps you saved the workbook when it showed #VALUE! and your macro installs the Add-In.

 

[Roccofan]

On 2002-10-01 11:09, Andrew Poulsom wrote:
If I use the EOMONTH function without the Analysis ToolPak the formula returns #VALUE! - strange I would have expected #NAME!. Anyway, if I then install the Add-In the formula still returns #VALUE! even with calculation set to Automatic. Pressing F9 resolves it. So it is a sort of volatility issue.

Perhaps you saved the workbook when it showed #VALUE! and your macro installs the Add-In.

This happened to me also, but the last time F9 wouldn't work. That's when I posted and restarted Excel. Everything appears to be working fine now.

Thank you all for your help.

RF