Results 1 to 11 of 11

20050112, 18:15 #1
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
show zero as negative when required (Excel97 SR2)
I have a need to present values in fields and to have those values split into integers and decimals. It is in relation to filling in Inland revenue data so the two fields (for each value) do need to be kept separate.
I can deal with the adding up of integers and decimals such that the carry over from the decimals is added to the integer values, but I also have a sum field which basically adds two values and subtracts a third value.
If however, the overall result is negative and the integer portion is zero but it has a decimal value, then I want the zero to appear complete with a negative sign.
Is that making it clear?
e.g. contributions = 5000.00
deductions = 5000.50
result = 0.50
That figures OK when the cell contains the complete value, but when I split it into two cells of 0 and 50, then I want the answer to appear as I 0 I 50 I
Any ideas?
TIA
Alan Cheshire
UK

20050112, 18:31 #2
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: show zero as negative when required (Excel97 SR2)
Is it correct to assume that if the math result is positive:
contributions = 5000.50
deductions = 5000.00
result = 0.50
the result should display a positive zero, as in  0  .50 ?John ... I float in liquid gardens
UTC 7ąDS

20050112, 18:43 #3
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: show zero as negative when required (Excel97 SR2)
Yes that's right John.
Also, forgot to mention that this result field is one of 52 in column list which needs to be summed at bottom.
I think this works but a the G23 and H23 references would need to contain lots more formula make up:
=IF(AND(G23H23<0,(G23H23)>1),TEXT(0,"#,##0"),G23H23)
G23

20050112, 18:47 #4
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: show zero as negative when required (Excel97 SR2)
A number zero can not display a negative sign unless you change the format and there is no way (short of manually doing it or VB coding) to change the format so some zeroes have a format displaying the negative and others not display it.
You could use a formula to display (as text) the values with a negative sign. This would require using a formula using the VALUE function to convert it back if you need to do math with it.
You could use 3 cells, one for the sign, one for the integer and one for the decimal.
I am curious how the math works out adding the decimals and the integers the way you describe if you have a mix of pos and neg numbers. It seems that it would give the wrong numbers unless you combined them first or used the INT and MOD portions and did not "split them" as you describe.
Steve

20050112, 19:16 #5
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: show zero as negative when required (Excel97 SR2)
Hmmn, I can't get conditional custom number formats to do it*, you may need to use something like:
=IF(A1<0,"","")&INT(ABS(A1))
for the integer cell, which returns a text result. Test it thoroughly to see if it works.
Edit; I have now read your response, and the problem with my formula is that you are not going to be able to sum it withouit reconverting the results with the =VALUE() function. See Steve's comments.
* this conditional number format displays the nearest whole digit, so it's not correct for the integer cell if the value is between "0.5" and "0.9":
[<0]""#0;[>=0]#0;General
Any formulaic way to trick this display would make your summing of the totals invalid.John ... I float in liquid gardens
UTC 7ąDS

20050112, 20:03 #6
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: show zero as negative when required (Excel97 SR2)
If you are going to sum them, then both the decimal and the integer part need to be negative or you will get the wrong numbers (see example)
Steve
<table border=1><tr><td align=right valign=bottom>

20050113, 08:44 #7
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: show zero as negative when required (Excel97 SR2)
Thanks guys.
Steve  yep, the decimal part of the value (which becomes an integer when it is finally viewed) also needs to be treated as if it were a negative if the integer portion is negative...but the negative symbol must not show in the field. I am going to try treating each line on its own  including the final summary line and I think that will allow me to treat the decimal part on the final summary line without having to do a SUM() with lots of individual lookups.
John  I tried the formula that I was using (see above) and it appears to be allowing me to sum the lines with this cell treated as a value, but your suggestion for the =IF(A1<0,"","")&INT(ABS(A1)) ignores the value of the cell in a summed column.
Attached worksheet to indicate the formatting from both John and mine own suggestions
Cheers
Alan
Cheshire
UK

20050113, 11:17 #8
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: show zero as negative when required (Excel97 SR2)
If you are going to KEEP the original values (if you are using the formulas you suggest, you will have them)
Why not use for the int part:
<pre>=IF(A2<0,""&INT(A2),INT(A2))</pre>
Decimal part:
<pre>=ROUND(IF(A2<0,1MOD(A2,1),MOD(A2,1))*100,0)</pre>
Then do not sum the individual parts obtained from the above. Sum the total (eg in A7) and convert that number using the same formulas as above.
Steve

20050113, 13:41 #9
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,326
 Thanks
 3
 Thanked 215 Times in 198 Posts
Re: show zero as negative when required (Excel97 S
Hi,
Does the attached work for you? It's not elegant but seems to work OK.
HTH.Regards,
Rory
Microsoft MVP  Excel

20050113, 14:10 #10
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: show zero as negative when required (Excel97 S
Thanks Rory,
Just going to try it in the full app.
It appears to be doing the business as required.
Alan
Cheshire
UK

20050118, 08:57 #11
 Join Date
 Apr 2001
 Location
 Warrington, Cheshire
 Posts
 355
 Thanks
 3
 Thanked 0 Times in 0 Posts
Re: show zero as negative when required (Excel97 S
Thanks to everyone who contributed. The solution eventually was to use Trunc with combination of If statement and concat.
The integer portion became:
<pre>=IF((C17+(D17/100)+(E17+(F17/100))((G17& _
TEXT(H17,"00"))/100))<0,"","") & TRUNC(C17+(D17/100)+(E17+(F17/100))((G17&TEXT(H17,"00"))/100))</pre>
and the decimal portion became:
<pre>=ABS((C17+(D17/100)+(E17+(F17/100))((G17& _
TEXT(H17,"00"))/100))TRUNC(C17+(D17/100)+(E17+(F17/100))((G17&TEXT(H17,"00"))/100)))*100 </pre>
where
C17 is integer with D17 decimal,
E17 is integer with F17 decimal,
G17 is integer with H17 decimal
by concatenating G17 with H17 the need for an If statement to determine the sign of G17 is removed (must format the decimal as "00" prior to concat though).
Thanks for everyones contribution (large or small).
Alan
Cheshire
UK
(snowey at the moment)