Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    I use the value outputs of cells to calculate other values such as average or sums. If I add a text to output [ "&"x"] the cells do not allow the numerical values to be used in other formulas. I cannot get a numerical sum, for instance. These outputs are not seen as numerical. Also I cannot control number of numbers after decimal.
    Can these issues be resolved?

    Len

  2. #2
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Any chance of a real example of the problem?
    Andrew

  3. #3
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Sure.

    A1 = 2 A2 = 5, A3 = 16
    A1, A2, A3 has [,&" days"] added to formula to add text "days" to end of value.
    Example: A2 value is "2 days" [[as compared to only "2"]]

    =Sum(A1:A3) does not give me the sum (23) of the numeric values (2,5,16) because of the addition of the text to these outputs. All values with text added are ignored.

    If I average A1 & A2 & A3 and format the value to give me 1 place after the decimal , I should get 7.6, but I get 7.66666666

    Hope this is clear and helps.

    Len

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Len,

    What you need is some custom number formats.

    Type: Custom Number Formats
    into the search box in Excel and it will tell you how or look how the cells in the attached workbook are formatted.

    Using custom number formats your formulas don't need any text in them converting the resulting value to text.

    RG
    Attached Files Attached Files
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    As a follow up to that.

    Along with Custom Number Formats (which is what you wanted),
    you could also use Round with the Average IF your answer must be to 1 DP,
    because formatting only displays to fixed DP but does NOT actually round

    e.g. =ROUND(AVERAGE(Range),1)

    Generates an answer to 1 DP

    There are also functions

    ROUNDUP and ROUNDDOWN
    Andrew

  6. #6
    3 Star Lounger
    Join Date
    Dec 2009
    Posts
    212
    Thanks
    36
    Thanked 0 Times in 0 Posts
    Thanks AKW. Exactly what I needed and I appreciate the options you gave. Good learning experience. And thanks for the sample file.

Posting Permissions

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