Automatically choose a bin

  • Thread starter Thread starter rwilliams616
  • Start date Start date
R

rwilliams616

I have a column with a series of dates, each referring to the date a certain
event occurred. I have another column that represents weekly bins, such that
week #1 ends on November 4, week #2 ends on November 11, week #3 ends on
November 18, etc. I would like to set up my spreadsheet so that when I enter
an event date, it will automatically return the week # corresponding to that
date. So for example, if I enter an event date of November 2, it will return
"1" and if I enter an event date of November 18, it will return "3".

I plan on updating this file frequently, so I am hoping to automate this
rather than use the histogram each time I need to update it. Is there an
easy way to do this, or is it easier to just run the histogram each time I
need an update?

Thanks.
 
if your weekend dates are in A1:A10 and the date you enter is in B1, you can
use the array formula:
=IF(B1>=A1-6,IF(B1<=A10,MATCH(B1,A1:A10-6,1),0),0)
After entering the formula, press Ctrl+Shift+Enter
The formula returns the number of the week the date is in, 0 if the date is
out of range.
Tyro
 
Clarification. After entering the formula ........ should read: After typing
in the formula ........

Tyro
 
Back
Top