Average above "n" rows in blank row [Closed]

Report
-
 thenarfer -
Hello,

I'm working on a dataset of historical price data. The set has dates in column A, and prices in column B. Through a macro I have inserted a blank row at the end of every month. Will you help me write a macro that averages the prices in the rows above the blank rows? Keep in mind that there are different numbers of rows per blank-row interval. Thank you.

Example:
Before:
10
14
(empty row)
20
30
22
(empty row)
14
16
(empty row)
After:
10
14
12
20
30
22
24
14
16
15

1 reply

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
797
no need of a macro . no need of a blank row.
subtotal function will do

sample data like this(col. c additional column . C2 has formula
=month(a2) and c2 is copied down.


date price month
1-Jan 42 1
8-Jan 90 1
15-Jan 68 1
1-Feb 66 2
9-Feb 84 2
18-Feb 62 2
2-Mar 41 3
5-Mar 82 3
28-Mar 75 3


click data(menu bar)-subtotals

in the subtotal window
"at each change in"
choose "price"

under "use function"
choose "average"

under add subtotal to
check "month"
check "replace current subtotals"
check "summary below data"
click ok

you will get


date price month
1-Jan 42 1
8-Jan 90 1
15-Jan 68 1
66.66666667 1 Average
1-Feb 66 2
9-Feb 84 2
18-Feb 62 2
70.66666667 2 Average
2-Mar 41 3
5-Mar 82 3
28-Mar 75 3
66 3 Average
67.77777778 Grand Average


will this not do . still do you want a macro?
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

This is awesome! Worked like a charm! No need for a macro any more. Thank you! =)

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!