If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#3
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#4
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#5
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#6
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#7
|
|||
|
|||
Negative result gives #### is there a way to show the actual - num
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 |
#8
|
|||
|
|||
Negative result gives #### is there a way to show the actual -
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 . |
#9
|
|||
|
|||
Negative result gives #### is there a way to show the actual -
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 |
#10
|
|||
|
|||
Negative result gives #### is there a way to show the actual -
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 . |
|
Thread Tools | |
Display Modes | |
|
|