averaging a column

I have a column with different values
>.02
<4
<.05
4
>1
<1

How can I total & average these? 

Also how can I enter a >4 value in a column & not have it reconized as
math formula?

Thanks,
Derrell


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

0
11/4/2003 12:59:07 AM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
464 Views

Similar Articles

[PageSpeed] 21

Derrel
    Maybe I'm just missing something here.  You can average and sum only
numerical values.  You can't average inequalities.  It doesn't make sense
    If you want to enter >4 in a cell, precede it with an apostrophe.   HTH
Otto
"Derrell Teat" <Derrell.Teat.wch60@excelforum-nospam.com> wrote in message
news:Derrell.Teat.wch60@excelforum-nospam.com...
>
> I have a column with different values
> >.02
> <4
> <.05
> 4
> >1
> <1
>
> How can I total & average these?
>
> Also how can I enter a >4 value in a column & not have it reconized as
> math formula?
>
> Thanks,
> Derrell
>
>
> ------------------------------------------------
> ~~ Message posted from http://www.ExcelTip.com/
> ~~View and post usenet messages directly from http://www.ExcelForum.com/
>


0
ottomhello (92)
11/4/2003 2:37:57 AM
In Excel 2000 and Excel XP (and I suspect all other versions) with 
default Options settings, if you enter >4 it is interpreted as text, not 
a formula.  However, when you want text and Excel is not cooperating, 
prefix with a single quote.

If the data that you are trying to total and average is constrained to 
be nonnegative, then a lower bound for the total is
   =SUM(A1:A5)
and
   =SUM(IF(LEFT(A1:A5,1)="<",VALUE(RIGHT(A1:A5,LEN(A1:A5)-1)),A1:A5))
array entered (Ctrl-Shift-Enter) is an upper bound for the total.

Divide each by COUNTA(A1:A5) to get bounds for the average.

Your data looks highly unusual for this kind of analysis, since your 
data claims that 0.02 was quantitated exactly, while in one instance 4 
was not.

Jerry

Derrell Teat wrote:

> I have a column with different values
> 
>>.02
>>
> <4
> <.05
> 4
> 
>>1
>>
> <1
> 
> How can I total & average these? 
> 
> Also how can I enter a >4 value in a column & not have it reconized as
> math formula?
> 
> Thanks,
> Derrell

0
post_a_reply (1395)
11/4/2003 1:08:45 PM
Reply:

Similar Artilces:

Column To Rows
I often get lists of contacts that are generated in one long column. They are often may be 4 or 5 rows per record. In order to be able to use the data I need to transpose the rows in each record (one column) to new columns. It may include Company Name in Row A1 and contact name in Row B1 and perhaps Phone or Web Site in C1 etc. The records are always the same amount of rows. I have tried copy, transpose, and text to rows and anything else I can think of. Can anyone make a suggestion ?? Thanks in advance Tony Pass If desired, send your file to my address below. I will on...

Fill bar/column charts with patterns in Excel 2007?
Is there anyway that I can fill a bar/column chart with patterns as I can do with Excel 2003? I know that MicroSoft removed this feature in Excel 2007. But I don't understand why they do it. Lots of academic journals prefers black and white charts than shaded or charts with gradient. Also, it's hard to tell one series of data from another series if they are printed in black and white. Any help is appreciated. Excel 2007 doesn't use these patterns anymore. Did you really find them useful in Excel 2003? They printed at such a fine resolution that different patters were ind...

Averaging numbers but ignoring < and
I have a column of data as illustrated below than contains numbers, blank cells, dashes and < entries. Is it possible to average only the numbers ensuring that the divsor is the number of cells that contain number entreis rather than all cells containing an entry (eg for below example answer should be 0.137) 0.3 <0.001 0.01 <0.01 0.1 Thank you On Wed, 23 Feb 2005 06:29:03 -0800, KIM <KIM@discussions.microsoft.com> wrote: >I have a column of data as illustrated below than contains numbers, blank >cells, dashes and < entries. Is it possible to average only ...

Insert Columns Diabled!!!!
Howdy All, Can somebody help me with this. On any workbook that I open the "Insert-Columns" menu item is disabled. This also effects the right click context menu as well. I have even opened excel with the standard workbook with 3 worksheets in it and its still disabled. Thanks dbl As I pressed the post button I realized that I had not included what version of Excel. This is Excel 2003. Thanks dbl DBLWizard wrote: > Howdy All, > > Can somebody help me with this. On any workbook that I open the > "Insert-Columns" menu item is disabled. This also eff...

Average of Averages
Another seemingly simple process which I can't figure. :) I have a list of averages, and I want to get the average of them. As such, A 1 25% 2 42% 3 100% 4 50% I need a formula to average something like the above. Thanks, Alan =AVERAGE(A1:A4) is one possible solution. Hope it helps. -- goober ------------------------------------------------------------------------ goober's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=19838 View this thread: http://www.excelforum.com/showthread.php?threadid=513937 Hi Alan It really depen...

