Insert a column and enter =AVERAGE(B1:IV1) Copy down 50 rows. Gord Dibben MS Excel MVP On Mon, 6 Aug 2007 16:56:01 -0700, wannabe68 <wannabe68@discussions.microsoft.com> wrote: >I am trying to get the average of a row of numbers with various numbers of >columns. I have 50 rows of numbers and am looking for the formula to average >all of the rows, showing the average of each row. I know how to do them >individually, but very time consuming. Any help would be appeciated.

0 |

8/7/2007 12:46:15 AM

Look at the formula that Gord Dibben provided: If your rows go from column A through AX, and start at row 1 and continue on down for 50 rows, then in AY1: =AVERAGE(A1:AX1) and copy down the sheet. If there are numbers all the way from row 1 to row 50 in column AX, you can move the mousepointer to the lower right corner of AY1 until it becomes a small cross (or thick plus sign - depends on how you think of it) and double-click the left mouse button and the formula will fill to the last number automatically. Note that AVERAGE() ignores empty cells. In other words, if you have the value 5 in A1, C1 and G1 and all other cells between them are empty, =AVERAGE(A1:G1) will give you 5 (15/3) But if you need to include the total possible count, then you could use =SUM(A1:G1)/columns(A:G) which will give you 2.142857 (15/7) "wannabe68" wrote: > Thanks JBM, that answers that question. Nothing is easy. > If I have a spead sheet with 50 columns and 50 rows, what is the formula > that I should put in the "formula" line to get the average of each row, all > at one click of the ?. Is a formula going to give me what I want? > > "JMB" wrote: > > > The gray area in the bottom right of your spreadsheet. If your NumLock is > > on, the status bar will say "NUM". If you don't see it, verify it is > > activated by clicking View (there s/b a check next to status bar - click it > > to activate it if there is no check). > > > > But you will still need to select each row individually to get the average. > > The formula will be much faster and will be easier to refer back to. > > > > > > "wannabe68" wrote: > > > > > Gord > > > I was reading another post by George G. He wrote: > > > >> An easy way that doesn't use any formula. > > > | >> Right-click the Status Bar, and put a checkmark next to 'Average'. > > > | >> Highlight the cells you wish to average, the average of these cells > > > | >> will be displayed on the Status Bar! > > > That sounds like the easiest way to get what I want. My problem is, where > > > is the "Status Bar"?? > > > > > > "Gord Dibben" wrote: > > > > > > > Insert a column and enter =AVERAGE(B1:IV1) > > > > > > > > Copy down 50 rows. > > > > > > > > > > > > Gord Dibben MS Excel MVP > > > > > > > > On Mon, 6 Aug 2007 16:56:01 -0700, wannabe68 > > > > <wannabe68@discussions.microsoft.com> wrote: > > > > > > > > >I am trying to get the average of a row of numbers with various numbers of > > > > >columns. I have 50 rows of numbers and am looking for the formula to average > > > > >all of the rows, showing the average of each row. I know how to do them > > > > >individually, but very time consuming. Any help would be appeciated. > > > > > > > >

0 |

8/7/2007 3:48:01 PM

Hi, I have a requirement to extract some data which under normal circumstances would be ease as the formula =VLOOKUP(C2,$R$12:$T$1010,3,0) would return the correct result if R12 contained 301 and T12 contained XYZ i.e. XYZ would be returned. However, I want to return the contents of cell T14 which is always 2 rows below the match in column R. Appreciate any help. Ta, Rob ...

This should be simple, but the words I want to use don't help in HELP All I want to do is make the column headings in the first few rows stay on the screen when I scroll down and make the row headings at the left stay put when I scroll right In ((Lotus 1-2-3)) this was called freeze titles Thanks for your help, temayer Thanks for your reply, but I don't think that is it, because that seems to only work in one direction and there doesn't seem to be a way to tell it,"Don't scroll the top three lines or the first two columns, as appropriate, so I can see what the cell i...

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

I am using an excel spreadsheet as a template for webpages. For each page I insert 5 pictures which I create elsewhere. As each picture is inserted, Excel gives it a number. When you select the graphic you see the number in the Name box. The problem I have is that when the number of Pictures gets to 65536, VBA gives an error message when trying to insert the next picture. The excel file keeps a running total of the pictures inserted over time i.e. it doesn't go back to zero when you close the file. Does anyone know how to set this back to zero? Post your VBA code so we can see ...

