Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    =value function (2000 SR1)

    I have a spreadsheet that I've used for years that uses the =value(xx). When cell xx (formatted as text) contains 3/4, the number 0.75 is returned, change to 1/2 & 0.5 is returned, etc. In attempting to use this same procedure in an new spreadsheet, the number 31319 is returned. Can anyone tell me what's going on & how to fix it?
    Thanks, Scott

  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: =value function (2000 SR1)

    Could the formual be pinting to a cell that contains a Date. 31319 when formatted as a date = Sept 29 1985.

    If the cell pointed to does not contain a date, what exactly does it contain ?

    Andrew C.

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    No, the cell is formatted as "General".
    If it helps, I think that the original spreadsheet was created in Excel97.
    BTW, the cell returns 37319, not 31319 as I stated earlier.
    Scott

  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: =value function (2000 SR1)

    It is even more interesting if t returns 37319, which is March 4 2002, which could display as 3/4 depending on the formatting. Could you say what value is in the cell XX, where =VALUE(XX) returns 37319.

    Andrew C

  5. #5
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    Yes, xx contains 3/4.
    My first assumption was that somehow I had globally formatted the template (or whatever you call the way a new spreadsheet opens) to read things entered with slashes as dates, but I can't find anything that indicates that that is the case, nor can I find anyway to change it.
    Scott

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

    Re: =value function (2000 SR1)

    I'm afraid that you will probably have to re-enter your data, but before doing so format the cell as Fraction (quarters), then entering 3/4 should return 0.75. I suspect that the existing 3/4 is actually text, and is being interpreted by excel as March 4 of the current year. If you enter =VALUE("3/4") in a cell do you get the same result ?

    Andrew C

  7. #7
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    Yes, if I enter =value("3/4"), it returns 37319. The question is, can I get back to the old way of doing things? The original spreadsheet works just fine, even if I make modifications to it. If I make a copy of the original, or if I start from scratch, it won't work. Very frustrating.
    Scott

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

    Re: =value function (2000 SR1)

    If you enter 3/4 into a cell in XL2K that is formatted General, it will be interpreted as march the 4th of the current year 2002. That would store 37319 into the cell. Using the value function on a text cell containing 3/4 will do the same conversion.

    What do you want to happen? If you are trying to get .75 as a result, then you can use a formula like this one if the 3/4 is in cell C1:

    <pre>=LEFT(C1,FIND("/",C1)-1)/RIGHT(C1,LEN(C1)-FIND("/",C1))
    </pre>

    Legare Coleman

  9. #9
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    I understand, but how can you get it to stop returning a date? I've tried resetting the entire spreadsheet to text format and then reformatting the cell with the =value function to various other formats besides General, but it always returns 37319! The original spreadsheet, written in Excel97 I think, returns 0.75, i.e. the numeric value. Do you know of any way to stop Excel2K from returning dates?
    Scott

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

    Re: =value function (2000 SR1)

    As far as I know, there is no way to change the way XL decides what the value is.
    Legare Coleman

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: =value function (2000 SR1)

    >but how can you get it to stop returning a date?
    <img src=/w3timages/blackline.gif width=33% height=2>

    Andrew's solution of preformatting the entry area as Fraction works for me.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    After getting John's reply, I reread your post - I had overlooked the recommendation to format the cell "Fraction". Works just like it should now!! THANK YOU.
    Scott

  13. #13
    New Lounger
    Join Date
    Jan 2002
    Location
    AL, USA
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: =value function (2000 SR1)

    Thank you John. I reread Andrew's reply, I had missed the part about formatting the cell to Fraction. Works just like I need it to now.
    Scott <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

Posting Permissions

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