Extracting averages

Hi,

can anybody help me on this problem: I have a column with a LOT of numbers
and I want to know the average of each three (in for instance A1-A3, A4-A6,
A7-A9 and so on) and put these average values in a new column (say, B), but
this new column should not contain any blanc fields, ie. I want the length
of new column (B) to be only one-third of the length of the original column
(the A-column)...

Thank you
/Mette


0
mdjo01 (3)
8/19/2003 7:38:06 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
370 Views

Similar Articles

[PageSpeed] 14

Mette

You need to use the OFFSET function.  If you send me your 
email address to nicholasward@kpmg.com.au, I will send you 
an example spreadsheet.

Cheers
Nick.


>-----Original Message-----
>Hi,
>
>can anybody help me on this problem: I have a column with 
a LOT of numbers
>and I want to know the average of each three (in for 
instance A1-A3, A4-A6,
>A7-A9 and so on) and put these average values in a new 
column (say, B), but
>this new column should not contain any blanc fields, ie. 
I want the length
>of new column (B) to be only one-third of the length of 
the original column
>(the A-column)...
>
>Thank you
>/Mette
>
>
>.
>
0
8/19/2003 7:57:00 AM
Reply:

Similar Artilces:

Average value only for the filtered area..
Dear, I have data in the range of cells A2:E1000. The row 1 has the headers to which i had given filters. The column E has a numeric value for which an average value is required at G1 for only those cells in column E which reflect by a filter in column C. For example, after selecting a particular name using the filter in column C, it resulted in 10 numerical values, the formula should calculate average value of only those 10 values and reflect in G1. Please help. Hi, Try this in G1 =SUBTOTAL(101,C2:C100) -- Mike When competing hypotheses are otherwise eq...

