14 Day Average REVISITED

[F. Lawrence Kulchar]

I have the following within my opening 5 columns of my spreaadsheet:

A B C D E

3 DATE TOTAL EFFORTS DAILY 14 DAY
4 Ave. Ave.
5
6 1-Jun 1000 10 100 #REF!
7 2-Jun 1200 12 100 #REF!
8 3-Jun 800 4 200 #REF!
9 4-Jun 300 3 100 #REF!
10 5-Jun 600 3 200 #REF!
11 6-Jun 200 2 100 #REF!
12 7-Jun 180 3 60 #REF!
13 8-Jun 160 4 40 #REF!
14 9-Jun 120 3 40 #REF!
15 10-Jun 600 6 100 #REF!
16 11-Jun 250 2 125 #REF!
17 12-Jun 660 4 165 #REF!
18 13-Jun 520 4 130 #REF!
19 14-Jun 1500 10 150 115.5714
20 12-Jan 1200 12 100 115.1388889
21 13-Jan 300 4 75 115.46875
22 14-Jan 60 1 60 109.0163934
23 15-Jan 109.4827586
24 16-Jan 104.5454545
25 17-Jan 104.7169811
26 18-Jan 107.4
27 19-Jan 113.2608696
28 20-Jan 118.372093
29 21-Jan 121.3513514
30 22-Jan 121.1428571
31 23-Jan 140 1 116.25
32 24-Jan 140 1 115.1724138
33 25-Jan 96.84210526
34 26-Jan 140 1 97.5
35 27-Jan 140 1 124
36 28-Jan 140 1 140
37 29-Jan 140 1 140
38 30-Jan 140 1 140
39 31-Jan 140 1 140
40 1-Feb 140 1 140
41 2-Feb 140 1 140
42 3-Feb 140 1 140
43 4-Feb 140 1 140
44 5-Feb 140 1 140
45 6-Feb 140 1 140


In column E19, I have = SUM(B6:B19)/SUM(C6:C19)...then copied UP and DOWN,
in order to arrive at a running 14 day average.

Column E20 has SUM(B7:B20)/SUM(C7:C20) to, AGAIN, arrive at a 14 day average.

HERE IS MY DILEMMA:

I DO NOT WANT A "RUNNING" AVERAGE. I ONLY WANT A 14 DAY AVERAGE FOR CELLS
WHERE THERE ARE ACTUALLY 14 DAYS WITHIN THE RANGE.

In other words, cell E23 should read exactly as cell E20...

In E23, I presently have =SUM(B10:B23)/SUM(C10:C23)...WHICH IS INCORRECT
BECAUSE JAN. 15TH HAS NO DATA IN IT!...THEREFORE, IT SHOULD READ THE SAME 14
DAY AVERAGE AS JAN. 14...THE LAST 14DAYS WORTH OF DATA.

THE SAME APPLIES DOWN TO E30, Jan. 22 -- the last 14 days of data up to Jan.
22...E6:E22...the last 14 days of data! Therefore, cells E23 through E30
(Jan. 15 through Jan. 22) should all have 109.0164...the same as Jan.
14...bexause Jan. through Jan15 are the cells comprising the last 14 days of
data.

Finally, on Jan. 23, we pick up the last 14 days:

So, in cell E31, we should have =SUM(B31+B10:B22)/SUM(C31+C10:C22)

Can anyone provide me a drop-down formula that works in Column E where I can
continually keep a 14 day average for ONLY those days that have data in
it!!???

Then I need to use that formula within my spreadsheet in column E !!

Thanks,

FLKulchar

 

[Ragdyer]

Check your original post!

 

[Max]

Below is what I've posted further in your earlier thread
(Suggest you follow up there):

-------------
To help you easily compare the 2 suggestions offered (mine & RD's)
here's a working sample with identical dummy source data
to illustrate both methods, each in its own sheet:
http://freefilehosting.net/download/3mc2h
14 day average.xls

Both return identical results ...

Your preference of course, as to which option to take up
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:55
xdemechanik
---

 

[Francis L. Kulchar]

Mr. Max:

I sincerely thank you for ALL your excellent support and help; I dave
downloaded your file with both methods...after I study them (AND UNDERSTAND
THEM), I WILL CHOOSE MY PREFERRED METHOD...although I am tempted to go with
Ragdyer's answer only because it looks a bit shorter.

In any event, I will Have to study and learn the INDEX, LARGE, and ROW
functions!

Thank you,

FLKulchar

 

[Max]

Welcome, Francis
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:56
xdemechanik
---