Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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 Tutorials

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 curren ...
Average Non-Contiguous Cells in Excel
How to average non-contiguous cells (cells that aren't next to each other) if those cells contain numbers and are ...
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 ...
Linking Cells in Excel
In this tutorial I am going to cover how to link cells together. This is a useful feature of Excel as you can link ...

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%


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 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
.ClearComments
.AddComment
.Comment.Visible = False
.Comment.Text Text:="ADAPTOR:Normal" & Chr(10) & (Reading1.value) & Chr(10) & (Reading2.value) & Chr(10) & (Reading3.value)

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!

Thanks in advance
Steve

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.

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




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

Sub AddSingleQuote()
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 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?


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


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


I am trying to import a text file with 50,000 data entries into a worksheet
but the importing function seems to allow only extraction to a single row
which limits the data extraction to 256 data entries. Is there a way to
import with the data formatted to a single column to allow the use of the 64K
rows available to parse the data?



Hi

I am wanting to take several paragraphs from another document and paste this into a single cell within Excel but no matter what I try, when pasting it wants to spread the paragraphs across multiple cells. I am also wanting to have the Cell that the data is pasted to wrap the text (ie keep the column width).

Any suggestions on how I can get the paste to work within the single cell.

Much appreciated

Redders


I have a form where user can add multiple records first to a text box one by one and then "flush" them into a database after he's done - just to avoid the need to open the form in worst case hundreds of time.

So my form looks basically like this:


It confuses the user way too much if a single "record" is spread into multiple lines. How do force my VBA code to print that text (ie. the content of that "Name") to a single line, while keeping the textbox as multiline?


I'm extracting pupil grade levels from several years with each years lists being in a single sheet.

Now I can create a Pivot table showing what percentage of pupils got what level for each year, but what I would like to do is consolidate all this data into a single Pivot table.

Now I'm only using a single column in the sheet as the levels are text based. When I try to select the multiple sheet option, it comes back and says it needs more than one row for this operation. Er... At this point I'm lost. Is there a way of consolidating a single column of data from multiple sheets into a single Pivot table?


Hello,

I am wanting to import many text files (currently about 150 and growing) into a single excel spreadsheet.

All files contain 4 lines of text in a consistent format.

I want to achieve a single worksheet of 4 columns and 150 (or however many) rows.

Up until now I was considering writing a script to manipulate the files into a single CSV file to import, but have discovered by programming skills have become rusty after 10 years of minimal use!

Using Excel 2010, but have access to 2003 or 2007 if required.

Thanks in advance for your innovative ideas!

Tony


Hi,

I am going to save an Excel worksheet into tab delimited text file for importing into another application.

I notice that contents in some columns contain single quotation mark at the front of each string of text. I understand this is to 'force' the contents to be in text format.

How can I remove this single quotation mark? I do not want them to be in the tab delimited text file.

I am using Excel 2010