Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the frequency?
[Re-word my post]
Does anyone have any suggestions on how to determine the frequency? There is a list of date under column A, and a given number 3 in cell B1 I would like to determine the frequency with +/- 3 days acceptable range 04-Jan-09, 09-Jan-09, 11-Jan-09, 18-Jan-09, 20-Jan-09, 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09, 05-Feb-09, 06-Feb-09, 12-Feb-09, 17-Feb-09 ... On 4-Jan-08, it should return 1 in cell C1 for range between 1-Jan-09 and 7-Jan-09, which include 04-Jan-09 only. On 09-Jan-08, it should return 2 in cell C3 for range between 5-Jan-09 and 12-Jan-09, which include 09-Jan-09 and 11-Jan-09. On 11-Jan-08, it should return 2 in cell C3 for range between 8-Jan-09 and 14-Jan-09, which include 09-Jan-09 and 11-Jan-09. .... On 24-Jan-08, it should return 8 in cell C8 for range between 21-Jan-09 and 27-Jan-09, which include 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09 Does anyone have any suggestions on how to determine the occurrence? Thanks in advance for any suggestions Eric |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the frequency?
On Sun, 23 Nov 2008 05:12:05 -0800, Eric
wrote: [Re-word my post] Does anyone have any suggestions on how to determine the frequency? There is a list of date under column A, and a given number 3 in cell B1 I would like to determine the frequency with +/- 3 days acceptable range 04-Jan-09, 09-Jan-09, 11-Jan-09, 18-Jan-09, 20-Jan-09, 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09, 05-Feb-09, 06-Feb-09, 12-Feb-09, 17-Feb-09 ... On 4-Jan-08, it should return 1 in cell C1 for range between 1-Jan-09 and 7-Jan-09, which include 04-Jan-09 only. On 09-Jan-08, it should return 2 in cell C3 for range between 5-Jan-09 and 12-Jan-09, which include 09-Jan-09 and 11-Jan-09. On 11-Jan-08, it should return 2 in cell C3 for range between 8-Jan-09 and 14-Jan-09, which include 09-Jan-09 and 11-Jan-09. ... On 24-Jan-08, it should return 8 in cell C8 for range between 21-Jan-09 and 27-Jan-09, which include 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09 Does anyone have any suggestions on how to determine the occurrence? Thanks in advance for any suggestions Eric In your second example i think that C3 should be C2 and that 5-Jan should be 6-Jan. Try the following formula in cell C1: =SUMPRODUCT((A$1:A$100=A1-B$1)*(A$1:A$100<=A1+B$1)) Change the 100 to be at least the number of dates that you have in column A. Copy the formula down as many rows as you need. Hope this helps / Lars-Åke |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the frequency?
Re-tweaked my response involving sumproduct in your earlier posting
(I read liberally between-the-lines in venturing my response) Try in C1: =SUMPRODUCT(($A$1:$A$17=A1-$B$1)*($A$1:$A$17<=A1+$B$1)) Copy down P/s: From your posting profile, you have put in close to 1,100 posts to-date. That's quite a lot of posts. And you must have received many, many responses since which helped you resolve your issues and build up your knowledge along the way. But amazingly, you have yet to help out any other poster to-date? -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Eric" wrote: [Re-word my post] Does anyone have any suggestions on how to determine the frequency? There is a list of date under column A, and a given number 3 in cell B1 I would like to determine the frequency with +/- 3 days acceptable range 04-Jan-09, 09-Jan-09, 11-Jan-09, 18-Jan-09, 20-Jan-09, 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09, 05-Feb-09, 06-Feb-09, 12-Feb-09, 17-Feb-09 ... On 4-Jan-08, it should return 1 in cell C1 for range between 1-Jan-09 and 7-Jan-09, which include 04-Jan-09 only. On 09-Jan-08, it should return 2 in cell C3 for range between 5-Jan-09 and 12-Jan-09, which include 09-Jan-09 and 11-Jan-09. On 11-Jan-08, it should return 2 in cell C3 for range between 8-Jan-09 and 14-Jan-09, which include 09-Jan-09 and 11-Jan-09. ... On 24-Jan-08, it should return 8 in cell C8 for range between 21-Jan-09 and 27-Jan-09, which include 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to determine the frequency?
And a lot of Eric's posts are repeats, and I've yet to see him thank anyone.
While all his posts require some creativity to solve, it's hard to believe they are related. It's easier to believe he's playing games. Regards, Fred. "Max" wrote in message ... Re-tweaked my response involving sumproduct in your earlier posting (I read liberally between-the-lines in venturing my response) Try in C1: =SUMPRODUCT(($A$1:$A$17=A1-$B$1)*($A$1:$A$17<=A1+$B$1)) Copy down P/s: From your posting profile, you have put in close to 1,100 posts to-date. That's quite a lot of posts. And you must have received many, many responses since which helped you resolve your issues and build up your knowledge along the way. But amazingly, you have yet to help out any other poster to-date? -- Max Singapore http://savefile.com/projects/236895 Downloads:20,500 Files:363 Subscribers:64 xdemechanik --- "Eric" wrote: [Re-word my post] Does anyone have any suggestions on how to determine the frequency? There is a list of date under column A, and a given number 3 in cell B1 I would like to determine the frequency with +/- 3 days acceptable range 04-Jan-09, 09-Jan-09, 11-Jan-09, 18-Jan-09, 20-Jan-09, 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09, 05-Feb-09, 06-Feb-09, 12-Feb-09, 17-Feb-09 ... On 4-Jan-08, it should return 1 in cell C1 for range between 1-Jan-09 and 7-Jan-09, which include 04-Jan-09 only. On 09-Jan-08, it should return 2 in cell C3 for range between 5-Jan-09 and 12-Jan-09, which include 09-Jan-09 and 11-Jan-09. On 11-Jan-08, it should return 2 in cell C3 for range between 8-Jan-09 and 14-Jan-09, which include 09-Jan-09 and 11-Jan-09. ... On 24-Jan-08, it should return 8 in cell C8 for range between 21-Jan-09 and 27-Jan-09, which include 22-Jan-09, 23-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 24-Jan-09, 26-Jan-09, 27-Jan-09 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to I determine frequency | Excel Worksheet Functions | |||
Frequency.... I think | Excel Worksheet Functions | |||
How to determine the frequency of a list of numbers? | Excel Discussion (Misc queries) | |||
histograms - frequency and relative frequency? | Excel Discussion (Misc queries) | |||
Determine Frequency in Filtered List | Excel Worksheet Functions |