Email:      Pass:    Pass?
Close Window   
Free Ebook
Get Your Free Excel
Our Top 15 Excel Tutorials
Instant Access!
Subscribe for Free Excel tips & more!


Free Excel Forum

Average Of Text

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Is it possible in XL2003 to get an average based on text values.

For example in each row in a single column I have text values like: Single, Married, Divorced, etc...

Can I get a single result return in text format for the average text data recorded in that column.

A2 = Single
A3 = Married
A4 = Single
A5 = Divorced
A6 = Single

i.e. the total majority of data = Single

View Answers     

Similar Excel Video Tutorials

Helpful Excel Macros

Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Sort Data With Headers in Ascending Order in Excel
- Macro that sorts data that has headers in ascending order in Excel. This macro assumes that you data has headers on it.
Show a Single Comment in Excel - Means the Comment is Always Visible, No Hovering Necessary
- This macro in Excel allows you to display or show a particular comment in Excel. This will make the desired comment vis
Excel Macro that Searches Entire Workbook and Returns All Matches
- This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Count The Number of Words in a Cell or Range of Cells in Excel - With User-Specified Delimiter / Separator - UDF
- UDF to count the number of words in a cell or range with a user-specified delimiter. This means that you can tell the f

Similar Topics

I have created a query using a couple of tables. I need an expression that returns the output from text to a defined numeric value. For example:

Marital Status Marital Desc
S Single

M Married

S Single

D Divorced

S Single

I need the following: S = 1, M = 2, D = 5 and Null to equal -1 etc. I attempted to create the following but it returned no value. Is there away to do this?

IIf(([Table], [Column Name])='S',1)

I have a list of individuals with their status, # withholding and their salary. I want to find their tax bracket based on these 3 criteria using the table below. I tried using a vlookup but I don't know how to apply it to ranges of data such as salaries and having to satisfy multiple conditions. Can someone tell me what is a formula I can use?

For example, in A1=John, B1=married, C1=2, D1=15,000. In E1 I want to show their tax bracket based on the what's in B1, C1, D1. How can I do this?

