1. 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. Any chance of a real example of the problem?

3. 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. 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

5. 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

6. 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
•