Inconsistent #VALUE! error

[Giordano Bruno]

I have a worksheet with a formula in the tab GenData which returns the value 3
(the formula is =MOD(ROUNDDOWN((StartMonth-0.01)/3,0),4)+1).

In other worksheets in the same workbook, I have the formula
=RIGHT(GenData!$A$10,1)

In some of the worksheets this returns the text 3. In others it returns a #VALUE! error.

I can "correct" the error by changing the formula to
=RIGHT(TEXT(GenData!$A$10,"general"),1)

But why does it fail on only some of the worksheets. Can anyone think of a reason for this behaviour.

 

[Robert Mika]

Have you check if all your worksheets are formated in the same way?

 

[Giordano Bruno]

Hi Robert,

No, they are not all formatted the same way, but I just copy and paste the formula using Ctrl-C and Ctrl-V so I am not sure what difference the formatting will make.

 

[Robert Mika]

I do not see your data but I would consider to look at the same cell in diffrent worksheets.
Assuming the the "3" in this formula :
MOD(ROUNDDOWN((StartMonth-0.01)/3,0),4)+1 is returned from the RIGHT function you may found out that once you have "3"(TEXT) and another time 3(VALUE).
As you can not do calcualtion on a character you may get this #VALUE error.

 

[JackDanIce]

If you're trying to return a numerical value from a text input, you could use this formula:

=RIGHT(GenData!$A$10,1) + 0

Which will force the output to be numerical and may solve your error problem

 

[Giordano Bruno]

Hi Robert,

The original 3 is a value (ISNUMBER on the result returns true). I have further refined the test and find that the formula =RIGHT(1,1) returns "1" on some worksheets and #VALUE! on others. Where it returns "1" that is text, not a value.

Jack,

Thanks for your contribution. I have in fact worked out a number of workarounds although yours had not occurred to me and I am grateful to have learned something from that.

My real concern is to understand what is happening on the spreadsheet which is Excel 2003 sp3.