[Status] [# of Withholding] [Salary range low] [Salary range high] [Tax bracket]
single 1 5,000 10,000 25%

single 1 10,001 15,000 28%
single 2 5,000 11,000 25%
single 2 11,001 16,000 28%
married 1 5,000 12,000 25%
married 1 12,001 17,000 28%
married 2 5,000 13,000 25%
married 2 13,001 18,000 28%


I wonder if somebody can help to make this macro work. This is the code asociated to a form. I imput values in the Text Boxes and the calculated result is displayed in the active cell of my excel sheet. Until this point it works fine. Then I want to add a comment to this cell showing the original values enter for the calculation, this is needed to do our quality control. I think I have some format problem in the red part of the code.

Can somebody help me?

Private Sub CommandButton1_Click()

Dim Reading1 As Single
Dim Reading2 As Single
Dim Reading3 As Single
Dim Average As Single
Dim Reading As Single
Reading1 = TextBox1.Value
Reading2 = TextBox2.Value
Reading3 = TextBox3.Value
Average = (Reading1 + Reading2 + Reading3) / 3

Select Case True
Case OptionButton1.Value

Reading = Average * 50
TextBox4 = Reading
ActiveCell.FormulaR1C1 = Reading
With ActiveCell
.Comment.Visible = False
.Comment.Text Text:="ADAPTOR:Normal" & Chr(10) & (Reading1.value) & Chr(10) & (Reading2.value) & Chr(10) & (Reading3.value)

End With

I got this example from and was having some trouble understanding some stuff. appreciate any help i can get.

my question relates to the sub procedure. The array has been declared with (10). I was wondering how would you make this dynamic. So that instead of stopping at 10 rows, the sub procedure would calculate all the consecutive rows with numbers in it.


Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer

Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Next i

Mean = Sum / UBound(Arr)
End Function

Function StdDev(Arr() As Single)
Dim i As Integer
Dim avg As Single, SumSq As Single

avg = Mean(Arr)
For i = 1 To UBound(Arr)
SumSq = SumSq + (Arr(i) - avg) ^ 2
Next i

StdDev = Sqr(SumSq / (UBound(Arr) - 1))
End Function

The following sub routine reads the data in column one from row 1 to 10 (of Sheet1) into the array, calls both functions by passing the arguements to them, computes the mean (average) and the standard deviation, then returns the values in a message box.

Sub compute()
Dim Arr(10) As Single
Dim Average As Single
Dim Std_Dev As Single

For i = 1 To UBound(Arr)
Arr(i) = Sheets("Sheet1").Cells(i, 1)
Next i

Average = Mean(Arr)
Std_Dev = StdDev(Arr)
MsgBox "Average:" & vbTab & Average & vbCrLf & "StdDev :" & vbTab & Std_Dev
End Sub

My problem is how to write a formula in vb.
I searched everywhere, and nothing
I write a code, but i get overflow error.

Option Explicit
Const Pi = 3.14159265358979
Const B = 2
Const C = 1.5
Const D = 8
Const E = 4

Private Sub chkRezultat_Click()
 Dim X As Single, Y As Single, Z As Single, W As Single
    txtRezultat.Visible = True
  'here i type the formula
   X = B * Val(txtQT.Text)
   Y = B * (Pi) ^ C
   Z = Val(txtsigmax.Text) * Val(txtsigmay.Text) * Val(txtsigmaz.Text)
   W = X * Y
   txtRezultat.Text = Val(W) / Val(Z)

'here is how the formula should look like   
'txtRezultat.Text = ((B * Val(txtQT.Text)) / Val((B * Pi) ^ C)) * Val(txtsigmax.Text) *Val (txtsigmay.Text) * Val(txtsigmaz.Text)
  chkRezultat.Value = False
End Sub

When it gets overflow these turns yellow:

 txtRezultat.Text = Val(W) / Val(Z)

I don't know if this:

Dim X As Single, Y As Single, Z As Single, W As Single

, is written correctly.
Any idea or suggestion?
My real formula looks like this(is one of the tree formula that i have to write)
Cmax (X) = 2*QT/((2*π)^3/2)*σx*σy*σz


Sorry about this but I am a complete novice and was wondering if there is any way of achieving the following:

I have a number of rows, 22 columns long containing text strings. I need to search each column of each row to see if the text string is contained within a text string of another cell.

If any of the text strings within the first two columns match any of the text within the single cell it should return a true result but if the text string also contains any of the text strings from the remaining 20 columns it must return a false result.

An example being:

The single text string is 'CATASTROPHE'
There are 22 columns of smaller text strings (the longest being 6 char) and in the first column is the text string 'CAT'. There is also a text string in the 8th column of 'STROP'.

This would need to return a false result but if none of the remaining columns after 'CAT' matched any of the lead text string the result must be true.

Is there any way of achieving this? Any help would be much appreciated.

If you need any more info or sample data please let me know.

I need to determine federal tax withholding from gross pay.

If gross amount (for S-0 = single no dependents) is
Over But NOT Over Then Subtract And multiply
$0.00 $195.00 $51 10%
$195.00 $645.00 $99 15%
Single-0 $645.00 $1,482.00 $351 20%
$1,482.00 $3,131.00 $447 25%
$3,131.00 $6,763.00 $849 30%
$6,763.00 --

If gross is $400 then what are the total taxes taken out? Could someone please provide a formula?

I tried this one: =IF(M10<=D11,(M10-G11)*I11,IF(M10<=C13,(M10-G12)*I12,IF(M10<=D13,(M10-G13)*I13,IF(M10<=D14,(M10-G14)*I14))))

But obviously, it didn't work.

Any help would be appreciated

Also - I'll have different tables for different Married/Dependents (example - there is an entirely different table for Married -2 (dependents) than there is for Single - 0. If anyone can help me with an IF formula to determine an employee Married/Dependent status combined with another formula to pull the proper table - I'd be forever grateful.

I'm at my wits end. I'm doing this as a favor for my mom who does payroll for my dad's small business. I don't have anywhere else to turn.

I'll even pay someone if I can just get this figured out.

Thanks so much

I need VB code to delete the columns in a spreadsheet for all cases where that column contains a given text value in in a single row within the worksheet.

So, the code would need to highlight ALL columns containing that text in a stated row and then delete those selected columns. Don't know if this needs to be executed a single column at a time or if there is code to do the highlighting in a single step and then delete all of the applicable columns via a single command.

Much appreciated in advance.


I have a spreadsheet and in say column E there is either a comment (if that's easier) or as I currently have text ( I use this to say that we had a system error on a particular day and briefly say what was wrong - i.e system hung - down 20 minutes) usually there is no text in that column as it is rare to get a failure, however I want to be able to read the text in that column and then be able to look at it in a single cell, either by hovering over the new cell or simply into another single cell.

Is this realistic or am I asking too much.

Once again thanks in antisipation.

I honestly do not know what I would do with out this fantastic Forum - well done guys.



Is it possible to apply bullets to a single cell?

I want to use a large amount of text in a single cell but at the moment I am having to use spaces to get the text in the format I require.


Hi, all.
I have a simple question.

I have:
1 Nick L x x x Single
2 John B x x x Married
40 Rob C x x x Single

I want to create a separate TAB ..


I want the "singles" to be on top (automatically with vlookups)

the Married on a different table..

In practice ..

it should say something like find "singles" and included it in my top table ..for "married" include it in my bottom table!..

But to make a list again wouldn t it be like find the first single...add to the list..find the next and add to the next raw.......

Can this be done??

the data are obviously not the actual but the idea is the same (think of products and unique names!)


I have a table of data:
column A: dates
column B: values

I want to know the average value over a certain period
however, for some dates I have more values than for others, which makes that
a classic average would give a distorted picture, very much like a wighted
average, which i don't want

13-Jun-02 8250
22-May-02 8400
16-May-02 8750
15-May-02 8000
12-Apr-02 8000
28-Mar-02 8300
22-Mar-02 8250
22-Mar-02 7750
08-Mar-02 8500
01-Mar-02 8250
26-Feb-02 7500
20-Feb-02 8400
05-Feb-02 6800
05-Feb-02 7000

I understand that the solution might take more than one step as if more that
one value corresponds with 1 single day, first an average should be
calculated? or not?
is there a single formula covering this request?

thanks in advance!

Hello there.

This is my first post here, and after reading through the rules I will try to be accurate and to the point. I am a learner at excel formulae and I am pretty much teaching myself as I go.

What I have been trying to do is find a formulae that will display to a single cell, a value that is determed from a number of certain conditions being met.

Sample data.
Hardware/Timing, 180, 360, 540, 720
Desktop, Good, Ok, Bad, Very Bad
Laptop, Good, Ok, Bad, Very Bad

I already have conditions in place that will hide text values if the condition is not met, so for example if a Desktop's bootup time is greater than 360, and less than or equal to 540, then all other values on that row will remain blank except for the Bad text string.

What I've been trying to achieve is if the Hardware is equal Laptop or Desktop, search that row for the only text visible and display that text to a single cell. This very well may not be possible but I had to ask the question as I can't think of the logic to achieve this and display the result to a single cell.

Many thanks

Les Butler

Hello. I wrote the code below to sandwich data in a cell between single quotes. I was forced to used two single quotes in the first concanate portion because as you know excel treats one single quote at the beginning of a text field to be non-formula. Do you see any issues with my code adding two single quotes at the beginning? It seems to work, but I don't want to be surprised one day when it might not. Thank you.
In case it is hard to read, the string part of my code is double quote, single quote, single quote, double quote, &cell.Value&, double quote, single quote, double quote.

Sub AddSingleQuote()
Dim n As String
Dim cell As Range
For Each cell In Selection
cell.Value = "''" & cell.Value & "'"
End Sub

I have dumped two Outlook Contact lists to Excel to compare them. They are nearly the same, and I am using formulas to determine where there are differences. However, one dump produced an Excel file with text prefixed with a single quote as in

'Estate lawyer

As you may know, this single quote is an indicator to Excel that the string following is to be treated as text, and the single quote is not displayed in the cell. The single quote is only visible in the formula bar. However, when I compare to a record that contains

Estate lawyer

they do not match, even though they are actually the same.

A find & replace on the single quote does not see it. LEFT and MID don't see it as the first character. In VBA, Mid doesn't see it as the first character. Changing formats to text and back to General doesn't get rid of it.

I can F2 and delete it, but there are several thousands records to be compared. If I can't find an Excel answer I can export to a text file then edit it out in a text editor then reimport it, but what a pain.

Any way to fix this in a reasonably bulk fashion?

I have a worksheet which contains a column of mixed text and numbers, some cell entries have prececing zero's. The data has been imported using the import as text method using a single quote to signify text , if i try to remove this single quote anything with a preceding zero loses the zero which i need......HELP PLEASE


Couldnt find anything searching forum for this.

Without using VB is there a single cell solution to this?

Column of cells contain values in this format;

100, 100, 95, 96

as text.

There can be any number of commas and values. Is there a way to find the average of the numbers in each cell? (Above example would return 97.75.)

Also what search words should I have used before just typing in new question?

Thanks in advance.

Hi all

I'm trying to find a way to split a single column of data across multiple sheets.

The situation is I have 60 sheets in a workbook all in identical format, with different data.
On a single workbook I would use "text to columns" function and then delimited based on splitting using spacing.
How do I do this across 60 sheets as I would with a single sheet?

Hello all,

I'm using excel 2002 and I need to do the following:

In a single cell on tab one, I want to show the average of the values in column B (in tab 2) that have the exact text "2.1.1" in column A (also tab 2)

EG, From the following

ColA ColB
2.1.1 50
2.1.1 1
2.1.1 100
2.2.2 95
2.3.3 50

The formula should find all the 2.1.1s in col A and then average the figures in column B next to these (in this case 50, 1 & 100). Please note that new rows will be being added, which is why I need the formula to search for the text in column A.




What could be the easiest way, working with a single string to
- Write a single string to a text file
- Read a single string to a text file in a variable

Any help or link to info appreciated


I have a spreadsheet where one column has an excess of text for each row entry, but I don't want to use text wrap. I'd like to keep the row height consistent throughout the document, limited to one line of text.

Is there a quick/easy way to reveal the excess text that doesn't fit in a cell? Can it be done in a paragraph format with returns, as opposed to a single long horizontal line of text? All this, and when not selected, return to a single height row?

I am trying to combine numeric data in multiple columns to a text string in a single column.

So for example

1 0 4 0 2 (each in a separate column)


10402 (in a single column)

If the cell in the column is empty, I need that to become a space in the text string.


How can I import a whole text field into a single cell in Excel, without the line breaks making Excel start a new row? I'd like the whole text document in a single cell.

I know I could copy and paste but I want to automate it.

Eventually, I would like to write a script to go through a whole directory and import each text file into a different cell of my spreadsheet. But small steps...

Hi all

I have several different text 'strings' that populate column D of my sheet
for example


is it possible to have a formula that will check each cell thru column D and only copy any letters upto the word "TTP"

so the result from above would read


many thanks

I would like to enter two lines of text in a single row (using ALT-ENT), but
I don't want to merge cells. It's ok if the text spills across adjacent
columns. And I don't want the text to wrap. Any ideas?