Hi,

I have a list of over 6,000 staff records in column A, with a date of birth in the format dd.mm.yyyy against each record (in column B). I have been asked to report on which horoscope is the most common out of the staff list. Does anyone know of a set formula I could use to calculate this, or of a cunning way of working this out? :)

Thanks in advance!

Gra-

Off the top of my head, I can only think of a VLOOKUP table, listing the
first day of each Sign down one column and the corresponding Sign down the
column to the right, and using the TRUE option in the formula........

=VLOOKUP(A1,YourAstrologyTable,2,TRUE)

But you have to be careful of anyone born on the "cusps" (the exact start or
end dates of each sign) because depending on the time of day they were born,
they may be the previous or latter sign rather than the one they appear to
be. Only the calculation of a real Horoscope Chart for the time and place
they were born will prove the difference.

hth
Vaya con Dios,
Chuck, CABGx3



"Gra" wrote:


Hi,

I have a list of over 6,000 staff records in column A, with a date of
birth in the format dd.mm.yyyy against each record (in column B). I
have been asked to report on which horoscope is the most common out of
the staff list. Does anyone know of a set formula I could use to
calculate this, or of a cunning way of working this out? :)

Thanks in advance!

Gra-




--
Gra

arrange data in date order, and mark the horoscope dates on it, and note
the row numbers where there is a change, ie it might be 235 then 402

402 - 235 is number with first star sign or


make a lookup table, all 366 dates of year and name of starsign, call
the table starsign

if birth date is in A1, put in B1

=vlookup(A1,starsign,2)

and copy down all 4000 records


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574151

Set table below and call "Horoscope" (year in table is arbitrary)

01/01/2006 Capricorn
20/01/2006 Aquarius
20/02/2006 Pisces
21/03/2006 Aries
21/04/2006 Taurus
22/05/2006 Gemini
23/06/2006 Cancer
24/07/2006 Leo
24/08/2006 Virgo
24/09/2006 Libra
24/10/2006 Scorpio
23/11/2006 Sagittarius
23/12/2006 Capricorn

Assume birth date in A1 then in B1:

=VLOOKUP(DATE(2006,MONTH(A1),DAY(A1)),Horoscope,2, 1)

Copy down

N.B. Year in DATE = year in your HOROSCOPE table

HTH


"robert111" wrote:


arrange data in date order, and mark the horoscope dates on it, and note
the row numbers where there is a change, ie it might be 235 then 402

402 - 235 is number with first star sign or


make a lookup table, all 366 dates of year and name of starsign, call
the table starsign

if birth date is in A1, put in B1

=vlookup(A1,starsign,2)

and copy down all 4000 records


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574151

Well done Toppers..........it works good!

You got any goodies on Biorhythms? I used to have a neat one in Symphony
but have never tried in Excel.

Vaya con Dios,
Chuck, CABGx3



"Toppers" wrote:

Set table below and call "Horoscope" (year in table is arbitrary)

01/01/2006 Capricorn
20/01/2006 Aquarius
20/02/2006 Pisces
21/03/2006 Aries
21/04/2006 Taurus
22/05/2006 Gemini
23/06/2006 Cancer
24/07/2006 Leo
24/08/2006 Virgo
24/09/2006 Libra
24/10/2006 Scorpio
23/11/2006 Sagittarius
23/12/2006 Capricorn

Assume birth date in A1 then in B1:

=VLOOKUP(DATE(2006,MONTH(A1),DAY(A1)),Horoscope,2, 1)

Copy down

N.B. Year in DATE = year in your HOROSCOPE table

HTH


"robert111" wrote:


arrange data in date order, and mark the horoscope dates on it, and note
the row numbers where there is a change, ie it might be 235 then 402

402 - 235 is number with first star sign or


make a lookup table, all 366 dates of year and name of starsign, call
the table starsign

if birth date is in A1, put in B1

=vlookup(A1,starsign,2)

and copy down all 4000 records


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574151

Chuck,

Thank you for the feedback. Hope the OP feels the same way!

Re biorhythms .... not my scene ... nor are horoscopes!

"CLR" wrote:

Well done Toppers..........it works good!

You got any goodies on Biorhythms? I used to have a neat one in Symphony
but have never tried in Excel.

Vaya con Dios,
Chuck, CABGx3



"Toppers" wrote:

Set table below and call "Horoscope" (year in table is arbitrary)

01/01/2006 Capricorn
20/01/2006 Aquarius
20/02/2006 Pisces
21/03/2006 Aries
21/04/2006 Taurus
22/05/2006 Gemini
23/06/2006 Cancer
24/07/2006 Leo
24/08/2006 Virgo
24/09/2006 Libra
24/10/2006 Scorpio
23/11/2006 Sagittarius
23/12/2006 Capricorn

Assume birth date in A1 then in B1:

=VLOOKUP(DATE(2006,MONTH(A1),DAY(A1)),Horoscope,2, 1)

Copy down

N.B. Year in DATE = year in your HOROSCOPE table

HTH


"robert111" wrote:


arrange data in date order, and mark the horoscope dates on it, and note
the row numbers where there is a change, ie it might be 235 then 402

402 - 235 is number with first star sign or


make a lookup table, all 366 dates of year and name of starsign, call
the table starsign

if birth date is in A1, put in B1

=vlookup(A1,starsign,2)

and copy down all 4000 records


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=574151