Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
I can't seem to get a negative result to display proper, see scenario below:
Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
You are using the ampersand instead of plus symbol - this will join the
values together as if they were strings, and so you can't subtract a string from another one. Try changing the & to +. Hope this helps. Pete Rhonda wrote: I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
Rhonda
To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
The use of the ampersands is fine as long as the data in the referenced cells
are all numbers. If they weren't, an error would be returned. If you're getting ##### as a result, this probably means one of two things. 1. Your column width is not wide enough to display the result. or 2. Your cell containing the formula is formatted as a date. Dates cannot be negative. HTH, Elkar "Pete_UK" wrote: You are using the ampersand instead of plus symbol - this will join the values together as if they were strings, and so you can't subtract a string from another one. Try changing the & to +. Hope this helps. Pete Rhonda wrote: I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
If Pete's hypothesis is correct, you should get #VALUE! error not the #### you
say you get. Perhaps your formula is not as you have posted but is returning negative dates? Or needs to have its column wider to accomodate the results? Gord Dibben MS Excel MVP On 16 Oct 2006 13:10:36 -0700, "Pete_UK" wrote: You are using the ampersand instead of plus symbol - this will join the values together as if they were strings, and so you can't subtract a string from another one. Try changing the & to +. Hope this helps. Pete Rhonda wrote: I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
Thanks so much for the prompt response. The length of the cell is not the
problem with the ##### (first thing I checked). It also highlights in the "yellow info" box - "negative dates and times cannot be displayed" or something like that. When I get back to work tomorrow, I will try changing the & to + and see what happens, will let you know tomorrow. One other thing is that all the other formulas work fine but this cell has the start time less than the actual time which is what I need (the difference - negative) Maybe the date can't be apart of the formula, just the time, I will try to do the above and see what happens. Let me know if you think of anything else. thanks again! Rhonda Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
Otto,
The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
OK, it is now clear what is happening. Concatenation is fine, it
concatenates a date with a time producing a time. You are subtracting a later time from an earlier one. Excel, when it makes an arithmetic operation OFTEN adopts the format of the operands, hence it is trying to display the negative number as date, which produces an infinite series of #####. Question is, what do you want to have in this cell? Something expressed as time? A number of minutes in the form, e.g. 3.25 minutes to mean 3:15? HTH Kostis Vezerides Rhonda wrote: Otto, The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
To get a negative time difference to display, you can use:
Tools/ Options/ Calculation, and select the 1904 date system, but there may still be problems with your formula if you're using the & symbol rather than +. 10/1/06 will be 38727 in the 1900 date system (or 37265 in the 1904 system), and 4:45 PM is 0.697917. If you concatenate them, for example with the & symbol, you'll get 387270.697917 (or 376250.697917), putting the zero which was before the decimal point of the time at the end of the integer for the date. Hence a one day change in the date will appear as a 10 day change in the total. If you have the results formatted as [h]:mm to be able to see the full number of hours, you'll see a 240 hour change, not 24 hours. You must, therefore, replace your & symbols by +. -- David Biddulph "Rhonda" wrote in message ... Otto, The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
Vezerid,
Yes, I want the time to display (hmm), it should be -0.5 minutes. Any suggestions? Rhonda "vezerid" wrote: OK, it is now clear what is happening. Concatenation is fine, it concatenates a date with a time producing a time. You are subtracting a later time from an earlier one. Excel, when it makes an arithmetic operation OFTEN adopts the format of the operands, hence it is trying to display the negative number as date, which produces an infinite series of #####. Question is, what do you want to have in this cell? Something expressed as time? A number of minutes in the form, e.g. 3.25 minutes to mean 3:15? HTH Kostis Vezerides Rhonda wrote: Otto, The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
David,
I will try this and see what happens, thanks for the info. I will let you know if it works. Rhonda "David Biddulph" wrote: To get a negative time difference to display, you can use: Tools/ Options/ Calculation, and select the 1904 date system, but there may still be problems with your formula if you're using the & symbol rather than +. 10/1/06 will be 38727 in the 1900 date system (or 37265 in the 1904 system), and 4:45 PM is 0.697917. If you concatenate them, for example with the & symbol, you'll get 387270.697917 (or 376250.697917), putting the zero which was before the decimal point of the time at the end of the integer for the date. Hence a one day change in the date will appear as a 10 day change in the total. If you have the results formatted as [h]:mm to be able to see the full number of hours, you'll see a 240 hour change, not 24 hours. You must, therefore, replace your & symbols by +. -- David Biddulph "Rhonda" wrote in message ... Otto, The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Display negative results
Rhonda,
change your formula to this: =((H11+I11) - (F11+G11))*24*60 and format the cell as Custom set to 0" minutes"< (exactly as typed in the angle brackets. This will give you: -5 minutes -65 minutes -125 minutes etc for 5 min, 1hour 5 min, 2 hour 5 min differences etc. This is not quite what you asked for, but then I'm not sure how a format of "hmm" would give you a display of "-0.5 minutes". Hope this helps. Pete Rhonda wrote: Vezerid, Yes, I want the time to display (hmm), it should be -0.5 minutes. Any suggestions? Rhonda "vezerid" wrote: OK, it is now clear what is happening. Concatenation is fine, it concatenates a date with a time producing a time. You are subtracting a later time from an earlier one. Excel, when it makes an arithmetic operation OFTEN adopts the format of the operands, hence it is trying to display the negative number as date, which produces an infinite series of #####. Question is, what do you want to have in this cell? Something expressed as time? A number of minutes in the form, e.g. 3.25 minutes to mean 3:15? HTH Kostis Vezerides Rhonda wrote: Otto, The problem is not the size of the cell. This is what is in the cell: Cell H11 - 10/01/06 Cell I11 - 4:45 PM Cell F11 - 10/01/06 Cell G11 - 4:50 PM So the formula is =(H11&I11)-(F11&G11) The ###### appears in the column rather than a -.05 for the five minutes difference, does this explanation help? I thank you in advance for your prompt response. Rhonda "Otto Moehrbach" wrote: Rhonda To add to Pete's response, #### usually means that the cell has a number that is too large to fit into the cell. That is, the column is not wide enough to hold that number. Increase the width of the cell as needed. HTH Otto "Rhonda" wrote in message ... I can't seem to get a negative result to display proper, see scenario below: Cell AL11 is the final calculation of (H11 & I11) - (F11 & G11). Notice the ### marks in AL11 due to the negative results. I need to keep the existing formula, but somehow display the negative results. HELP - Rhonda! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Negative Results | Excel Discussion (Misc queries) | |||
results display in filter function | Excel Worksheet Functions | |||
How can I display certain results in a color font | Excel Worksheet Functions | |||
problem:search results negative in excel docs with macros | Excel Discussion (Misc queries) | |||
Display negative data points differently. | Charts and Charting in Excel |