I am trying to find a formula which will and count and plot the number of people who registered within a particular time after account opening in column f. The formula is
On a seperate sheet than the data.
:
Same day
1-30 days
31-60 days
61 to 90 days
>90 days
Last edited by waylynjohnston1; 11-03-2016 at 06:04 PM.
Many members are unable to see images in the *.png format due to forum compatibility issues with some browsers.
If you need to post an image post it in the *.jpg format.
Even better than posting images... post a SMALL sample file. That way we can test solutions directly in the file with the relevant data.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
Thanks Tony,
The data is below. I am trying to find the fomula for the Count Column.
Name Open Date Register Date Registered Count
Bob 1/20/2016 1/20/2016 Same Day 2
Jan 1/1/2016 2/28/2016 1-30 Days 1
Dave 2/10/2016 5/1/2016 31-60 Days 1
Larry 1/31/2016 8/1/2016 61-90 Days 1
Moe 4/1/2016 4/1/2016 >90 Days 1
Curly 2/5/2016 2/7/2016
Uploaded Jpg file.
As Tony said the best way to get help is by uploading a sample file such as the one attached.
This solution adds two helper columns (D & E) which may be hidden for aesthetic purposes.
The formula that populates the first helper column is: =DATEDIF(B2,C2,"d")
The second helper column is populated with the lower boundary values of the succeeding 'Registered' label i.e. 1,31,61,and 91
As an option you could use the bin numbers to populate the labels which would allow the changing of the labels by simply changing the values in that column.
A formula to populate the labels could be: =E2&"-"&E3-1&" Days"
The 'Count' column could then be populated with the formula: =COUNTIFS(D$2:D$7,">="&E1,D$2:D$7,"<"&E2)
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
Try this...
Data Range
This formula entered in G2 and copied down:
=SUMPRODUCT(--(DATEDIF(B$2:B$7,C$2:C$7,"d")>=E2),--(DATEDIF(B$2:B$7,C$2:C$7,"d")<=F2))
Simple ...
=SUMPRODUCT(--(C$2:C$7-B$2:B$7>=E2),--(C$2:C$7-B$2:B$7<=F2))
Thank you JeteMC, Tony Valko, and Phuocam. I used JeteMC solution. It worked but I'd like to approach the problem without helper columns as when I sort the raw data, the formulas become corrupted.
I forgot to mention a very important detail. Some of the registration dates are prior to the open date. Therefore I am receiving a #NUM! Value in my results. I believe this is due to the negative value. Do you know a way to get around this issue of the #NUM! displaying?
Can you post a SMALL sample file and show us what result you expect?
A SMALL file will have about 20 rows worth of data.
While I can't post a file, in the example Tony posted on 11/3/16 at 7pm, if cell C6 is changed to 3/29/16, I would expect cell G2 to change to 1. With the current formula, if cell C6 is changed to 3/2/16, cell G2 changes to #NUM!.
Try Phuocam's formula: gives result of 1 with changed date.
If G2 should change to 1, shouldn't G3 change to 2?Originally Posted by waylynjohnston1
Try this...
=SUMPRODUCT(--(ABS(C$2:C$7-B$2:B$7)>=E2),--(ABS(C$2:C$7-B$2:B$7)<=F2))
Tony Valko,
That formula did the trick. Thank you Tony Valko, JohnTopley, Phuocam, and JeetMc for all the help in solving. Tony, I did want G2 to change to 1, and G3 to stay at 1, and the formula provided worked. This formula allows you to evaluate values contained in two columns of dates with multiple criteria using the sum product function. This is SOLVED. Thank you so much for your help!!!
Waylyn
You're welcome. Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks