Home > Steve > Computing

Computing

Steve's
logo

Snippets

With no particular rationale, I have decided to present any computing "things" I know that I feel are worthy of disseminating.


Office

My desktop is fairly old and it runs Office 2003.

Office/Excel

ISO Week Number

More recent versions of Excel than 2003 have an inbuilt function that returns the ISO Week Number of a date. That week number increments on Mondays and is week 1 on the week containing the first Thursday (the majority of the days in week 1 are in the Thursday year).
=ISOWEEKNUM(date)

Excel 2003 provides a week number function (again it can be set to increment on Mondays), where Jan 1 is always week 1 and weeks may not all have 7 days.
=WEEKNUM(date,2)

The following code resides entirely in one cell, so certain functions get called multiple times. This could be avoided by using multiple cells for the function, but I wanted to use it weekly in a calendar sheet, so one cell was all I would allot.
The word "date" (in lower case) should be replaced by the relevant cell number.


If you want a 2-digit (leading zero) display on a numeric cell you can Format Custom as "00".

As a numeric value (use if you want to autofilter on a range of values)(aligned with text version 2 lines below):

          =WEEKNUM(date-WEEKDAY(date,3)+3,2)-IF(WEEKDAY(DATE(YEAR(date-WEEKDAY(date,3)+3),1,1),3)>3,1,0) 

As text with a leading zero when less than 10:

=RIGHT(100+WEEKNUM(date-WEEKDAY(date,3)+3,2)-IF(WEEKDAY(DATE(YEAR(date-WEEKDAY(date,3)+3),1,1),3)>3,1,0),2) 

First Tuesday in the Year

Returns the date (eg "2025-01-07 Tue" given a year "2025"):
Assume year, eg 2025, in cell B2.

=DATE(B2,1,8)-WEEKDAY(DATE(B2,1,5)) 

My custom cell date format is "yyyy-mm-dd ddd".

If you want another day of the week, alter the last "5" to a value in the range 1..7.


RANK.AVG

Excel 2003 only has the function RANK. That gives the ranking of a value in a list of N items from 1..N, but if there are several copies of one value all are given the value of the first occurence.

There are two modes, ascending and descending, controlled by the 3rd parameter.
The function is: =RANK(value,array,mode) . For example given the (sorted) array "1,2,2,3" (They do NOT need to be sorted.) RANK(value,array_ref,0) returns array equivalent "4,2,2,1", whilst RANK(value,array_ref,1) returns array equivalent "1,2,2,4". (Mode 0 has biggest value = 1, Mode 1 has smallest value = 1.

You can create the RANK.AVG function using =(array_size)+RANK(value,array,0)-RANK(value,array,1) and =(array_size)+RANK(value,array,1)-RANK(value,array,0) .


Home > Steve > Computing
Last updated 2025-04-21
This page is part of http://www.stocton.org/
Email: webmaster@stocton.org