Results 1 to 4 of 4
  1. #1
    Mike Savides
    Guest

    Problem with sums, average, etc.

    Sometimes we encounter data supplied to us that acts like text, rather than numerical values(see example). For example, a group of 40 values sometimes returns a count of 40, but may return a count of 11 or 3! Doing a "paste special" "values" in place doesn't help, but pasting outside of the problematic spreadsheet seems to solve the problem. Also, if you edit the "bad" cell, it behaves as a number. Changing the format to numerical doesn't correct the problem. Any idea how the data came to behave this way and how to correct the problem in an easier fashion?

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

    Re: Problem with sums, average, etc.

    This normally happens if the value has a space or something else that makes Excel think it is text rather than a number. To fix this problem, select a coulumn of the values and click on "Text to columns" in the Data menu. Then select delimited and click on Finish. It should convert the values to numbers.
    Legare Coleman

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

    Re: Problem with sums, average, etc.

    It seems some of the data is actually text. If you format the cell's horizontal alignment as general instead of center, you will notce some of the numbers align to the left, they are text.

    You can convert the to numbers by the following procedure.

    In an unused cell place 0 (zero), copy it, then select the entire data(excluding formulae) and Paste Special, and select Add. This adds 0 to all the values and in the process converts them to numbers. (You could use 1 instead of zero and multiply)

    Hope that helps

    Andrew C

  4. #4
    Mike Savides
    Guest

    Re: Problem with sums, average, etc.

    Thank you!

Posting Permissions

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