Help with cell references, please

TIA

I have an application that logs multiple data points at one minute 
intervals and writes them to a file every 24 hrs. - thus I get a file 
with "n" number of columns and 1440 rows of numbers every day.  The file 
names are of the form 20040113 corresponding to the date.

I now need to go back thru 90 days of these files, single out a 
particular column of interest, take an average of those 1440 numbers and 
populate another worksheet with those averages so I can chart 90 days of 
them.

I think I need to use a for-next loop to increment both the date and the 
averages-worksheet cell reference but I'm having trouble coming up with 
the proper format for the references. (Since I only need 90 days, it's 
probably easier to do for a one month period at a time rather than 
dealing with incrementing the date format through the months.)

I suppose I could just go back and do it all manually but I'd rather 
learn the proper way.

Hopefully this makes sense and thanks again for the help you all offer.



0
steve111 (1)
1/13/2004 11:35:28 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
342 Views

Similar Articles

[PageSpeed] 4

in a new worksheet in whatever cell you want;

=AVERAGE([20040113.xls]Sheet1!$A$1:$A$1440)

Copy down 89 rows and then change the 20040113 to 
20040112, 20040111..

Then you can average your average (in row 91)

I'm sure there is an easier way to do all this.


ozzie


>-----Original Message-----
>TIA
>
>I have an application that logs multiple data points at 
one minute 
>intervals and writes them to a file every 24 hrs. - thus 
I get a file 
>with "n" number of columns and 1440 rows of numbers every 
day.  The file 
>names are of the form 20040113 corresponding to the date.
>
>I now need to go back thru 90 days of these files, single 
out a 
>particular column of interest, take an average of those 
1440 numbers and 
>populate another worksheet with those averages so I can 
chart 90 days of 
>them.
>
>I think I need to use a for-next loop to increment both 
the date and the 
>averages-worksheet cell reference but I'm having trouble 
coming up with 
>the proper format for the references. (Since I only need 
90 days, it's 
>probably easier to do for a one month period at a time 
rather than 
>dealing with incrementing the date format through the 
months.)
>
>I suppose I could just go back and do it all manually but 
I'd rather 
>learn the proper way.
>
>Hopefully this makes sense and thanks again for the help 
you all offer.
>
>
>
>.
>
0
1/14/2004 12:10:49 AM
Reply:

Similar Artilces:

"unable to display all the cells due to low memory"
I have a co-worker getting this error whenever I try to open my Tasks folder. He has a lot of tasks (1000+), but they are all very small with no attachments. Running Outlook 2000 on Exchange 2000. Thanks! -- Remove 'spam' from email address to contact me directly ...

how do i automatically archive old cell contents to a separate .
I have a worksheet that contains lists of "action items", that is used to keep track of jobs that need to be done. I want to be able to automatically move the contents of a row of cells to another worksheet once the action item has been completed. In other words, once I have changed the value in a cell to "yes" (the action has been completed), the information is archived in a separate worksheet. Is this possible, and how? I'd keep them in place, but use Data|Filter|autofilter to show/hide what I want. I think it makes life much more simple--especially when you...

instantaneous cell value
This is probably incredibly simple but I've been going in circles with it for some time. I'm looking for a way to grab and hold the instantaneous value of a cell whose contents are constantly being updated. Any thoughts? You can "fix" the values of a cell by <copy>, then select another cell (maybe in another sheet) and Edit | Paste Special | Values | OK. This would leave the formula in the original cell to continue to update itself. Hope this helps. Pete thanks for the reply. this works as a "manual" solution but how can I assign only the contents o...

Issue with cell ranges and formulas
I've got an interesting issue that I'd like some help with. I'm doing a performance analysis where I'm trying to average some numbers. Here's what I did to create the worksheet: In cell A1 enter 'Time', in B1 enter 'CPU #0', in C1 enter 'CPU #1', in D1 enter 'CPU #2', in E1 enter 'CPU #3', and in F1 enter 'Average'. I then format the cells in column A to use the 'time' category and '1:30 PM' type. Now I enter in cell A2 '8:30 PM' and A3 with '8:35 PM'. Next enter '17.368' in ...

Help about plugin programming
Hi all, I am given a task to display an image of our own format in adobe acrobat reader, by adding a plug-in for the acrobat reader. I had never done such plug-in programing project, so could anyone give me some idea of how to do this task? Any SDK or sample codes that I can refer? I checked adobe website, and their adobe SDK is not free... By the way, any expert have done similar plug-ins? a plug-in that can display content of customized format files in adobe reader, or just any other adober reader plug-ins? If so, could you please send me a copy of the source program to my email chen...

Help for Excel Chart problem
Hi I have a little problem. I have to prepare audience profile for some TV Channels using dimensions age, sex, monthly income and education. I want to combine metric - age and income in one chart and non metric - sex and education in other. So I dont know which chart to use. I think the most appropriate is the bubble chart (more I have the penetration as a bubble size). But I cannot think how to dispose the dimensions and data. Example: Channel 1 Age 12-18 30 % 19-34 20% 35-64 35% 64+ 15% Sex Male - 48% Female - 52% So I need to positioning this channel and compare with ot...

