Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Decimal palces (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Loungers

    OK here is what I want to do:

    1) Look at a cell value, say it has 8
    2) Change a cell format from "0.000000" to "0.00000000"

    That is based on a cell value, change the cells I need to change to that many decimal places. Oh no VBA please, just worksheet functions or something that does not need any user inteaction.

    TIA

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    I must be misunderstanding you, but can you not format the cell as Number, and set the decimal places to 8 ?

    Andrew C

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Decimal palces (Excel 97 and >)

    Sure Andrew

    But what happens when the cell changes to 6 or 19 or 9? Would I need to adjust the cell manually, or is there an automatic way? <img src=/S/exclamation.gif border=0 alt=exclamation width=15 height=15>

    I can write the VBA code to the worksheet_Change or Calculate event, but that should be the LAST resort.

    I know its not easy, but you'll never know what loungers can come up with <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30>

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    Wassim,

    A worksheet function cannot apply any form of formatting, so if you need the format to be dynamic VBA is only way, most likely in either of teh evnts you suggested.

    When you say what happens when the cell changes to 6 or 19, do you mean the number of decimal places or the value ? What would determine the number of decimal places ?. Would a Text version of the number be acceptable ?. If so I believe something might be achieved with the TEXT function.

    Andrew

  5. #5
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Decimal palces (Excel 97 and >)

    Yes Andrew

    I was afraid that VBA is the only solution.

    I get data from a mainframe DB2 application, and then I need to add a small minute amount, say +0.000(how ever many decimal places the number has)1. This is done by the following formula:

    <font color=blue> =A8+VALUE(CONCATENATE("0.",REPT(0,LEN(A8)-FIND(".",A8,1)),"1")). maybe someone can shorten it, or make it more intuative. </font color=blue>

    But then the cell where that formula is located will "truncate' because its format does make things harder to display. So I need to adjust the format to match the Len(A8) for example.

    Users can't work with them, can't shoot them...

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  6. #6
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Decimal palces (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Legare

    How does Conditional Formatting change the number of decimal places?

    Could you send me an example?!

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    You could use Conditional Formatting, but that is limited to three conditions. If three conditions is not enough, your only choice is going to be VBA.

    BTW, your formula is probably not going to do what you are expecting if A8 does not contain a text string. If A8 contains a number, I think that the LEN is going to return the length of what is currently displayed, which may be different from what is actually in the cell.

    Edited by Legare for a correction.

    LEN looks like it returns the length of what was entered, not what is displayed. So, 0.1234 will have a length of 6 (not 4 like your formula looks like it is expecting). Also, if you don't put a 0 in front of the decimal, Excel will insert one.
    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    Not sure how this would fit in your fomula but you could use Text and a lookup table

    HTH

    Peter
    Attached Files Attached Files

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    Sorry about that. You can change the Font, Border and Pattern, but not the number format in the conditional formatting. The only way I can see to do what you are asking is to use VBA code in the Change Event.
    Legare Coleman

  10. #10
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Decimal palces (Excel 97 and >)

    <P ID="edit" class=small>Edited by Bat17 on 03-Apr-02 03:28.</P>Not sure that this formula is shorter or more intuative <img src=/S/smile.gif border=0 alt=smile width=15 height=15> but it seems to work.

    =TEXT(E8+(1*10^-(LEN(E8)-LEN(INT(E8))-1)),CONCATENATE("0.",REPT(0,(LEN(E8)-LEN(INT(E8))-1))))

    This wont get you up to 19 decimal places though as Excel gives up with about 15 significant numbers. It does format it at the same time though.
    I was not quite sure if you wanted 1.123 to end up as 1.124 or 1.1231 though!

    <font face="Script MT Bold">Edited with an afterthought!</font face=script>

    if you do want the 1.231 version you could just treat it as text:- E8 & 1

    HTH

    Peter

  11. #11
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Decimal palces (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Bat17

    Wow that was neat. <img src=/S/bravo.gif border=0 alt=bravo width=16 height=30> I think I can modify it a bit and get something going.

    You see this is an example of why I subscribe to these lounges, you can't imagine what gems come out of the loungers.

    Thanks

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  12. #12
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Decimal palces (Excel 97 and >)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Bat17

    neat formula, but as you mentioned, I need to add a very minute amount and that would be 1.123 to ending up as 1.1231.

    But again, I can use this one as well.

    Thanks

    Wassim <img src=/S/compute.gif border=0 alt=compute width=40 height=20>
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

Posting Permissions

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