The row height is indeed in points but then what measure is the font height????? -- read on... Try this. Put a single 48 point Capital letter in a cell and autofit the row height. Check the measurement (Excel should report back that it's 90(120 pixels)). Now 1 pica point = 1/72 inch, so 90 points x 1/72 = 1.25 inches. Use MS Draw to create a circle 1.25 inches in diameter -- this circle *will* just fit in the row and therefore, the row height is in pica points. But, our 48 point letter should be 48 x 1/72 = 0.67 inches tall. It is not however, it is approx. 0.72 inches high on t...

When I try to enter a negative number into excel, the number turns positive as soon as I enter it. I have tried deleting book1.xlt to remove any bogus formatting but this problem appears to be some sort of default setting that I do not know about. After the number turns positive, what do you see in the formula bar for that cell? If it still shows negative there, then maybe it's just a formatting problem. Format|cells|number tab|make it general. If it really shows positive, then it sounds like you may have a macro that's helping out. Can you reopen your workbook with macros disa...

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

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

I'm trying to import the results from a test that I have run. The results have 500000 data points and excel will only let my import 65536 of them. Is there a way around this or should I be looking for another application to solve this? Thanks, Devan Hi no this is the maximum for one sheet. -- Regards Frank Kabel Frankfurt, Germany devan wrote: > I'm trying to import the results from a test that I have > run. The results have 500000 data points and excel will > only let my import 65536 of them. Is there a way around > this or should I be looking for another app...

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

I have 2003 version of Word at work. I am trying to create an outline numbered list with 3 levels. When I hit the enter button after typing the text for an item, word does not seem to recognize that it should continue the list. The next line just has normal formatting, and I'm forced to use the format painter brush to copy a previous item from the list, and then Word seems to recognize it as part of the list. I have verified the "Apply Automatic Numbered Lists" is checked in the Tools/Autocorrect Options/Autoformat as you type menu. An observation about Word ...

I've just installed Crystal Reports (9.2.2) and connected it to my CRM 1.2 database. I notice that custom fields I've added as Picklist type fields in CRM are appearing as Number type fields in Crystal, and therefore showing a number (eg 3) rather than the expected picklist item (eg 'Mobile'). Any ideas? Picklist values are stored in the StringMap table "Roger Strain" <roger@liquidthought.co.za> wrote in message news:4f36724f.0408191103.588694a5@posting.google.com... > I've just installed Crystal Reports (9.2.2) and connected it to my CRM > 1.2 da...

Using Excel 2000 I have a table to sort, but the top three rows are all header rows. This prevents me from using the Data | Sort option because the "header row" option only eliminates the first row from the sort. All I know to do is select all the rows in the table that need to be sorted and then use the Data | Sort option and check the "no header row" box. This gets cumbersome when there are hundreds of rows in the table to include in the sort and only three rows in the table to exclude from the sort. Any ideas? Bonnie, Add in an empty column into your data and ta...

I am trying to run a pivot table on a file containing 25000 rows. I have 5 duplicate (identical structure) files. The pivot works on each but one. On this file I get an error saying it has too many columns or rows in the pivot table. Drag at least one column or row off the table... Does anybody know what the reason for this message is, are their limits to rows or columns? The identical table works on the other files, therefore this message is so strange. Who has an idea? -- Craig_Richards ------------------------------------------------------------------------ Craig_Richards's Profil...

I have a spreadsheet consisting of 100 rows, with 11 columns in each. Different rows are highlighted via "conditional formatting" according to number ranges in some of the cells. I wish to have the highlighted rows move to the top of the list, that is if conditions are met to highlight a row that row would move to the top of the spreadsheet joining all other highlighted rows. I would want the highlighted rows to be sorted alphabetically, followed by the non-highlighted rows sorted alphabetically. Typically there are approximately 15 to 30 out of 100 rows that are highlighte...

I need to reference the row value of the current cell in a worksheet function: = row() in VBA: activecell.row Hope this helps Rowan excelneophyte wrote: > I need to reference the row value of the current cell ...

Anyway to sort names alphabetically when names may start with numbers? I have a list of name of chemicals. The names may start with numbers, like 1,2-dichlorobutane. Right now, if I sort the list, the names start with numbers would be on the top. How do I make it ignore nubmers and sort alphabetically? > I have a list of name of chemicals. The names may start with > numbers, like 1,2-dichlorobutane. Right now, if I sort the list, > the names start with numbers would be on the top. How do I make it > ignore nubmers and sort alphabetically? ----------- I'm not aware of any ...

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

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

Hi, I have a table (#qed_missing_quarters) with the following rows in it: row_num quarter_end_date 1 2002-09-30 00:00:00.000 2 2002-12-31 00:00:00.000 3 2003-03-31 00:00:00.000 4 2003-06-30 00:00:00.000 5 2003-09-30 00:00:00.000 And I have the following code as a test to traverse across the table: ;WITH qed_missing_values(row_num, quarter_end_date) AS ( SELECT row_num AS [row_num], quarter_end_date AS [quarter_end_date] FROM #qed_missing_quarters WHERE row_num = 1 UNION ALL SELECT a.row_num + 1, a.quarter_end_date FROM #qed_missing_quarters AS a INNER JOIN ...

hi, I need to store a two information in one cell with one value as hidden ,kindly reply if any one know the way without creating hidden rows or columns or worksheet The need is to store two information in one cell,with one value in display ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Using comments is one option. >-----Original Message----- >hi, > >I need to store a two information in one cell with one value as hidden >,kindly reply if any one kn...

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)] / (...

Hi, I have a form for issuing a letter number for our letter book. In this form , I have a text box with auto number . what I need is to issue auto number start with letters. For example: APP&P1, which 1 is the auto number and it is start with “APP&P”. once the user complete the letter subject, letter receive and the sender , will receive a control number or letter number. What about having two fields, where the first field contains the letter prefix and the second field contains the number? You could display the two fields in a calculated control combining the data. "...

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

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