Splitting cells into multiple records
Hello, Here is what I am trying to do. I have an access DB that Excels pulls info out of and puts in a nice little format. My issue is that some of the fields have multiple records in it. Here is an example of the DB: First Name | Last Name | Issue | Remediation You would only have one first and last name, but there might be multiple instances in the issue and remediation cells. First Name | Last Name | Issue | Remediation John |Smith | Issue Number 1 | Remediation Number 1 | Issue Number 2 | Remediation Nu...

Using Outlook 2000...need help moving mail
We have a Micosoft Mail postoffice that we use to handle internal mail. Now we have someone at the company who has a Palm Treo 700w smartphone. He needs to get his internal and external mail. Our external mail is a POP3 server, hosted by someone else, and this person can get email sent to his internet email address. We need a way to automatically forward the mail from the MsMail postoffice to the internet email address so we don't have to have everyone at the company forced to use his external address manually. ...

I can't format 01/19/2004 to read January 19, 2004, please help!!
Dragged and dropped a series of dates and now I can't convert them to sort them properly. What to do?? Hi Paul, "paulonline66" <paulonline66@discussions.microsoft.com> wrote in message news:3BB832DA-34AE-44B4-9D73-6F178E9AEBF0@microsoft.com... > I can't format 01/19/2004 to read January 19, 2004, please help!! > Dragged and dropped a series of dates and now I can't convert them to sort > them properly. What to do?? With the date cells selected: Data | Text to Columns | Next | Next | Select the Date option and Select the DMY option in the dropdown...

Today customize problem (...not the known "button" problem, please read)
Hello, no, don't worry, i will not ask again the "my customize today page is not functionning, what should i do ?". :-) I have a problem with outlook 2002, while it looks like ok with outlook 2000. To customize the Today page with outlook 2000, you are able to modify the content itself to show another calendar than yours, for example. The "how to" is here (extracted from OutToday.doc, found on microsoft site): " Showing different Calendar and Tasks folders By default, the Calendar and Tasks databinding tables show the information from your main Calendar and Tas...

Excel help #3
Hi, Two questions. First is, when performing a Pivot Table, is there a way of changing the source so that when you produce a pivot table, the months are in order. And, when you concatenate and join a surname and first name together, how do you then separate them ? Thanks "Gary Newman" <gary.newman@adecco.co.uk> wrote in message news:09f501c39882$44084930$a401280a@phx.gbl... > Hi, > > Two questions. > First is, when performing a Pivot Table, is there a way of > changing the source so that when you produce a pivot > table, the months are in order. > &g...

Please,I need help ASAP
I created a Named Range containing a series of product code descriptions, which I need to use for validating data entry on another column, on the same worksheet. The worksheet view is "Page Break Preview" with a custom zoom factor of 60%. Everything on the worksheet if clearly visible, the font heigh is 14 on the entire sheet, however the dropbox shown when a cell on the validated column is selected is at a much smaller zoom factor, it still has more space to disply the lines. It can't be read, as if the font heigh were set to 8 or something. I have tried everything...

Filtered cells return after save???
Excel 2007... After removing columns from a document Im left with rows that have blanks in some of the fields. I click on the column that has blanks, go to data/ filter to uncheck blanks and then do a sort on the table to alphabetize the list thats left. I save the document thinking it all looks grteat. But when I reopen the document, the filter is not saved. the rows that have blank fields are back. Is it because there are other cells in the row that are not blank? Im confused. What good is filter? I need to filter the list to remove all rows with blanks in a particular column. None of...

