[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
Solved

# Fiddly Excel count and average calculation

Posted on 2013-05-15
Medium Priority
316 Views
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
0
Question by:hgj1357
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 39169733
Assuming data in columns A and B try this formula in C3 copied down

=IFERROR(IF(B2>0,INDEX(B3:B\$33000,MATCH(TRUE,INDEX(B3:B\$33000>0,0),0))/MATCH(TRUE,INDEX(B3:B\$33000>0,0),0),C2),"")

see attached

regards, barry
average-across-rows.xlsx
0

LVL 2

Author Closing Comment

ID: 39169843
Bob's your uncle!  Works like a charm
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
###### Suggested Courses
Course of the Month12 days, 15 hours left to enroll