14 Day Average

[F. Lawrence Kulchar]

I have data arranged as follows:

A B C
D E

DATE TOTAL # of READINGS DAILY AVE.
14 DAY AVE

June 1 . 1200 10
120 ---
June 2 1000 8
125 ---
June 3 1200 8
150 ---
June 4 800 8
100 ---
June 5 2000 8
250 ---
June 6 720 9
80 ---
June 7 1000 10
100 ---
June 8 1700 10
170 ---
June 9 250 1
250 ---
June 10 300 2
150 ---
June 11 450 3
150 ---
June 12 600 3
200 ---
June 13 1000 4
250 ---
June 14 1200 (13420) 12 (96) 100
139.79
June 15 --- --
--- 139.79
June 16 800 (13020) 10 (96) 80
135.63
June 17 100 (12120) 1 (89) 100
136.18

THE POINT IS THAT I AM NOT COMPUTING THE LAST 14 DAY AVERAGE.....BUT I AM
TRYING TO COMPUTE THE LAST 14 DAY AVERAGE IF, AND ONLY IF, THERE IS DATA FOR
THE DATE (S) BEING EXAMINED.
In other words, since June 15 is blank (a reading was NOT made), then the
date is ignored (it does NOT count towards my average
!!).

I had been using:

E14 = SUM(B1:B14)/SUM(C1:C14) for my 14 day average...then copy down!!!

Now, I'd like a formula to give me the average in E15 (which is the same as
E14 because the June 15th totals are blank!!!!!

I suppose I am looking for some sort of function that has SUMIF...or
whatever...in its use.

Please ADVISE: I am looking to compute, on a daily basis, the last 14 days
of data average -- but ONLY if all 14 days have data in them -- therefore, I
am NOT looking for a 14 day running average (that would be too easy), but a
14 day average which comprise cells with data in them.

For example, I may skip June 18 through June 25. Therefore the 14 day
average for June 25 would include data from the following dates:

1. June 3
2. June 4
3. June 5
4. June 6
5. June 7
6. June 8
7. June 9
8. June 10
9. June 11
10. June 12
11. June 13
12. June 14
13. June 16
14. June 17

and

and then, if I have data going in on June 26, I would use for my (last) 14
day average...
all of the above, dropping June 3rd...and adding June 26th!.

THANK YOU FOR THE HELP,

FLKulchar

 

[Max]

.. a 14 day average which comprise cells with data in them.

My thoughts would be to take out dynamically only these rows (with data) in
an adjacent area to the right of the source table. Then we could easily point
the average calcs on that range

Assuming your source table as posted is in cols A to D, data from row2 down,
where rows w/o data is determined by col B (Total) being either blank or
containing zeros

In E2: =IF(OR(B2="",B2=0),"",ROW())
Leave E1 blank. This is the criteria col.

In F2:
=IF(ROWS($1:1)>COUNT($E:$E),"",INDEX(A:A,SMALL($E:$E,ROWS($1:1))))
Copy F2 across to I2. Select E2:I2, fill down to cover the max expected
extent of source data, eg down to I500? Minimize/hide col E. Format col F as
dates. Cols F to I will return dynamically, only the required lines with data
neatly bunched together at the top in a continuous range. You could structure
your average calcs on that range.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:55
xdemechanik
---

 

[Ragdyer]

See if this works for you.

It's an *array* formula, and it's kinda big!<g>

Using Row 1 for headers, with dates in Column A, and data starting in B2,
enter this in E2:

=IF(COUNT(B$2:B2)>=14,SUM(INDEX(B$2:B2,LARGE(ROW($1:1)*(B$2:B2<>""),14)):B2)
/SUM(INDEX(C$2:C2,LARGE(ROW($1:1)*(C$2:C2<>""),14)):C2),0)

--
Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.
You *must also* use CSE when revising the formula.

*After* the CSE entry, copy down as needed.

 

[Ragdyer]

What does "all to no avail..." mean?

Wrong answer ...
No answer ...
Error message ...

Describe exactly what return you see, and post the *exact* formula that you
used.

 

[Max]

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
---