This will give you the conditional averages for the volts and amps that
you're after
Suppose you list the zones in E2 down, eg: b, f
Put this in F2's formula bar, array-enter it
by pressing CTRL+SHIFT+ENTER to confirm the formula:
=AVERAGE(IF(($D$9:$D$29=$E2)*($C$9:$C$29>=30),B$9:B$29))
Copy F2 to G2, fill down. Adapt the ranges to suit.
Col F returns the required av for volts, col G returns the av for amps
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"qcrob" <(E-Mail Removed)> wrote in message
news:13c03a0d-57bb-4466-b96f-(E-Mail Removed)...
>I am looking into purchasing a data logger that will collect voltage,
> amperage with a time scale.
> Below is a sample of the data and a summary to be generated above the
> data. I have some problems with how I can have a msexcel spreadsheet
> automatically calculate averages and total times for each zone.
> I would like a summary for many separate zones with a dozen or so
> sessions that have recording to last from 10 to 100 seconds within
> each session and automatically disregard calculating any values in
> column C that are <30 & all rows in column B that have been
> disregarded in column C.
> Averaging for volts and amps will include all readings within each
> zone.
> To calculate time, I would like to calculate the difference between
> when the amps go above 30 & the time when the amps drop below 30 + the
> time one row down.
>
> Formulas
> In A2 =(A11-A9)+(A15-A13)+(A20-A15)
> In B2 =AVERAGE(B9:B10,B13:B15,B18:B19)
> In C2 =AVERAGE(C9:C10,C13:C15,C18:C19)
> In A3 =(A24-A22)+(A29-A26)
> In B3 =AVERAGE(B22:B23,B26:B28)
> In C3 =AVERAGE(C22:C23,C26:C28)
>
> A B C D
> 1 total time avg volts avg amps total zone
> 2 00:00:54 25.3 122 b
> 3 00:00:30 25.0 125 f
> 4
> 5
> 6
> 7
> 8 time volts amps zone
> 9 13:30:12 24.9 131 b
> 10 13:30:18 25.3 124 b
> 11 13:30:24 39.4 12 b
> 12 13:30:30 38.4 9 b
> 13 13:30:36 26.3 130 b
> 14 13:30:42 24.9 114 b
> 15 13:30:48 25.3 125 b
> 16 13:30:54 39.4 2 b
> 17 13:31:00 38.4 8 b
> 18 13:31:06 25.2 111 b
> 19 13:31:12 25.1 117 b
> 20 13:31:18 39.4 3 b
> 21 13:31:24 38.4 13 b
> 22 13:31:30 25.1 125 f
> 23 13:31:36 25 131 f
> 24 13:31:42 38.4 7 f
> 25 13:31:48 37.6 11 f
> 26 13:31:54 25 124 f
> 27 13:32:00 24.9 130 f
> 28 13:32:06 24.8 114 f
> 29 13:32:12 37.6 11 f
>
> Can anyone help me with this? And an I on the right track?
> Can this be accomplished without VBA? (as I have no success with it in
> the past)
> Thank you for your consideration.
Similar Threads
