Question

  • Thread starter Thread starter Donnas3944
  • Start date Start date
D

Donnas3944

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.
 
How about another cell with nothing but the number. Or, weeknum*3 idea. Or,
edit/replace
 
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
 
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
 
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.
 
This formula using 1 April 2008 yields 31
=INT((TODAY()-DATE(2008,4,1))/7)
31.00

Using 2008,4,15 gives 29
 
Back
Top