Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number too large for excel
Hi, I have a large number of worksheets that all individually work
with time, doing different things. I then link these to a master sheet, which has 17 columns, each colum has many workbook time totals averaged out. The strange thing is that 15 of the 17 colums work flawlessly and 2 don't. There is no difference in the way the 2 non- working colums are formatted, linked, data originally input, or anything else. They are for all intents and purposes identical except of course for the actual values i.e. the times are different. The 2 non-working colums, give me the standard error message which indicates that the number is very large or negative, the actual time (when I total them up by hand on a calculator) is no larger or smaller than any of the other numbers. I've spent 5 full days working back through thousands of columns of data to see if somehow some gremlin got in, and there was an incorect format, but everything is in the correct excel time format h:mm:ss So, where can I look, I'm sick of seeing these ############ instead of a time. Naturally any help will establish the helper as a living legend and I'll make an urban myth out of you :-) Thanks in advance. Ron |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number too large for excel
On Wed, 19 Aug 2009 04:01:18 -0700 (PDT), Ron Ziemiecki
wrote: Hi, I have a large number of worksheets that all individually work with time, doing different things. I then link these to a master sheet, which has 17 columns, each colum has many workbook time totals averaged out. The strange thing is that 15 of the 17 colums work flawlessly and 2 don't. There is no difference in the way the 2 non- working colums are formatted, linked, data originally input, or anything else. They are for all intents and purposes identical except of course for the actual values i.e. the times are different. The 2 non-working colums, give me the standard error message which indicates that the number is very large or negative, the actual time (when I total them up by hand on a calculator) is no larger or smaller than any of the other numbers. I've spent 5 full days working back through thousands of columns of data to see if somehow some gremlin got in, and there was an incorect format, but everything is in the correct excel time format h:mm:ss So, where can I look, I'm sick of seeing these ############ instead of a time. Naturally any help will establish the helper as a living legend and I'll make an urban myth out of you :-) Thanks in advance. Ron If I am correctly interpreting your message to mean that the "standard error message" to which you allude is a sequence of "###" in the box with the result, the problem may be that your formula is resulting in negative time values. If that is the case, some options a 1. Change to the 1904 date system (the 1900 date system does not allow negative times). However, this will result in offsetting any dates stored in this worksheet by 4 years and a day. And also, you would have to ensure that your users are using the same date system. 2. Format the cell to NOT show negative values: Format/Cells/Number/Custom Type: [h]:mm; (note the semicolon at the end) You can still use the value in calculations; it just won't show. 3. Change your formula to force the time value to be positive. This will mess up the use of this cell in calculations. You could prepend this display by a "-" for negative values: =IF(SIGN(B6-A6)=-1,"-", "+")&TEXT(ABS(B6-A6),"[h]:mm") --ron |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Number too large for excel
When you get ##### in a cell it is because the cell is too small to display
the entire entry. Try widening the cell, or go to the cell properties and select SHIRNK TO FIT. "Ron Ziemiecki" wrote: Hi, I have a large number of worksheets that all individually work with time, doing different things. I then link these to a master sheet, which has 17 columns, each colum has many workbook time totals averaged out. The strange thing is that 15 of the 17 colums work flawlessly and 2 don't. There is no difference in the way the 2 non- working colums are formatted, linked, data originally input, or anything else. They are for all intents and purposes identical except of course for the actual values i.e. the times are different. The 2 non-working colums, give me the standard error message which indicates that the number is very large or negative, the actual time (when I total them up by hand on a calculator) is no larger or smaller than any of the other numbers. I've spent 5 full days working back through thousands of columns of data to see if somehow some gremlin got in, and there was an incorect format, but everything is in the correct excel time format h:mm:ss So, where can I look, I'm sick of seeing these ############ instead of a time. Naturally any help will establish the helper as a living legend and I'll make an urban myth out of you :-) Thanks in advance. Ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do you auto-number a large list of rows in Excel? | Excel Discussion (Misc queries) | |||
How to Search For a URL in a Large Number of Excel Files? | Links and Linking in Excel | |||
Easy Reading - large number minus small number | New Users to Excel | |||
Displaying large number of pictures in EXCEL | Excel Discussion (Misc queries) | |||
How can I remove a large page number from each page in Excel | Excel Discussion (Misc queries) |