Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average REVISITED
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average REVISITED
Check your original post!
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "F. Lawrence Kulchar" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average REVISITED
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 --- |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average REVISITED
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 -- Francis L. Kulchar "Max" wrote in message ... 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 --- |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
14 Day Average REVISITED
Welcome, Francis
-- Max Singapore http://savefile.com/projects/236895 Downloads:17,800 Files:359 Subscribers:56 xdemechanik --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with averages revisited | Excel Worksheet Functions | |||
Greater than/Less than a certain Time (revisited) | Excel Worksheet Functions | |||
Problems with autoformatting revisited | Excel Discussion (Misc queries) | |||
Nested If statement revisited | Excel Worksheet Functions | |||
Blank cell revisited | Excel Discussion (Misc queries) |