Negative result gives #### is there a way to show the actual - num

[catts22]

Hi

The result of a formula gives me ##### for a negative number. Is there a
way to get the actual negative number (i.e. for example -9 instead of ###)?

Alternatively maybe show a 0 (zero)? Sending a report to my boss with ####
doesn't look so great. Maybe a minimum number formula?

Thanks

 

[Mike H]

Post the formula and a description of the data the formula is manipulatiing
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Otto Moehrbach]

Try increasing the width of the column. HTH Otto

 

[Dave Peterson]

Try formatting the cell as General -- or reduce the font size.

And make sure the cell is not formatted as a date or time.

 

[CellShocked]

Column width or font size. Period.

You can also then format the cell to show specifically as whole numbers
only, or define the number of digits after the decimal place you wish to
show.

Adjust the WYSIWYG stuff like font choices and sizes, etc. after you
perfect the functional accuracy of the sheet. The printed 'report'
should be toward the end of development cycles.

Always drag the column wider when you confront this 'problem', then
think about what 'needs' to be done to 'solve' it on a finalized sheet.

 

[Dave Peterson]

If the cell is formatted as a time or date and the value is negative and the
workbook isn't using the 1904 base date, then you can see ###'s, too.

It's not always columnwidth or font size.

 

[CellShocked]

That is sad then. Should be blinking then or some other easy attribute
to delineate it.

 

[catts22]

HI

My formula is to give me the average time/hours.. Originally I had :

=IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20)

But because Incidents 2010'!$I$20:$I$6998 may only have a few entries which
total up to "negative hours" because come Incidents were closed over the
weekend, then when I do the average in this formular I get a negative number
of hours or #####

so I tried this: Essentially if the sum is greater than 0 give me the
average hours, otherwise give me 0, however I get "FALSE"

=if(IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20))>0,IF(AND(BY20>0,BY20-CA20>0),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incidents
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998>=DATE(2010,1,1))*('Incidents
2010'!$O$20:$O$6998<DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20),""))

So making the column bigger or changing the format does't work as I trying
to get hours i.e the time spent doing the Incident.

Any ideas?

 

[Dave Peterson]

If you want to see 0 if the formula returns a negative number, then instead of
using:

=if(longformula<0,0,longformula)
you could use:
=max(0,longformula)

 

[CellShocked]

You should use 24 time entry only, and you should never need to use
negative values, nor should you ever achieve a negative result from time
calculations.

There are several really good templates on the Microsoft Office web
site for time sheets and time management.

Breaking up a time or date string into separate "fields" for year,
month, day, time, etc make things a bit more difficult. Working with
actual date and time strings makes the spreadsheet function much better.

You should take a look at a few of these:

http://office.microsoft.com/en-us/templates/CT101172771033.aspx

http://office.microsoft.com/en-us/templates/TC300083091033.aspx?CategoryID=CT101172771033

First one on the list. Tallies time on the job all week each job.

 

[Otto Moehrbach]

Terry
That doesn't happen because you have a negative number. It's because
the column is not wide enough to hold that number. Increase the width of
that column. HTH Otto

 

[Luke M]

Actually, if the cell is formatted to date or time, a negative value will
display hash marks. If changing column width doesn't work, try changing the
cell format to General or number.