averaging less than values
How do I average a column of numbers where less than values are calculated as that number? 2 <2 3 4 <2 5 In the average I want the<2 to = 2. So the answer should be 3 -- JD Assuming source data as posted in A2:A7 Paste this in say, B2, then press CTRL+SHIFT+ENTER to array-enter the formula: =AVERAGE(SUBSTITUTE(A2:A7,"<","")+0) Adapt the ranges to suit -- Max Singapore http://savefile.com/projects/236895 Downloads:17,500 Files:358 Subscribers:55 xdemechanik --- "JD" wrote: > How do I average ...

line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has the capability been added to Access 2007? Access does support a series displayed as a bars and another series as line. -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "John" <John@discussions.microsoft.com> wrote in message news:A0DD25F6-D99A-44C3-812B-416776BED8AF@microsoft.com... > Access 2003 does not support combined Line/Column Charts, but Excel does. > Has > the capability been added to Access 2007? ...

column headings in numbers vs letters
For some reason all my excel files now display columns as numbers rather than letters. How can I change back to letters? Why are all my old files also now reading in numbered columns? This is a setting of Excel, not of your files. Tools>Options>General, uncheck R1C1 Reference style -- Kind Regards, Niek Otten Microsoft MVP - Excel "garyflood" <garyflood@discussions.microsoft.com> wrote in message news:6870D172-C11D-4FF9-A7D8-48BE89149DF8@microsoft.com... > For some reason all my excel files now display columns as numbers rather > than > letters. How...

Adding a column...very bizarre behavior
I have an MPP file given to me by one of my users. When she tries to add a column to the Gantt Chart view, we get a little screen flicker, then several new columns appear on the view. I don't know what to tell her. It doesn't do this on any other view, and it only does this in this particular MPP. If I open another project file, it doesn't behave this way. Is it possible that this file has just been corrupted somehow? This is a multi-part message in MIME format. ------=_NextPart_000_002F_01CAE16B.B28F3380 Content-Type: text/plain; charset="Windows-1252...