How to add a vertical bar to a column chart to display AVERAGE val
I have a chart that summarizes survey responses: - Y axis is the percent of total respondents - X axis is the Likert scale responses, "Strongly Disagree (1)", thru "Strongly Agree (7)". I want to add a vertical bar that shows the average response (which the chart would take from the spreadsheet). For example, if the average was "5.5", I want the bar to be vertical, and for it to cross the X axis where 5.5 would fall, between the "Somewhat Agree (5)" and "Agree (6)" bars, as if the X axis were truly a numerical plotting (as oppose...

Averaging unique values
Hi all, I have rows of data being recorded for different dates. The number of data points for each date changes (normally 1-4). Since I don't know in advance how many data points will be recorded for each date I can't set-up an average for each day. Is there a way to find and list unique dates and then get an average of data for each unique date? (i.e. get an average of the 4 points for 12/01/10, 2 points fo 13th etc) Col 2 col 3 col 4 col 5 col 6 col 7 col 8 Row 7 12/01/10 12/01/10 12/01/10 12/01/10 13/...

Extract Hyperlink #2
How do I display the hyperlink address instead of the text information. Is there a function that would allow me to extract the hyperlink and put in cell? Hi see your other post -- Regards Frank Kabel Frankfurt, Germany Carlos wrote: > How do I display the hyperlink address instead of the text > information. Is there a function that would allow me to > extract the hyperlink and put in cell? ...

SchemaSet.Compile()
I have a very complex XSD with lots of imports and schemaRefs. <longshot>I'm cheating and want to be able use the XSD.exe tool to simplify serialization soooooooo was wondering if there's a possibility of compiling the schema into a single logical set and then writing it out as a single XSD.</longshot> any pointers graciously accepted. Allen Allen wrote: > I have a very complex XSD with lots of imports and schemaRefs. > > <longshot>I'm cheating and want to be able use the XSD.exe tool to simplify > serialization soooooooo was wondering if ...

Extraction of Active Directory account property
Is it possible to run a script against AD on our mail server , to extract info from all AD accounts, to see who has OWA enabled, and who doesn't, because I don't want to do this manually, and maybe have the output spit out to a text file, in the form of: Lastname, Firstname - OWA enabled Lastname, Firstname - OWA enabled Lastname, Firstname - OWA not enabled etc, or similar format? Assuming you are using ADUC from the Windows 2003 adminpak, then this custom search filter might work for you. (objectcategory=user)(ProtocolSettings=*http�1�1�*) "DefenderD90" <...

Average first n numbers in a range (there may be less than n numbe
I want average the first six numbers in a series of rows. The series may include blanks, zeros, alpha charachters. There may not be six numbers in the series If there are less than six I still want the average , if there are more than six, I just want to average the first six. I have tried UDF methods and VB code, but can't get the right result. Any sane suggestions? Thnaks -- Mike A. M. Assuming the six rows you want to average are in columns A-F, this formula works: =SUM(A2:F2)/SUMPRODUCT(--(ISNUMBER(A2:F2))) This adds columns A thru F (blanks and alphas are ignored), then counts and...

Average HH:MM:SS #2
Thanx for the response....! In this example I have a series of 12 times. They are formatte HH:MM:SS hour, minute, second. Thus, the first value is 0 hours, minutes & 44 seconds. I need to find what the average time is fo these 12 times. ie. I need to sum these 12 times and divide by 12 o just use the average function. I can do averages easily with ra numbers. However, when I have data in time format (HH:MM:SS) th result of sum or average for the row of 12 times gives me div/0. I a wondering if there is another function that I don't know about or if need to convert the data...

Averaging multiple ranges through multiselect listbox
I have a spreadsheet with a variable number of data columns. The nam of the column imported into the spreadsheet is located at the top o the column data. A list box was then created that imported the colum title as the name in the list box. How do I associate the colum titles in a multiselect list box with the data in their columns? Title 1 Title 2 Title 3 AVG 2 3 2 5 4 3 I want to be able to have a list box containing title 1, 2 and 3. then want to be able to select multiple ranges by multiselecting Title in the listbox such as Title 1 and ...

Average Sale
Can anyone give me a custom report saying how many sales/customers we have had the last week/ month/ year and what the average sale would be? I do need to take sales out from 2 work stations/registers and in house charges. (formula is: total sales minus registers 101, 107 and in house charges = X amount divided by transactions = Average Transaction $) Hope this makes sense to anyone... I can't find anything in Customer Source, and to pluck from 5 different reports just doesn't sound like the right way to get this information. Thanks!! I did find a custom report in Customer Source, b...

desperately need help with average
This is what i have & works but need to do more AO6:AO66 = Names from week 1 =IF(AW6>0,AW6,'Week 1'!S6) AQ6:AQ66 = week 1 hours & adjusted hours =IF(AO6>"",E6+AZ6,"") AR6:AR66 =L means laidoff=IF(AY6>0,AY6,"") AS6:AS66 = Average hours {=IF(OR(AR6="L",AW6>""),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")} AT6:AT66 = total hours of this week =IF(AR6="L","",AU6) AU6:AU66 = Total hours of week 1 & this week=IF(AR6="L",AS6,IF(B6="","",AQ6+SUM(F6:Q6)...

Exporting extraction operator from IMPLEMENT_SERIAL
Hello, Can anyone tell me how to export the extraction operator (operator>>) used for serialization. I tried making the following macro based on MS's IMPLEMENT_SERIAL macro, but it doesn't like the __declspec(dllexport). I get a warning C4158: storage-class or type specifier(s) unexpected here; ignored. #define IMPLEMENT_SERIAL_EXP(class_name, base_class_name, wSchema) \ CObject* PASCAL class_name::CreateObject() \ { return new class_name; } \ _IMPLEMENT_RUNTIMECLASS(class_name, base_class_name, wSchema, \ class_name::CreateObject) \ AFX_CLASSINIT _init_##class_name(RUNTI...

Extracting a character from a string of characters
I would like to extract the 6th character (the 5) from the following example of a string of characters e.g.B123456789. Have a look in HELP index for MID -- Don Guillett SalesAid Software donaldb@281.com "Sue" <Sue@discussions.microsoft.com> wrote in message news:73FB02F4-EDA0-4819-9280-3F74F4163B4E@microsoft.com... > I would like to extract the 6th character (the 5) from the following example > of a string of characters e.g.B123456789. > > If its always the 6th character use MID i.e. if B123456789 was in cell A1 =MID(a1,6,1) -- ______________________...

Average For Filtered Cells
I use the auto-filter feature. Is there a formula I can use that will compute the average of the values in a column for just the cells that are displayed after using the auto filter ? Thank you in advance. Look in the help index for SUBTOTAL -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "carl" <carl@discussions.microsoft.com> wrote in message news:FC1FDF1F-5BD1-4145-A024-47A5C867F160@microsoft.com... >I use the auto-filter feature. > > Is there a formula I can use that will compute the average of the values > in ...

Extract phone number front block of text
I have a webpage that lists business names, address, phone number, etc. There is no definite pattern to how they entered the text. What I' like to do is something like.... -Search cell A1 for "-", return the 3 characters to the left of th "-".- In the next column do... -Search cell A1 for "-", return the 4 characters to the right of th "-".- Then I can concatenate the two and add in the area code. TIA guys/gals. PS. I tried searching but didn't know exactly what to search for an didn't get very far -- Tec ------------------------...

Can delimited text file imports be extracted to a single column i.
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? JROD, It sounds as if the record delimiter isn't what Excel expects. What do you see when you open the file with a program like NotePad? -- Earl Kiosterud mvpearl omitthisword at verizon period net ------------------------------------------- "JR...

I Need Help with Average Formula I Need Assistance!!!
This is what i am trying to use but get Value or False errors & also tried as an array =IF(AR6="L",OR(AW6>""),AVERAGE(IF(AU$6:AU$66>=0,AU$6:AU$66),AU6,"")) This is what i need if AR6=L or AW6>"" then have to Average AU6:AU66 IF AR6="" OR AW6="" I need to use value in AU6 then if cells are blank use "" Mike, I'm not 100% sure of what you are asking for, but I will point out corrections that I see: 1) your OR argument is out of order. Try something like this: =IF(OR(AR6="L",AW6...

How item cost are computed when selecting Weighted Average cost update method
When item on-hand quantity goes positive, it's all right with the (Beginning inventory cost + Current purchase cost) / (Beginning inventory units + Current purchase units) formula. But I am just curious when on-hand quantity goes negative or zero, will RMS still computed item cost with the formula above? Assume that we have in stock 3 hammers that cost $10.00 each, then we sale 6 hammers at POS, and we then order 6 more hammers that cost $12.00 each. Any ideas? and we then order 3 more hammers that cost $12.00 each. NOT 6. "Kaibin" <kaibin@hotmail.com> wrote in me...

How do I average wind directions using excel?
I have a data set of wind directions in degrees, rounded to the nearest 10 degrees. I am attempting to take a average of that data set. Any help would be greatly appreciated. Hi. That's a good question because the average of 350� and 10� is due north, and not the average (180�) I "Think" that one way is to take the average of the East-West, and North-South components. With given Degrees in Column A names "Rng", see if this Array Formula will work... =DEGREES(ATAN2(AVERAGE(COS(RADIANS(Rng))),AVERAGE(SIN(RADIANS(Rng))))) If the answer is negative, then add 360....

slecting averages from a changing table
i have a spread sheet set up to colect data on a daily basis from Jan 1 to dec 31. i want to be able to have a month by month average of slect coloms show up off to the side for quick access. colum a converts the date from colum C in to the numerical value ( jan = 1 ect), Colum B show what week of the year the date falls in to (jan 1 = week 1 of the year),colum C has the dates in it, data is input in colums D to M. so i want to be able to take the data in coulm H and M and be able to average everything that has a jan date in to cell N4, feb in to cell N5 ect..can it be done? ...

Using MATCH and INDEX to extract unique values
Some time ago, someone on this list posted a array function using MATCH and INDEX to extract unique values. I used the formula to great effect, but now I can't find the spreadsheet I used it in, and I need to recreate the formula. I also haven't been able to turn it up using google search. Can anyone here point me to the correct formula again? Thanks. You can download an example here, it's the third formula http://nwexcelsolutions.com/Download/Sort_alphabetical_and_then_extract_unique_records.xls Regards, Peo Sjoblom "jg70124" wrote: > Some time ago, so...

Advanced Excel Averaging
Hello all. I'm in desperate need of help with the following problem. We are creating a workbook template that will be used to record "scores" for a process self-assessment we will be conducting at various locations. Each workbook will contain a worksheet for each department to record their scores. The workbook will also contain a worksheet that averages the scores from the various worksheet. A workbook can contain any number of worksheet, herein lies the problem. I need to figure out to use VB (or something) to average the scores on the worksheet together...and do do it without ...

calculating average time
Hi, I have a query in which I am calculating time between 2 procedures with this formula: Time1: [Procedure1]/60 & Format([Procedure1] Mod 60," ") I am trying to create a report with all the entries. At the bottom of my report, in the page footer, I would like to be able to have the sum of all the times, averages, min, max...etc. Each time I use the formula =Avg([Time1]) (or a similoar one for the others), I always get no value and just an ERROR where the info is supposed to be... Any suggestions? Thanks in advance. Ka As soon as you use Format, you've conve...

Average formula #6
What formula do I use to calculate a weekly average as the monthly tota changes? Example: july total value divided by 28weeks, august value divided b 32 weeks, sept value divided by 36 weeks and so on In other words, a weekly average as each month ends and the value i entered I hope someone understands this and can help thanks so much lesli -- onyx481 ----------------------------------------------------------------------- onyx4813's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2630 View this thread: http://www.excelforum.com/showthread.php?threadid=47137 As...

Averaging Hourly Data
I have a lot of weather data for one month that is broken down by the minute. My end goal is to take the average hourly value of the column values (temp, humidity, etc.) and create one hourly averaged data point for each column. So, I am having trouble averaging integer values that occur multiple times within the same day and hour. Would like to apply this query to the entire table to average all integers for each day and hour. There are typically about 25 data points per hour (this number varies), for 31 days in August. I need to have one average hourly data point for every ...