Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
using hlookup with sum/choose? | Excel Discussion (Misc queries) | |||
Choose next record | Excel Discussion (Misc queries) | |||
Choose From a Range | Excel Worksheet Functions | |||
Choose. | New Users to Excel | |||
Why does Excel automatically choose the PlotBy method | Charts and Charting in Excel |