Automatically choose a bin
 

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.

Automatically choose a bin
 

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


"rwilliams616" wrote in message
...
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.

Automatically choose a bin
 

Clarification. After entering the formula ........ should read: After typing
in the formula ........

Tyro

"Tyro" wrote in message
t...
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


"rwilliams616" wrote in message
...
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.

Automatically choose a bin
 

Excellent, that works exactly how I wanted it to. Thanks!

"Tyro" wrote:

Clarification. After entering the formula ........ should read: After typing
in the formula ........

Tyro

"Tyro" wrote in message
t...
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


"rwilliams616" wrote in message
...
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.

Automatically choose a bin
 

You're welcome! :)

Tyro

"rwilliams616" wrote in message
...
Excellent, that works exactly how I wanted it to. Thanks!

"Tyro" wrote:

Clarification. After entering the formula ........ should read: After
typing
in the formula ........

Tyro

"Tyro" wrote in message
t...
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


"rwilliams616" wrote in
message
...
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.