| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How can I convert decimal commas to decimal points? | Excel Discussion (Misc queries) | |||
| Subtracting two 2-decimal place numbers gives result 13-decimal places? | Excel Worksheet Functions | |||
| Batch converting CSV files from comma-decimal to period-decimal | Excel Discussion (Misc queries) | |||
| Converting 2-place decimal value to floating point decimal number with leading zero | Excel Discussion (Misc queries) | |||
| FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
Linear Mode
