Excel has a repeat function called REPT that will repeat a text character any number of times. It’s format is REPT(TEXT, Value) where Value is the number of times that TEXT is repeated. You can use “i”, “o”, “-“, or any other letters/symbols as your text and do a primative bar graph. You can also your numbers to roman numerals if they are less than 4000 by using the ROMAN function. The example table below shows three columns A, B, and C. Row 7 shows the formulae starting with cell A7. The rest of the rows show values if they formulae are copied down.
Here are four basic ways to convert a number in a cell to text.
Method (1) Format Cells
1) Right click cell(s) of interest and click on “Format Cells…”
2) Under the Number tab Choose Text as your Category
Method (2) Text function
1) use the TEXT function. It has the formatting of =TEXT(Value,Format)
For example, =TEXT(42.6,”0.00″) returns 42.60 as a text value. The formatting is the same as that used in the Format Cells dialogue box.
Method (3) Concatenation with &
Using the ampersand (&), you can concatenate text. For example, the formula =”a”&”b” returns ab. If you concatenate any text to a number (including a null text such as “”) it will return a text value. So, typing the formula =””&70.0 returns 70 as a text. Note the formatting isn’t as convenient as using the TEXT function.
Method (4) Text to Columns
Choosing Data -> Text To Columns from the menu will let you convert your numbers to text. When the dialogue opens up just follow the steps in the Wizard:
Step 1) Choose Delimited and click Next.
Step 2) Tab delimited and click Next.
Step 3) Choose Text as your Coumn Data Format and click Finish.
A very common question for Excel is, “How do I average in Excel?” The simplest way to do this is to use the AVERAGE function. In the example to the left, cell A5 has the following formula:
This returns the Average (mathematical mean) of the three numbers in the range from A1 to A3 inclusively. You can check it by computing (2+3+6)/3 = 3.667.
If you are at the bottom of a column of numbers that you want to average just type
in the cell that you want to show the average in. Before hitting the ENTER key, select the range of cells you want to include in the average. The formula will finish filling in the range and last parenthesis for you after you hit Enter.
Typing the formula below will give the same results as the example, but won’t update when you change any other cells:
Of course, if you want other types of mathematical averages other than the mean (which AVERAGE returns), then the MEDIAN and MODE functions are also available. They work similarly to AVERAGE, but return their respective mathematical counterparts of central tendency. Other tips and advanced averaging functions are available from Microsoft’s help website such as these:
- If you want to include logical values and text representations of numbers in a reference as part of the calculation, use the AVERAGEA function.
- If you want to calculate the average of only the values that meet certain criteria, use the AVERAGEIF function or the AVERAGEIFS function.