Email:      Pass:    Pass?

E-mail:

# Average Of Text

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

## Similar Excel Tutorials

Understanding Formulas and Functions in Excel
In this tutorial I will cover the basic concepts of Formulas and Functions in Excel. A formula is an expression whi ...
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...
Naming Cells in Excel to Make Using Formulas/Functions Easier
In this tutorial I am going to introduce the idea of Named Cells. A Named Cell is a cell which you have given a cus ...
Capitalize First Letter of Every Word in a Cell - PROPER Function
In Excel you can use a function to capitalize the first letter of every word in a cell.  This allows you to prepare ...

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%

Hello,

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 Average As Single

Select Case True
Case OptionButton1.Value

With ActiveCell
.Comment.Visible = False

End With

The title might not be descriptive enough but it is hard to explain what I am looking for!

I am creating Excel worksheets to create Selenium (FF plugin to automate web page testing) scripts that will automatically create a number of scenario's with random data. Everything has been fine but I am not trying to let the user select various factors to be included or excluded.

Say for example, there was a field called 'Marital Status' and the potential entries would be...
Single
Married
Divorced
Widowed
Living Together

I can list these on a separate sheet (raw_data) in cells A1:A5 and use the following to randomly assign one to each customer

=INDEX(raw_data!\$A\$1:\$A\$5,RANDBETWEEN(1,COUNTA(raw_data!\$A\$1:\$A\$5)))

This works fine but there are times when our tests need to be more specific, for example only creating scenario's where the customer was on their own. I would need to somehow use only 'Single', 'Divorced' and 'Widowed' from the above list. This is a fake scenario and the real list is around 20 items long and would need everything from one of the items to all of the items chosen.

I thought I had cracked it for using checkboxes for each item. I then used
{=INDEX(General_Data!\$D\$17:\$D\$27,SMALL(IF(General_Data!\$D\$17:\$D\$27<>"",ROW(General_Data!\$D\$17:\$D\$27)-ROW(General_Data!\$D\$17)+1),ROWS(D\$30:D31)),COLUMNS(\$D31:D31))}
and copied this down the same number of rows as there are options.

Checking and unchecking the boxes worked correctly, to an extent. All of the data for the checked boxes showed up at the top of the range but the unchecked ones showed at the bottom as #NUM

CHECKBOX NAME CHECKED? LIST Single Yes Single Married No Divorced Divorced Yes Widowed Widowed Yes #NUM Living Together No #NUM

Assuming the first entry of this new list is at D30, I have used =IF(ISERROR(D30),"",D30) in an adjacent cell and copied that down. That gives me the same list but with blank cells instead of #NUM.

On my output sheet, I then have =INDEX(General_Data!\$E\$30:\$E\$40,RANDBETWEEN(1,COUNTA(General_Data!\$E\$30:\$E\$40))) in each row. This is, however, including the blanks in the range so my 'Marital Status' scenario's show as
Widowed | Single Single | Divorced Widowed

What I need is every row to have a random one of the checked list. I know that this would probably be easy to do using VBA but as we use different versions of Excel, I have already found that simple macro's do not necessarily work from one machine to the next.

If anyone can offer any suggestions I would be extremely greatful. I am more than willing to *** up what I have so far and start again if someone can give a better way of doing it!

Steve

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

```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:
Code:

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

I don't know if this:
Code:

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

, is written correctly.
Any idea or suggestion?
Thanks.
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

Hello,

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.

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.

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

Hello, I need help analyzing an extremely large body of information. To understand my problem, imagine a single column of values with 9 rows. I wish to create a 1 column, 3 row table below this table that averages every 3 values. For example, in my single column of 9 values, the first value I want to calculate is the average of the first three rows (1-3). Then my next value under that needs to be an average of rows 4-6, etc. I understand how to create the =average formula, and I understand how to apply this formula through numerous columns by dragging the lower right column. However, I do not see how I can apply this formula to the next row to average the next three values. I need a quick way to apply a single function to thousands of data values. Please let me know if you cannot understand my issue and I can reword.

Hi,

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.

Cheers.

Hi!

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

eg.
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?

tom

Hi All,

I am new to this forum and not that keep in Excel :-( So forgive me if I'm not using the correct syntax...

I have a sheet containing results of an online survey.
Several columns contain identical data that needs to be "manipulated".

Let's take one column: in a single cell you can have:
- empty
- single text like 3mm
- multi text comma separated like 3mm, 5mm, 7mm, 9mm, ...<increments of 2mm> ... ,35mm
There are 120 rows FYI.

Per column I need to calculate the total amount of each occurance of 3mm, 5mm, 7mm, ... , 35mm

No clue how to tackle this. Working on Excel 2011 for Mac.

All help and tips are really appreciated.

Thanks!

Hi all,

I have an excel sheet containing text that I require copying and then pasting into a 3rd party application (bit like notepad).

I need to use a button to copy the text so it cannot be edited or changed. I can copy single lines of text and remove the return charter from the end of the text using:

```Please Login or Register  to view this content.
```

However, when it comes to a cell with paragraph spacing, the text gets pasted as a single line and not 3 single lines with a break between EG:

I want to paste this:

I need to use a button to copy the text.

It cannot be edited or changed.

I can copy single lines of text.

But I get this:

I need to use a button to copy the text.It cannot be edited or changed. I can copy single lines of text.

The final obstacle is that I require the cell to be editable and the macro/Active X control to copy the new edit. This is so no experience is required to get the button working again as it will be used across a team that does not use excel.

I can do the copy and paste if I select the text in the cell to clipboard and paste it manually so there must be a way.

Any help gratefully recived

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.

Dim n As String
Dim cell As Range
For Each cell In Selection
cell.Value = "''" & cell.Value & "'"
Next
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?

Hi,

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?

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.

Thanks

Ben

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)

to

10402 (in a single column)

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

Thanks.

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 People,

I am working on a form in VBA and need help text to pop up on mouse move event. I tried to use ControlTip Text but it was to simple for what I need to be done.

I am facing two issues....

1st - When I run the form without the 2nd textbox all the help text is showing at the beggining.
then when I add the second code -
2nd - When I try to run my code now its saying the following 'ambiguous name detected', I am assuming because I have used the Userform in the previous code.

Private Sub Label1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox1.Visible = False
End Sub

Private Sub Usreform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox1.Visible = False
End Sub

Private Sub Label2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox2.Visible = False
End Sub

Private Sub Userform_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
TextBox2.Visible = False
End Sub

John

I have a couple columns in a spreadsheet I am working on that require a text ' (single quote/apostrophe) to be the first character in the cell.

I can enter the single quote and can see it when editing the cell but the single quote is not displayed on the screen?

Any ideas? I did some searches on this site but could find nothing regarding this. I assume Excel is treating it as some sort of control character. I changed the column type to text...no help. If I put a space in front of the quote it does work but I would rather not have this space in the cell.

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%
\$645.00............\$1,482.00..................\$351......................20%
\$1,482.00..........\$3,131.00..................\$447......................25%
\$3,131.00..........\$6,763.00..................\$849.....................30%

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

Hi,

I have a spreadsheet used by several users that enter text updates. This means that the rows can be varied heights. However I want to be able to have all lines as a set height and showing the first line only and then have an option to expand and revert to single line when necessary. e.g. when reviewing workload on a call, be able to expand the update entered and then after review revert back to the single line of text.

Any help would be greatly appreciated.

Cheers
Jason