• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

Excel 2003 EOMONTH problem

I have a workbook that uses the EOMONTH function from the analysis tool pack.
The ATP add-in  is enabled.
There is a cell with the following formula:
=AVERAGE(OFFSET(VOLUME!$A$1,MATCH(BALANCING!D$3,VOLUME!$B:$B,0)-1,MATCH(BALANCING!$A4,VOLUME!$7:$7,0)-1,DAY(EOMONTH(BALANCING!D$3,0)),1))
The formula calculates correctly

If i click in the formula edit box and hit enter(not changing the formula at all), the formula changes to the following:
=AVERAGE(OFFSET(VOLUME!$A$1,MATCH(BALANCING!D$3,VOLUME!$B:$B,0)-1,MATCH(BALANCING!$A4,VOLUME!$7:$7,0)-1,DAY(eomontH(BALANCING!D$3,0)),1))
This formula returns #NAME
As you can see, the formula EOMONTH changes to eomontH

I tried entering a EOMONTH formula in a blank cell that references a date and it gets changed to eomontH and returns #NAME

If I don't edit the existing formulas that contain the EOMONTH function, they calculate correctly.

The workbook does not contain any VBA code.
There are no named ranges that are named eomontH in the workbook.

The function works correctly in a new blank workbook.
0
irc200
Asked:
irc200
  • 5
  • 3
  • 3
2 Solutions
 
redmondbCommented:
irc200,

Intriguing. Any chance you could post a copy of the file - redacted if necessary?

Thanks,
Brian.
0
 
irc200Author Commented:
Brian,

Unfortunately no as it is large file and contains confidential information throughout.

Ian
0
 
redmondbCommented:
Ian,

OK, I understand.

Have you tried it on another PC?

Regards,
Brian.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
redmondbCommented:
... and turned off all add-ins (except ATP)?

If that does nothing, try rebuilding the file from a new workbook in case it has some weird corruption.
0
 
barry houdiniCommented:
EOMONTH in Excel 2003 is an "Analysis ToolPak" add-in function - occasionally I have seen it misbehave like this. Try de-activating ToolPak then re-activating

Tools > add-ins > untick "Analysis ToolPak"

.....then repeat in reverse

If that doesn't work you could consider replacing EOMONTH with other non-addin functions. you can get the equivalent of

DAY(EOMONTH(BALANCING!D$3,0))

with

32-DAY(BALANCING!D$3-DAY(BALANCING!D$3)+32)

regards, barry

0
 
barry houdiniCommented:
...sorry, you knew it was ATP....but my comments still stand...

regards, barry
0
 
irc200Author Commented:
Thanks Barry,

I already tried that.  The EOMONTH function works if the cell is not edited.  Once it is edited then the function name in the formula changes from EOMONTH to eomontH and the cell contains #NAME?.   I opened the workbook in Excel 2010 in compatibility mode and the formula works properly as EOMONTH is a built in function in Excel 2010.   I then saved the worksheet in Excel 2003 format.  I re-opened this same saved worksheet in Excel 2003 and the problem still exists.

The following actions also failed to resolve the problem:
- Opening the workbook on a different PC
- Inserting a new worksheet and deleting all of the existing worksheets in the workbook.  
- Copying all of the worksheets to a new workbook.
- Copying and pasting cell contents to a new workbook

Office 2010 is not a solution as the person that first experienced the problem is not scheduled for a Win 7 / Office 2010 replacement machine in the near future.  It is not our company's policy to install Office 2010 on Win XP.
0
 
redmondbCommented:
... and disabling add-ins?
0
 
barry houdiniCommented:
Sorry, I don't really know what the problem might be specifically- did you consider changing the formula as I suggested?

reagrds, barry
0
 
irc200Author Commented:
Brian, Barry

Disabling the add-ins except ATP did not work.

The formula change suggested by Barry will work.  

I think there must also be some weird corruption in the workbook and it will have to be rebuilt from a new workbook as suggested by Brian.

Thanks for your help
Ian
0
 
redmondbCommented:
Thanks, Ian. Sorry I wasn't more help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 5
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now