SUMPRODUCT help #3
Help please I'm trying to count the number on items sold on a particular month using SUMPRODUCT but I'm getting the wrong answer This is what I use for each month (Feburary) for this example: =SUMPRODUCT((MONTH($L$2:$L$5973)=2)*(YEAR($L$2:$L$5973)=2007)*($U$2:$U$5973=$A4)) The item is in A4 the date is in column L and in Column U are all the mixed items. I have checked the date format and its ok. Can any one help please? Thanks josa Odd. I would begin by testing each part of the formula. For example: 1) =SUMPRODUCT((MONTH($L$2:$L$5973)=2)) 2) =SUMPRODUCT((YEAR($L$2:$L$5973)=200...

Help With Validation Please
Hi I have a spreadsheet set up wher I'd like users to put a Y in one of 3 columns (Red, Amber or Green) Is there any way of validating so that they can only put a Y in Red or Amber or Green column at a time and not in both or all three? Many thanks for any help. Helen Assuming Red, Amber and Green are in columns B:D -- Select columns B:D Choose Data>Validation From the Allow dropdown, choose Custom In the Formula box, type: =COUNTIF($B1:$D1,"Y")<=1 Click OK helen wheels wrote: > Hi > > I have a spreadsheet set up wher I'd like users to put a Y in one...

How to combine text from 3 cells into 1 cell
How to combine from 3 text-cells into 1 cell with space between them? See your other post for answer. John "JOF" <JOF@discussions.microsoft.com> wrote in message news:35A395E5-81A3-428E-BED7-1DF41F5370FF@microsoft.com... > How to combine from 3 text-cells into 1 cell with space between them? =A1&" "&B1&" "&C1 or =CONCATENATE(A1," ",B1," ",C1) "JOF" wrote: > How to combine from 3 text-cells into 1 cell with space between them? try this if your text are in a column A in B2 =A2&" "&...

Help with Summing Up Totals
I am trying to sum up totals in order to get an accurate picture of th style box (large cap growth, large cap core, large cap value, etc. each stock corresponds to. Right now, I have the percentage investmen for each stock of the total portfolio and then what style box it fall in (large cap growth, large cap core, etc.) How can I write a formul that will look up what style box it is then take the percentage fo that stock and then sum it all up? I know I could use a VLookup bu how do I sum them in the event that there's more than one stock tha falls into a certain style box? For exampl...

Write scatterplot point ID to a cell
Hello all I'm trying to click a point in a scatterplot and have its ID (label, values, whatever) written to a cell. Any ideas? Thanks jcamoes Hi, Have a look at Jon Peltier's article "Chart Events in Microsoft Excel" http://www.computorcompanion.com/LPMArticle.asp?ID=221 Cheers Andy jcamoes wrote: > Hello all > > I'm trying to click a point in a scatterplot and have its ID (label, > values, whatever) written to a cell. Any ideas? > > Thanks > > jcamoes > -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks! That&#...

Help
Please take a look at the following LETTER - POSITION - TOTAL A - 6 - 34 A - 2 - 23 A - 2 - 13 B - 5 - 23 C - 2 - 55 D - 7 - 23 The above data is made up but ... I usee the AutoSort and only display LETTER A (3 rows of data) On a new worksheet how would I list something similar to th following: =============== Report for LETTER A Position - Grand Total 2 - x 6 - x =============== (with X being the value calculated) What I also see as being tricky is for it to only to include position that actually contain values. For example, there is nothing for LETTE A in 1st position so there isn...

Formula to copy multiple cells onto another sheet
I have a worksheet containing names and address that are repeated onto other sheets based on criteria in another column. What formula if any can I use to determine if that person meets that certain criteria to automatically copy the 3 columns that the name, phone, and address are located in to another sheet? A little more info: I have multiple worksheets and one master list containing 6 columns: First, Last, Birthday,Phone, and Address, and class. In the class column is a variety of class names. I would like to write a macro (I have never done this before!) ,if possible, that will ...

Sticking cells
BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 and 2 on my spreadsheet to 'stick', so when I scroll down my sheet the top 2 rows are always at the top. How can I do this? Thanks in advance. -- @---}-- Laura..... :) Liverpool, England "Do you know where you're going to?" "Laura ( '_' )" <laura@invalid.example.com> wrote in message news:u%235COkd8FHA.4012@TK2MSFTNGP14.phx.gbl... > BlankHiya, Im not sure how 2 explain this, but I would like 2 get Row 1 > and > 2 on my spreadsheet to 'stick', so when...

Insert Cell Text Into Chart
I have a cell of text(on sheet 1) that I want to paste into the top portion of a chart (on sheet 2) - I have done this before, but I cannot remember how - HELP! Hi insert a textbox, select this textbox and enter the equation sign '='. Now select your cell on sheet 1 and hit ENTER -- Regards Frank Kabel Frankfurt, Germany Karen wrote: > I have a cell of text(on sheet 1) that I want to paste > into the top portion of a chart (on sheet 2) - I have done > this before, but I cannot remember how - HELP! Thank you for your help - I'm sorry, maybe I didn't explain it ...

Office 2007 Styles create issue with "Too many different cell form
I'm not sure if anyone has come across this issue. I have a file that is used by both 2007 and 2003 users. Everything was working fine until one day the 2003 users were getting "Too many different cell formats." The issue was that there were now 3000+ styles on the spreadsheet. There were now custom styles created for each of the new cell styles in 2007 like the accents. It looks like everytime this was saved in compatibility mode, this was turned into a custom style. Eventually there were Accent1, Accent 1 1, Accent 1 2, etc. This looks like it will continue to...

Data Export Help Needed
What is transferspreadsheet My knowledge is extreemly limited on VB. "Douglas J. Steele" wrote: > Have you tried using TransferSpreadsheet instead of OutputTo? > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Aamer" <Aamer@discussions.microsoft.com> wrote in message > news:C9B569B2-2A0E-4075-A11F-CAA7D6AD594B@microsoft.com... > > Douglass > > > > I entered the code as you described: > > > > Private Sub Export_Data_Click() > > O...

VLookUp need helps
Hello all In a workbook, I have many worksheets. Each worksheet is a detail of a person with ID and grade. These worksheets are called "Detail worksheet". (for example, in a worksheet, cell A1 has 232 for ID and cell A3 has a grade of 9. In another worksheet, cell A1 has ID is 321 and cell A3 has a grade of 4) Also in that workbook, I have a separated worksheet with a list of people with their IDs and empty grades. This worksheet is called "Sumary worksheet" Now I want to input person's grade from each "Detail worksheet" for "Sumary worksheet". Do...