Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel question?
What you want to do is have a database function.
For this to work, you'll need to setup filter criteria, to look like this (I added column and row labels): A 1 rank 2 ="=0" Then, use DAVERAGE(myDataRange, "FI5350", A1:A2) where myDataRange is the spreadsheet you posted, the second parameter is the column label that you want to summarize (average in this case), and the third parameter denotes the range where the criteria is contained (as shown above). In SQL this roughly translates into: SELECT myDataRange.rank, Avg(myDataRange.FI5350) FROM myDataRange GROUP BY myDataRange.rank except each rank is averaged separately with the worksheet function. My suggestion for layout: have each criteria (as shown above) in a separate column; then, use row labels to denote which summary function you're using (average, max, min, flow, etc). Fill out one column of values (i.e. for rank 0) then copy the formulas making sure you use relative column references in criteria range parameter. Use absolute references for the data range, or else name it, to ensure the database parameter is correct. You might find this helpful: http://office.microsoft.com/en-us/ex...CH062528201033 -Ilia Mark J wrote: i have a spreadsheet with the following: DateTime FI5350 rank 00:29:10 113.87 0 00:29:30 292.01 0 00:29:50 471.49 0 00:30:10 483.40 0 00:30:30 489.60 0 00:30:50 494.05 0 00:31:10 492.85 0 00:31:30 485.10 0 00:31:50 491.25 0 00:32:10 476.25 0 00:32:30 487.05 0 00:32:50 330.02 0 00:33:10 314.76 0 00:33:30 320.66 0 00:33:50 325.97 0 00:34:10 319.81 0 00:34:30 327.27 0 00:34:50 333.22 0 00:35:10 326.12 0 00:35:30 500.05 0 00:36:30 499.85 0 00:36:50 142.63 0 03:21:30 263.55 3 03:21:50 303.31 3 03:22:10 316.16 3 03:22:30 320.61 3 03:22:50 323.96 3 03:23:10 467.89 3 03:23:30 500.05 3 03:27:30 496.55 3 03:27:50 500.05 3 03:28:10 317.81 3 is there a formula that would produce the following; for Rank:0 Average 387.15 Max 0:36:50 Min 0:29:10 Runtime = 0:36:50 - 0:29:10 -- 05:40 Flow = (05:40)*(387.15) ---- 2477 for Rank:3 Average 381.00 Max 3:28:10 Min 3:21:30 Runtime = 3:28:10 - 3:21:30 --- 06:40 Flow = (06:40)*(381.00) ---- 2438 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|