Averages
Hello again, I have an interesting situation. I basically inherited an older database that is used to enter dates, usage and charges for accounts. The situation is that we are attempting to do averages filtered by account, and averaged against the number of days that data has been entered. So, for example, we had 7 months of data entered, our query should total the number of days in the seven months (x), then average that with the total usage number (y). This data currently resides in the same table labeled "data" in the form of from and to dates (6/1/1999 to 7/1/1999) and usage (32...

Sum of column #2
Hi I have a worksheet containing 25 columns In the column A I have categories and the other columns B to Z represent periods Looks like this PLB 1 2 3 1 2 2 2 3 .................. ADD 3 4 2 4 1 5 6 4 .................. AVL 2 1 4 2 3 7 1 4 ................... PLB 2 3 2 1 5 2 4 3 ..................... I am looking for a formula that will add each columns for the same Categorie So the result for the categorie PLB should be 1+2+3+1+2+2+2+3 (first row) + 2+3+2+1+5+2+4+3 (last row) = total = 38 ADD = 29 AVL = 24 thanks for helping Try this: =SUMPRODUCT((A2:...

Average Lead Time
Does anybody know if the average lead time in Item Vendor Maintenance counts weekends, or only business days? The average lead time is calcualted as follows: Enter the average number of days that pass between the time you place an order with this vendor and the time you receive the order. If you're using Purchase Order Processing, this field will be updated each time a shipment or shipment/invoice is posted in Purchase Order Processing. The average lead time is calculated using the following formula: [(Number of Receipts) * (Average Lead Time) + (Received Date - Ordered Date)] / (...

How to automatically fill a column?
How can I express this? Assume 2 columns, A and B. In A, a col of numbers: 1, 2, 3, etc. In B, now empty, I need to have a col of words that describe in words what the numbers stand for: apples (for No 1), oranges (for No2), lemons (for No 3), etc. I already have the col with the numbers, Column A. I need a way to tell Excel to fill in col B with the word "apple" wherever it sees a "1" in col A. And to fill in col B with the word "oranges" whereever it sees the number "2" in col A. etc, etc. Is this do-able? Best Regards, Arthur Think VLOOKUP wou...

Average Function
Hi group, I have a formula in a cell that takes the average Sales of the 12 rows (for Jan to Dec) like so: =Average(A1:A12)The problem is that for example, since therea are no sales figures from August to Dec, the average is underestimated because it averages it over the entire range of cells (though they are zero from Augus to Dec). How can I make this Average function to be smarter and only average those months that the sales is not zero (August to Dec is Not BLANK but its zero) Hi Average is a very simple thing: SUM/COUNT. So =SUM(A1:A12)/12 should do for all years with 12 mont...

How to end a column, type a paragraph/title, then start back?
I am typing a document that has titles for each section, but the section themselves are in 2 columns. I can't figure out how to make the column "end" so I can type the new title (lengthy) and then restart. I set the column back to 1 and that works while typing the title. However, when I restart columns, it puts my title in the first column (even though there are hard returns between). Clear as mud? Any suggestions? You need a continuous section break at each point where you want to change the number of columns. This is covered in the "Multicolumn sections ...

Combining 2 columns to 1
Hi I have 2 columns A and B. In column A i have one word like "hello". In column B i have a time "19:00" even though the column is pure standard. What i would like to do is combine these two columns to one column, so that i have column C "hello 19:00". I try to do this using the formula =A1&" "&B1 in column C, but when i do that i get the result "hello 0,791666666666667" which is not what i want. How can i do this? Best regards Jimmy Hi Jimmy Try =A1&" "&TEXT(B1,"hh:mm") -- Regards Roger Govier &...

averages
Hi All Trying to work out the best way to get an average answer based on a set of answers The answer will be 1 - 10 across a number of questions e.g B C D E F Answer Q1 Q2 Q3 Q4 1 1 1 3 3 2 1 0 1 2 3 0 1 3 3 4 1 0 1 1 5 1 1 6 7 6 2 2 5 2 7 2 3 3 4 8 2 1 7 6 9 1 2 1 3 10 2 2 8 7 Total 13 13 38 38 Average ? ? ? ? What is the best way to get the average response to each question. i've tried =SUMPRODUCT($B$2:$B$11,C2:C11)/SUM(C2:C11) but not sure this is right....

find formula that will look up a value in a specific column and .
I am looking for a formula that will find a certain text value (open) in a column (J) and pull that row (or row #) to a new sheet. Does anyone know if there is a way to do that? Ksgirl Providing the data starts in J1 the formula below will return the row number. Bear in mind that a formula can only return a value, not move data. You may also be looking for a VLOOKUP, it was just the way your question was phrased that made me return a row number =MATCH("Open",Sheet1!J:J,0) -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS ...

Adding columns #4
I have a spreadsheet with values in column E an I want to add them to column F with the first f2=e2.So as I add a value to column E the value in F changes to sum all with the blank cell showing 0. ie Column E2 Column F2 $1000 $1000 $500 $1500 $0 Okay, so in F3, type: =if(isblank(E3),"",F2+E3) ******************* ~Anne Troy www.OfficeArticles.com "bbc1" <bbc1@discussions.microsoft.com> wrote in message news:9664CDDF-CC23-400A-AFCE-A8BA2974297C@microsoft.com... > I have a spreadsheet with values in c...

Maximum number of column
I am working on a program that would import 400 columns of data into Microsoft Excel. The data is a text file in comma/tab delimited format. Excel has an allowable range of at most 256 columns. I was wondering how would I goes about importing this text file into excel. I'm using visual basic to progam the code. How would I split the file up so that I would be able to import it into Excel or how would I go about overcoming this limitation, beside having to utilize a text editor and row by row shorten the number of columns. The typical text file is about 2000 rows of data. I appreciat...

Sum on top of stacked columns #2
Is it possible in some way to add the sum of series on top of each column in a stacked column diagram? I am often using this feature in ThinkCell for my powerpoint slides. However, need to insert a stacked column graph from Excel 2007 into Word 2007. Currently, I've been using a text box with a reference. However, the reference in a text box is static, which makes it a source for error when inserting or deleting rows in Excel. Hello Drews. You can add a new series with the total sum and then: Change the chart type to line ONLY for this series. Format the series without lines or markers...

Newbee
I am working with a sheet that has several columns that contain "True" or "False" data in the cells. I have been able to change all "True" to "Yes" and "False" to space, wherever they exist in the sheet. I now want to change any "Yes" values, within a column to a space. The column is "G" and has "CardReq" in the first cell position. I use the following code to make the current changes and would like to add the new code to this routine. Can anyone assist?? ' setup the filename and open it... ...

Converting Weekly Data into Monthly Averages
Could someone please show me a formula to average weekly data into monthly. I have a spreadhseet: Date Data 2/1/99 1.15 9/1/99 2.42 16/1/99 1.24 through to..... 25/2/05 3.54 How can i calculate the average of every month between 1999 and 2005? In other words, i need something which looks up all the weeks in a particular month (ie Jan 2001) and averages them out into a table: 1999 2000 ......... 2005 Jan 2.45 6.42 Feb 3.64 etc etc. I was thinking vlookup, but don't know how to average in a vlookup. I would ...

tagging rows to columns???
I just created an excel catalog for my cd jukebox as I loaded the tray (300 discs). Now I want to alphabetize column A (artist) and have column B (album title) follow column A. Is there a way to do this? Thanks for all help. beo Try on a duplicate copy of your sheet: Assuming your lists are in cols A and B, data from row2 down, viz.: Artist Title ABC Text1 XYZ Text2 DEF Text3 etc Select cols A and B Click Data > Sort Check "Header row" under "My list has" Ensure settings under "Sort by" are: Artist > Ascending Click OK For the sample data ab...

column list
can someone help? i need to export a list to a word- wrapped with commas text document My spreadsheet has 500 such numbers: Column A 4567 45676 23434 442344 desired outpu: 4567,45676,23434,442344 Thanks Hi, Jose! Try this - you'll need to select the cells in your worksheet first, and obviously subsitute an appropriate path and filename in the save as line Hope this helps Pete Sub ListToTest() Dim ListCounter As Integer Dim ConCatString As String ConCatString = "" For ListCounter = 1 To Selection.Cells.Count ConCatString = ConCatString & CStr(...