Number too large for excel

[Ron Ziemiecki]

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

 

[Ron Rosenfeld]

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 are:

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

 

[Squeaky]

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.