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