I'm trying to calculate some max/min/ave data based on a certain time criteria constraints. I have multiple samples of data, taken every 5 seconds.

DateTime		Value
2009-09-10 13:30:00	1
2009-09-10 13:30:05	2	
2009-09-10 30:30:10	3
.
.
.
2009-09-10 13:30:55	4
2009-09-10 13:31:00	5
2009-09-10 13:31:05	6
2009-09-10 13:31:10	7
.
.
.

9/10/2009 9:30:09 AM

have you tried to reference a cell with those dates in there as opposed to "2009-09-10 13:30:00"

so it would look like this:

{=max(if(A2:A30>C1, B2:B30), if(A2:A30<=C2, B2:B30))}

9/10/2009 9:31:52 AM

I have tried that. On another sheet where I'm trying to summarize all the data, I have my two time constraints in two cells, A2=13:30:00 and B2=13:31:00. It spits out the same #VALUE

Array equations are kinda finnicky, but gets really crazy if you try to throw in dates. I think it's looking for date serial numbers, but never have been able to figure it out exactly.

9/10/2009 9:33:41 AM

use =DATEVALUE("2009-09-10 13:30:00")

[Edited on September 10, 2009 at 9:36 AM. Reason : my guess is that it's not recognizing the date string as a datetime type]

9/10/2009 9:34:38 AM

Actually, I don't have to have the dates in there, as I can focus just on the time. But I think I'm running into the same issue with the datatypes.

9/10/2009 9:41:46 AM

Convert the time to an integer value

=HOUR(C5) + (MINUTE(C5)/60) + (SECOND(C5)/60/60)

and use that as your limits

9/10/2009 9:53:39 AM

Thanks Chance. I'll give that a shot.

Here's another thought. All of this data is coming out of a large SQL database. Is it possible to have a cell execute a database query (to either a SQL server or an Access database)? I've only seen queries come from VBA, which terrifies me, but it seems like it would be simple to have a cell equation do something like =sqlquery("SELECT MAX(Value) FROM......")

Horribly inefficient, but I'm not going for efficiency right now It would save me from having to copy the massive 200,000 row dataset into Excel.

9/10/2009 10:08:31 AM

The company that I work for creates a product that allows you to write a sql query and pump the results into an Excel cell, range of cells. PM me if you want to know more.

9/10/2009 10:12:16 AM

http://articles.techrepublic.com.com/5100-10878_11-6112813.html

Excel has been around long enough that unless you're trying to do something really complex/weird, there should be some info out there to tell you how to do it.

Oh, you said sql, then sql and access. Does it have to be sql? I think it can do that to, this from the help page of Office 2007

Quote :

"SQL.REQUEST Show All Hide All Some of the content in this topic may not be applicable to some languages. Connects with an external data source, and runs a query from a worksheet. SQL.REQUEST then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (add-in: A supplemental program that adds custom commands or custom features to Microsoft Office.) (XLODBC.XLA). You can install the add-in from the Microsoft Office Web site. Syntax SQL.REQUEST(connection_string,output_ref,driver_prompt,query_text,col_names_logical) Connection_string supplies information, such as the data source name, user ID, and passwords, required by the driver being used to connect to a data source and must follow the driver's format. The following table provides three example connection strings for three drivers. Driver Connection_string dBASE DSN=NWind;PWD=test SQL Server DSN=MyServer;UID=dbayer; PWD=123;Database=Pubs ORACLE DNS=My Oracle Data Source;DBQ=MYSER VER;UID=JohnS;PWD=Sesame You must define the data source name (DSN) used in connection_string before you try to connect to it. You can enter connection_string as an array or a string. If connection_string exceeds 250 characters, you must enter it as an array. If SQL.REQUEST is unable to gain access to the data source using connection_string, it returns the #N/A error value. Output_ref is a cell reference where you want the completed connection string placed. If you enter SQL.REQUEST on a worksheet, then output_ref is ignored. Use output_ref when you want SQL.REQUEST to return the completed connection string (you must enter SQL.REQUEST on a macro sheet in this case). If you omit output_ref, SQL.REQUEST does not return a completed connection string. Driver_prompt specifies when the driver dialog box is displayed and which options are available. Use one of the numbers described in the following table. If driver_prompt is omitted, SQL.REQUEST uses 2 as the default. Driver_prompt Description 1 Driver dialog box is always displayed. 2 Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. All dialog box options are available. 3 Driver dialog box is displayed only if information provided by the connection string and the data source specification is not sufficient to complete the connection. Dialog box options appear dimmed and unavailable if they are not required. 4 Driver dialog box is not displayed. If the connection is not successful, it returns an error. Query_text is the SQL statement that you want to execute on the data source. If SQL.REQUEST is unable to execute query_text on the specified data source, it returns the #N/A error value. You can update a query by concatenating references into query_text. In the following example, every time $A$3 changes, SQL.REQUEST uses the new value to update the query. "SELECT Name FROM Customers WHERE Balance > "&$A$3&"". Microsoft Excel limits strings to a length of 255 characters. If query_text exceeds that length, enter the query in a vertical range of cells, and use the entire range as the query_text. The values of the cells are concatenated to form the complete SQL statement. Column_names_logical indicates whether column names are returned as the first row of the results. Set this argument to TRUE if you want the column names to be returned as the first row of the results. Use FALSE if you do not want the column names returned. If column_names_logical is omitted, SQL.REQUEST does not return column names. Return Value If this function completes all of its actions, it returns an array of query results or the number of rows affected by the query. If SQL.REQUEST is unable to access the data source using connection_string, it returns the #N/A error value. Remarks SQL.REQUEST can be entered as an array. When you enter SQL.REQUEST as an array, it returns an array to fit that range. If the range of cells is larger than the result set, SQL.REQUEST adds empty cells to the returned array to increase it to the necessary size. If the result set is larger than the range entered as an array, SQL.REQUEST returns the whole array. The arguments to SQL.REQUEST are in a different order than the arguments to the SQLRequest function in Visual Basic for Applications. Example Suppose you want to make a query of a dBASE database named DBASE4. When you enter the following formula in a cell, an array of query results is returned, with the first row being the column names: SQL.REQUEST("DSN=NWind;DBQ=c:\msquery;FIL=dBASE4", c15, 2, "Select Custmr_ID, Due_Date from Orders WHERE order_Amt>100", TRUE)"

[Edited on September 10, 2009 at 12:10 PM. Reason : .]

9/10/2009 12:04:26 PM

^ Thanks for that. I have been trying to avoid diving into the world of VBA, but it looks like it's a must for executing SQL queries. I've successfully avoided it for 10 years

Right now all our data is on a SQL server, but I pulled in a subset into an Access DB to see if I could work with it any easier. I also pulled in the same data (250k rows maybe?) into Excel 2007.

The work I'm doing now is just for experimentation as I try to develop an algorithm for analyzing and performing operations on the data. Eventually it will be coded into a program by our software folks once I figure out what it is the heck I want to do with the data

9/10/2009 1:58:08 PM

VBA really isn't hard if you have any OO based programming background. First, just record a macro doing random operations to see what it spits out and get an idea of things.

From there, just google for what you're trying to do.

The biggest thing I struggled (struggle?) with regarding excel VBA is how it handles ranges versus individual cells and setting data.

For example

this is a bit of code I recorded just now

   Selection.Copy
    Range("E10").Select
    ActiveSheet.Paste

Cells(1, 1).Value = 10

  
  myMax = 0
  For myRow = 1 To 10 Step 1
     If Cells(myRow, 3).Value > 5 And Cells(myRow, 4).Value > myMax Then
        myMax = Cells(myRow, 4)
     End If
  Next myRow
End Sub

9/10/2009 2:57:04 PM