Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    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

  2. #2
    Uranium Lounger
    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

  3. #3
    3 Star Lounger
    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(G23-H23<0,(G23-H23)>-1),TEXT(0,"-#,##0"),G23-H23)

    G23

  4. #4
    WS Lounge VIP sdckapr's Avatar
    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

  5. #5
    Uranium Lounger
    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

  6. #6
    WS Lounge VIP sdckapr's Avatar
    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>

  7. #7
    3 Star Lounger
    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

  8. #8
    WS Lounge VIP sdckapr's Avatar
    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,1-MOD(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

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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

  10. #10
    3 Star Lounger
    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

  11. #11
    3 Star Lounger
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •