hgj1357
asked on
Fiddly Excel count and average calculation
I have list of data (32,000 rows deep) that looks like this:
4/1/2013 0:00 0
4/1/2013 0:01 6590
4/1/2013 0:02 0
4/1/2013 0:03 0
4/1/2013 0:04 0
4/1/2013 0:05 0
4/1/2013 0:06 0
4/1/2013 0:07 0
4/1/2013 0:08 0
4/1/2013 0:09 0
4/1/2013 0:10 0
4/1/2013 0:11 0
4/1/2013 0:12 0
4/1/2013 0:13 0
4/1/2013 0:14 0
4/1/2013 0:15 0
4/1/2013 0:16 0
4/1/2013 0:17 0
4/1/2013 0:18 0
4/1/2013 0:19 13350
4/1/2013 0:20 0
4/1/2013 0:21 0
4/1/2013 0:22 0
4/1/2013 0:23 0
4/1/2013 0:24 0
4/1/2013 0:25 0
4/1/2013 0:26 0
4/1/2013 0:27 0
4/1/2013 0:28 0
4/1/2013 0:29 0
4/1/2013 0:30 0
4/1/2013 0:31 0
4/1/2013 0:32 8640
4/1/2013 0:33 0
4/1/2013 0:34 0
4/1/2013 0:35 0
4/1/2013 0:36 0
Date & time with a flow. The Flow is usually zero.
I need to count the zeros from the first zero up to and including the next non-zero number, then divide the non-zero number and populate the adjacent column with the average flow number since the last non-zero. i.e. there are 18 zeros until 13350, 13 zeros until 8640, Like this:
4/1/2013 0:00 0
4/1/2013 0:01 6590
4/1/2013 0:02 0 741.6666667
4/1/2013 0:03 0 741.6666667
4/1/2013 0:04 0 741.6666667
4/1/2013 0:05 0 741.6666667
4/1/2013 0:06 0 741.6666667
4/1/2013 0:07 0 741.6666667
4/1/2013 0:08 0 741.6666667
4/1/2013 0:09 0 741.6666667
4/1/2013 0:10 0 741.6666667
4/1/2013 0:11 0 741.6666667
4/1/2013 0:12 0 741.6666667
4/1/2013 0:13 0 741.6666667
4/1/2013 0:14 0 741.6666667
4/1/2013 0:15 0 741.6666667
4/1/2013 0:16 0 741.6666667
4/1/2013 0:17 0 741.6666667
4/1/2013 0:18 0 741.6666667
4/1/2013 0:19 13350 741.6666667
4/1/2013 0:20 0 664.6153846
4/1/2013 0:21 0 664.6153846
4/1/2013 0:22 0 664.6153846
4/1/2013 0:23 0 664.6153846
4/1/2013 0:24 0 664.6153846
4/1/2013 0:25 0 664.6153846
4/1/2013 0:26 0 664.6153846
4/1/2013 0:27 0 664.6153846
4/1/2013 0:28 0 664.6153846
4/1/2013 0:29 0 664.6153846
4/1/2013 0:30 0 664.6153846
4/1/2013 0:31 0 664.6153846
4/1/2013 0:32 8640 664.6153846
4/1/2013 0:33 0
4/1/2013 0:34 0
4/1/2013 0:35 0
4/1/2013 0:36 0
4/1/2013 0:00 0
4/1/2013 0:01 6590
4/1/2013 0:02 0
4/1/2013 0:03 0
4/1/2013 0:04 0
4/1/2013 0:05 0
4/1/2013 0:06 0
4/1/2013 0:07 0
4/1/2013 0:08 0
4/1/2013 0:09 0
4/1/2013 0:10 0
4/1/2013 0:11 0
4/1/2013 0:12 0
4/1/2013 0:13 0
4/1/2013 0:14 0
4/1/2013 0:15 0
4/1/2013 0:16 0
4/1/2013 0:17 0
4/1/2013 0:18 0
4/1/2013 0:19 13350
4/1/2013 0:20 0
4/1/2013 0:21 0
4/1/2013 0:22 0
4/1/2013 0:23 0
4/1/2013 0:24 0
4/1/2013 0:25 0
4/1/2013 0:26 0
4/1/2013 0:27 0
4/1/2013 0:28 0
4/1/2013 0:29 0
4/1/2013 0:30 0
4/1/2013 0:31 0
4/1/2013 0:32 8640
4/1/2013 0:33 0
4/1/2013 0:34 0
4/1/2013 0:35 0
4/1/2013 0:36 0
Date & time with a flow. The Flow is usually zero.
I need to count the zeros from the first zero up to and including the next non-zero number, then divide the non-zero number and populate the adjacent column with the average flow number since the last non-zero. i.e. there are 18 zeros until 13350, 13 zeros until 8640, Like this:
4/1/2013 0:00 0
4/1/2013 0:01 6590
4/1/2013 0:02 0 741.6666667
4/1/2013 0:03 0 741.6666667
4/1/2013 0:04 0 741.6666667
4/1/2013 0:05 0 741.6666667
4/1/2013 0:06 0 741.6666667
4/1/2013 0:07 0 741.6666667
4/1/2013 0:08 0 741.6666667
4/1/2013 0:09 0 741.6666667
4/1/2013 0:10 0 741.6666667
4/1/2013 0:11 0 741.6666667
4/1/2013 0:12 0 741.6666667
4/1/2013 0:13 0 741.6666667
4/1/2013 0:14 0 741.6666667
4/1/2013 0:15 0 741.6666667
4/1/2013 0:16 0 741.6666667
4/1/2013 0:17 0 741.6666667
4/1/2013 0:18 0 741.6666667
4/1/2013 0:19 13350 741.6666667
4/1/2013 0:20 0 664.6153846
4/1/2013 0:21 0 664.6153846
4/1/2013 0:22 0 664.6153846
4/1/2013 0:23 0 664.6153846
4/1/2013 0:24 0 664.6153846
4/1/2013 0:25 0 664.6153846
4/1/2013 0:26 0 664.6153846
4/1/2013 0:27 0 664.6153846
4/1/2013 0:28 0 664.6153846
4/1/2013 0:29 0 664.6153846
4/1/2013 0:30 0 664.6153846
4/1/2013 0:31 0 664.6153846
4/1/2013 0:32 8640 664.6153846
4/1/2013 0:33 0
4/1/2013 0:34 0
4/1/2013 0:35 0
4/1/2013 0:36 0
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER