adding text to formula voids use in calculations

Topic

New Reply

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

Reply | Quote

Replies

Any chance of a real example of the problem?

Reply | Quote

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

Reply | Quote

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

Thanks AKW. Exactly what I needed and I appreciate the options you gave. Good learning experience. And thanks for the sample file.

Reply | Quote