Decimal
 

Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

Assuming the value in cell BC3 is actually 73, you should not be getting an
error. The first thing I might check would be to make sure the worksheet has
been calculated (Press F9) or make sure Automatic Calculations is turned on.

Next, I'd take a closer look at the contents of cell BC3. Is it just a data
value, or is it the results of a formula? If data, try re-typing it. If a
formula, what is the formula?

HTH
Elkar


"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

When I tried it I got 73 in cell BD3, not #VALUE. Check that BC3 does
actually contain a number and is formatted as General.

Hope this helps.

Pete

On Jan 30, 5:06*pm, M.A.Tyler <Great Lakes State wrote:
Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3

*BC * * * * * * BD *
72.6 * *73.2
71.8 * *72.6
73 * * *#VALUE!
72 * * *72
73.8 * *74.6

Decimal
 

Your formula works fine for me, but I'm not sure what, for your data set
at least, makes it different than:

=MOD($BC3,1) + $BC3


In article ,
M.A.Tyler <Great Lakes State wrote:

Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

I set up a test book and could not duplicate the problem. My cell BD3
returns 73, not #VALUE!

I expect that you've got a TEXT number in there instead of a real number.

HTH
Vaya con Dios,
Chuck, CABGx3




"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

Chuck, long time no see!

How you doing?

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
I set up a test book and could not duplicate the problem. My cell BD3
returns 73, not #VALUE!

I expect that you've got a TEXT number in there instead of a real number.

HTH
Vaya con Dios,
Chuck, CABGx3




"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also
below, is
entered in BD3. Is the error because it's an odd number? More importantly
can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

I think that even a TEXT number should return a correct value unless it
has non-space characters in it (e.g., a non-breaking space)...

In article ,
CLR wrote:

I expect that you've got a TEXT number in there instead of a real number.

Decimal
 

Pretty good, thanks for asking Biff........I've been doing a bunch of Excel
programming work and some of the basics were starting to slide away from me.
Quite embarassing to be asked a "simple" question and not remember the
answer........so, I thought maybe a little "tune-up-time" might help.
Hope all's well with you and yours,

Vaya con Dios,
Chuck, CABGx3




"T. Valko" wrote:

Chuck, long time no see!

How you doing?

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
I set up a test book and could not duplicate the problem. My cell BD3
returns 73, not #VALUE!

I expect that you've got a TEXT number in there instead of a real number.

HTH
Vaya con Dios,
Chuck, CABGx3




"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also
below, is
entered in BD3. Is the error because it's an odd number? More importantly
can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

BC3's data is a result of another formula in BB3, =TEXT($BA3,"0\:00.0"), BA3
pulls data from another sheet via hlookup. I did try the same series of
formulas in other cells, on the same worksheet, and recieved the correct
answer, so I'm stumped?

"Elkar" wrote:

Assuming the value in cell BC3 is actually 73, you should not be getting an
error. The first thing I might check would be to make sure the worksheet has
been calculated (Press F9) or make sure Automatic Calculations is turned on.

Next, I'd take a closer look at the contents of cell BC3. Is it just a data
value, or is it the results of a formula? If data, try re-typing it. If a
formula, what is the formula?

HTH
Elkar


"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also below, is
entered in BD3. Is the error because it's an odd number? More importantly can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

Good catch JE, we all thought just TEXT did it .....it don't ....but
something like 73,0 instead of 73.0 will cause the error.......

Vaya con Dios,
Chuck, CABGx3



"JE McGimpsey" wrote:

I think that even a TEXT number should return a correct value unless it
has non-space characters in it (e.g., a non-breaking space)...

In article ,
CLR wrote:

I expect that you've got a TEXT number in there instead of a real number.

Decimal
 

Glad to hear you're ok. We lost one of our regulars recently, Sandy Mann.

I hadn't seen you "around" for a while so I was hoping nothing bad had
happened.

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
Pretty good, thanks for asking Biff........I've been doing a bunch of
Excel
programming work and some of the basics were starting to slide away from
me.
Quite embarassing to be asked a "simple" question and not remember the
answer........so, I thought maybe a little "tune-up-time" might help.
Hope all's well with you and yours,

Vaya con Dios,
Chuck, CABGx3




"T. Valko" wrote:

Chuck, long time no see!

How you doing?

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
I set up a test book and could not duplicate the problem. My cell BD3
returns 73, not #VALUE!

I expect that you've got a TEXT number in there instead of a real
number.

HTH
Vaya con Dios,
Chuck, CABGx3




"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also
below, is
entered in BD3. Is the error because it's an odd number? More
importantly
can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6

Decimal
 

Oh my, I'm sure sorry to hear that about Sandy.........he's helped me a lot
over the years. He'll be sorely missed. Thanks for letting me know.

And thanks for your concern about my absence.......I'm in my ninth year of
my Triple Bypass now, so far so good......

Vaya con Dios,
Chuck, CABGx3





"T. Valko" wrote:

Glad to hear you're ok. We lost one of our regulars recently, Sandy Mann.

I hadn't seen you "around" for a while so I was hoping nothing bad had
happened.

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
Pretty good, thanks for asking Biff........I've been doing a bunch of
Excel
programming work and some of the basics were starting to slide away from
me.
Quite embarassing to be asked a "simple" question and not remember the
answer........so, I thought maybe a little "tune-up-time" might help.
Hope all's well with you and yours,

Vaya con Dios,
Chuck, CABGx3




"T. Valko" wrote:

Chuck, long time no see!

How you doing?

--
Biff
Microsoft Excel MVP


"CLR" wrote in message
...
I set up a test book and could not duplicate the problem. My cell BD3
returns 73, not #VALUE!

I expect that you've got a TEXT number in there instead of a real
number.

HTH
Vaya con Dios,
Chuck, CABGx3




"M.A.Tyler" wrote:

Assume that the rows below are 1-5, and the following formula, also
below, is
entered in BD3. Is the error because it's an odd number? More
importantly
can
someone suggest a fix?

Thanks!

=IF(MOD($BC3,1)=0,0,--RIGHT($BC3,LEN($BC3)-FIND(".",$BC3)))*0.1+$BC3


BC BD
72.6 73.2
71.8 72.6
73 #VALUE!
72 72
73.8 74.6