EOMONTH not recognized in nested formula

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

Board Regular
Joined
Apr 18, 2002
Messages
61
Ian,

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

RF
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092

ADVERTISEMENT

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

Roccofan

Board Regular
Joined
Apr 18, 2002
Messages
61
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
1,743

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Apr 18, 2002
Messages
61
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
 

Forum statistics

Threads
1,143,615
Messages
5,719,725
Members
422,242
Latest member
hishamkhatri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top