Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
I have a spreadsheet that I update weekly with several formula's in it and
contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
How about another cell with nothing but the number. Or, weeknum*3 idea. Or,
edit/replace -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
Can you give me an idea as to how the weeknum* would work
"Don Guillett" wrote: How about another cell with nothing but the number. Or, weeknum*3 idea. Or, edit/replace -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
Hi,
=WEEKNUM(aDate,type) for example =WEEKNUM(TODAY()) this would return the weeknum of today. The weeknumber being the number of the week in the year. This is the 45 week in 2008 form example. Returns a number that indicates where the week falls numerically within a year. Important The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. Syntax WEEKNUM(serial_num,return_type) Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. Return_type is a number that determines on which day the week begins. The default is 1. Return_type Week Begins 1 Week begins on Sunday. Weekdays are numbered 1 through 7. 2 Week begins on Monday. Weekdays are numbered 1 through 7. Remark Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default. Then each of your three cells can contain this function or can reference each other. If the number you want is not what the function return just add or subtract a given amount from it: =WEEKNUM(TODAY())-10 Also not this function is part of the Analysis ToolPak to if you are using Excel 2003 or earlier, choose Tools, Add-ins, and check Analysis ToolPak. If this helps, please click the Yes button Thanks, Shane Devenshire "Donnas3944" wrote: Can you give me an idea as to how the weeknum* would work "Don Guillett" wrote: How about another cell with nothing but the number. Or, weeknum*3 idea. Or, edit/replace -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
Thanks but I still don't get it....here is what I have...ad SS with 89 rows
of data and various formula's. So for example one colum adds 89 rows of numbers, I then need to divide that colum by 28 ( this is how many weeks we have been using this info)...so next week I will add another week of data an then need to divide the column by 29. This has to be done in numerous colums..I want to do something that will automatically change the divide by weekly number increaseing by one where needed instead of manually changing. Does this make sense? NOt sure I am explaining myself very well "ShaneDevenshire" wrote: Hi, =WEEKNUM(aDate,type) for example =WEEKNUM(TODAY()) this would return the weeknum of today. The weeknumber being the number of the week in the year. This is the 45 week in 2008 form example. Returns a number that indicates where the week falls numerically within a year. Important The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. Syntax WEEKNUM(serial_num,return_type) Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. Return_type is a number that determines on which day the week begins. The default is 1. Return_type Week Begins 1 Week begins on Sunday. Weekdays are numbered 1 through 7. 2 Week begins on Monday. Weekdays are numbered 1 through 7. Remark Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default. Then each of your three cells can contain this function or can reference each other. If the number you want is not what the function return just add or subtract a given amount from it: =WEEKNUM(TODAY())-10 Also not this function is part of the Analysis ToolPak to if you are using Excel 2003 or earlier, choose Tools, Add-ins, and check Analysis ToolPak. If this helps, please click the Yes button Thanks, Shane Devenshire "Donnas3944" wrote: Can you give me an idea as to how the weeknum* would work "Don Guillett" wrote: How about another cell with nothing but the number. Or, weeknum*3 idea. Or, edit/replace -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
How do you know there are 28 weeks of data? If you can tell that to Excel,
then you can get it to automatically divided by the number of weeks. Do you have a week number in your data? Do you have a date? If you have a date (in, say, column A), maybe the number of weeks is: =weeknum(max(A:A))-weeknum(min(A:A))+1 If there's no way of determining from the data how many weeks you have, I would create a separate cell where you enter the number of weeks. Then change your averaging formulas to divide by this cell rather than a specific number. Regards, Fred. "Donnas3944" wrote in message ... Thanks but I still don't get it....here is what I have...ad SS with 89 rows of data and various formula's. So for example one colum adds 89 rows of numbers, I then need to divide that colum by 28 ( this is how many weeks we have been using this info)...so next week I will add another week of data an then need to divide the column by 29. This has to be done in numerous colums..I want to do something that will automatically change the divide by weekly number increaseing by one where needed instead of manually changing. Does this make sense? NOt sure I am explaining myself very well "ShaneDevenshire" wrote: Hi, =WEEKNUM(aDate,type) for example =WEEKNUM(TODAY()) this would return the weeknum of today. The weeknumber being the number of the week in the year. This is the 45 week in 2008 form example. Returns a number that indicates where the week falls numerically within a year. Important The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard. Syntax WEEKNUM(serial_num,return_type) Serial_num is a date within the week. Dates should be entered by using the DATE function, or as results of other formulas or functions. For example, use DATE(2008,5,23) for the 23rd day of May, 2008. Problems can occur if dates are entered as text. Return_type is a number that determines on which day the week begins. The default is 1. Return_type Week Begins 1 Week begins on Sunday. Weekdays are numbered 1 through 7. 2 Week begins on Monday. Weekdays are numbered 1 through 7. Remark Microsoft Excel stores dates as sequential serial numbers so they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,448 days after January 1, 1900. Microsoft Excel for the Macintosh uses a different date system as its default. Then each of your three cells can contain this function or can reference each other. If the number you want is not what the function return just add or subtract a given amount from it: =WEEKNUM(TODAY())-10 Also not this function is part of the Analysis ToolPak to if you are using Excel 2003 or earlier, choose Tools, Add-ins, and check Analysis ToolPak. If this helps, please click the Yes button Thanks, Shane Devenshire "Donnas3944" wrote: Can you give me an idea as to how the weeknum* would work "Don Guillett" wrote: How about another cell with nothing but the number. Or, weeknum*3 idea. Or, edit/replace -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Question
This formula using 1 April 2008 yields 31
=INT((TODAY()-DATE(2008,4,1))/7) 31.00 Using 2008,4,15 gives 29 -- Don Guillett Microsoft MVP Excel SalesAid Software "Donnas3944" wrote in message ... I have a spreadsheet that I update weekly with several formula's in it and contains several weeks of information. Each week when I go in and update it he final step is to change the number of weeks I am dividing by. Example this week was 28, next Friday I will need to divid by 29. This has to be changed in 3 locations on the SS. Is there a way I can add a fromula that I can change that will change this number in all 3 locaitons and only have to change it once. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where can I see my question and answer? Yesterday I ask a question | Excel Discussion (Misc queries) | |||
If Question | Excel Worksheet Functions | |||
Newbie Question - Subtraction Formula Question | Excel Discussion (Misc queries) | |||
question....help | Excel Discussion (Misc queries) | |||
The question is an excel question that I need to figure out howto do in excel. | Excel Worksheet Functions |