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

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

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.


"catts22" wrote:

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

Try increasing the width of the column. HTH Otto

"catts22" wrote in message
...
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

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

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

catts22 wrote:

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

--

Dave Peterson

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.


On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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

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

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.

On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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

--

Dave Peterson

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


On Sun, 07 Mar 2010 13:14:23 -0600, Dave Peterson
wrote:

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

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.

On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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

HI

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

=IF(AND(BY200,BY20-CA200),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$6998DATE(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(BY200,BY20-CA200),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$6998DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20))0,IF(AND(BY200,BY20-CA200),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$6998DATE(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?


"CellShocked" wrote:

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


On Sun, 07 Mar 2010 13:14:23 -0600, Dave Peterson
wrote:

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

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.

On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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
.

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

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



catts22 wrote:

HI

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

=IF(AND(BY200,BY20-CA200),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$6998DATE(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(BY200,BY20-CA200),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$6998DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20))0,IF(AND(BY200,BY20-CA200),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$6998DATE(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?

"CellShocked" wrote:

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


On Sun, 07 Mar 2010 13:14:23 -0600, Dave Peterson
wrote:

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

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.

On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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
.


--

Dave Peterson

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/te...172771033.aspx

http://office.microsoft.com/en-us/te...CT101172771033

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


On Mon, 8 Mar 2010 12:46:01 -0800, catts22
wrote:

HI

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

=IF(AND(BY200,BY20-CA200),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incident s
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998=DATE(2010,1,1))*('Incident s
2010'!$O$20:$O$6998DATE(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(BY200,BY20-CA200),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incident s
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998=DATE(2010,1,1))*('Incident s
2010'!$O$20:$O$6998DATE(2010,2,1)),'Incidents
2010'!$I$20:$I$6998)/(BY20-CA20))0,IF(AND(BY200,BY20-CA200),SUMPRODUCT(('Incidents
2010'!$C$20:$C$6998="good")*('Incidents
2010'!$W$20:$W$6998="TSP_CIM_USAWM")*('Incident s
2010'!$N$20:$N$6998=3)*('Incidents
2010'!$O$20:$O$6998=DATE(2010,1,1))*('Incident s
2010'!$O$20:$O$6998DATE(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?


"CellShocked" wrote:

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


On Sun, 07 Mar 2010 13:14:23 -0600, Dave Peterson
wrote:

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

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.

On Sat, 06 Mar 2010 08:50:49 -0600, Dave Peterson
wrote:

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

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

catts22 wrote:

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
.