Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |