My first Excel formula

Me and my Dad wanted a spreadsheet that lists the date and the number of house points I got on that date.  My Dad wrote most of the instructions here but I helped find out what to do at each stage and also I can recreate this spreadsheet whenever I want to.

Here’s our first attempt.

Date	House Points
01 February 2010	1
02 February 2010	2
03 February 2010	1
04 February 2010	8
05 February 2010	3
06 February 2010	0
07 February 2010	0
08 February 2010	2
09 February 2010	1
10 February 2010	3

This is how we did the dates:

	A
1	01 February 2010
2	= A1+1
3	= A2+1

And so on…

But this meant we had all the weekends included and we would be putting zeroes in the weekends.  This is ok for working out the total number of house points but it massively lowers the average as we are always counting weekends but we always have zeroes there.  For example, in 15 school days I got 30 house points.  The average is therefore 30/15 = 2.  But in our sheet we would have 3 weekends included so our average would be 30/21 = 1.43.

It is clear that we don’t want to include weekends.  This is what we did next.

In column A we start off with a date in cell A1.  Cells A2 and below will be 1 day later than the previous cell in the column.  But we only want weekdays.  Here is the formula:

	A
1	01 February 2010
2	=IF(WEEKDAY(A1+1,2) =6, A1+3, A1+1)

First we need to use the WEEKDAY function.  This is defined as:

WEEKDAY(serial_number,return_type)

Return_type is a number that determines the type of return value.

Return_type	Number returned
1 or omitted	Numbers 1 (Sunday) through 7 (Saturday). Behaves like previous versions of Microsoft Excel.
2	Numbers 1 (Monday) through 7 (Sunday).
3	Numbers 0 (Monday) through 6 (Sunday).

We want to use return type 2 as we want Monday to Friday and as soon as we get a day number of 6 we know we have reached Saturday.  We could also use a return type of 3.  But using 1 would mean we need to change the formula to look out for 1 meaning Sunday and 7 meaning Saturday.

The formula says that if by adding 1 to the previous day we would get a Saturday then we should add 3 to the previous day and make it a Monday.  Otherwise we just add 1 because adding 1 is still a weekday.

The condition is:

WEEKDAY(A1+1,2) =6

If this is true then we use the value:

A1+3

If the condition is false we use the value:

A1+1

We then wanted to see the name of the weekday, this is easy…

	A	B
1	01 February 2010	=TEXT(A1,”ddd”)
2	=IF(WEEKDAY(A1+1,2) =6, A1+3, A1+1)	=TEXT(A2,”ddd”)

We are showing the shorthand day name.  Our final sheet looks like this:

01 February 2010	Mon	2
02 February 2010	Tue	2
03 February 2010	Wed	1
04 February 2010	Thu	1
05 February 2010	Fri	1
08 February 2010	Mon	3
09 February 2010	Tue	2
10 February 2010	Wed	1
11 February 2010	Thu	3
12 February 2010	Fri	1
15 February 2010	Mon	3
16 February 2010	Tue	0
17 February 2010	Wed	7
18 February 2010	Thu	1
19 February 2010	Fri	2