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