Tricky excel exercise

OSB · 7 Nov 2007 at 20:51

Hi guys, i'm having difficulty in trying to make a spreadsheet in excel. Basically it needs to show that the 13ths in the months fall on fridays more often than any other day. So it needs to be able to count up the number of time each day lands on the 13th in a 400 year period (which is the period of the gregorian calender). Now i can use excel, but i can sem to think how to get it to do this.

Any ideas???

OSB

Hxc · 7 Nov 2007 at 20:59

I think this would be a job for the COUNTIF() function, if I understand what you say correctly.

stumpy187 · 7 Nov 2007 at 21:01

COUNTIF was the first thing that came to mind but i can't actually think of anyway to work it out.

Hxc · 7 Nov 2007 at 21:03

It very much depends how the data is organized, thinking about it. It's going to be a bit more complex than the standard countif(h3>H4) though ;P

James07 · 7 Nov 2007 at 21:06

Its something like Countif cell [d2] reads [blah] then cell [d2] = [######]

Something like that, did it for gcse, all my gcse coursework is at DanMc07's house on a hdd, email him he may be nice enough to find you the file, its gcse>ict>unit 3 me thinks, get him to email me if you email him, i deff have what your looking for on there... My cw got me 2 B's so cant be too bad

Let me know.. email me or him if interested and see what we can do for you, best bet is to email him, bypasses asking me and getting me to ask and so on..

Morba · 7 Nov 2007 at 21:08

bleh, found a quicker way, give me a min :]

actually, google shows a better way.

Andy100 · 7 Nov 2007 at 21:28

Bit long winded but...

Date in column A, =DAY(A#) in column B, Day of the week in column C, =IF(B3=13,C3," ") in column D.

Copy this down the 146000 rows you need then pivot the results by day.

Lol, there's got to be an easier way than that though.

BurningHorizons · 7 Nov 2007 at 21:33

I've worked it out 01/01/1901 - 31/12/2007 and get the following totals for which day the 13th falls on

Monday 184
Tuesday 183
Wednesday 184
Thursday 184
Friday 183
Saturday 184
Sunday 182

So to answer your question over the last 100 odd years, it's completely even, give or take, which I would expect.

Email in Trust if you would like the spreadsheet.

OSB · 8 Nov 2007 at 08:31

Yeah over 100 years it may be even but the gregorian calendar has a period of 400 years so that when you take a 400 year period, with all its gap years, into account it does actually occur more often on a friday. but only slightly. I have managed to do this now but it was rather long winded. Thanks for your help guys.

OSB