Display negative results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
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
 
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
 
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
 
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,

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
 
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
 
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 +.
 
Vezerid,

Yes, I want the time to display (hmm), it should be -0.5 minutes. Any
suggestions?

Rhonda
 
David,

I will try this and see what happens, thanks for the info. I will let you
know if it works.

Rhonda

David Biddulph said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
Back
Top