# EOMONTH not recognized in nested formula

#### Roccofan

##### Board Regular
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

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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.

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

Ian,

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

RF

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

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

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)

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.

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

Replies
2
Views
296
Replies
0
Views
507
Replies
0
Views
286
Replies
3
Views
641
Replies
1
Views
658

1,220,987
Messages
6,157,236
Members
451,407
Latest member
vdaesety

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

### Which adblocker are you using?

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

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