Formula to determine the Zodiac sign

mgana · Jul 23, 2003

I need a formula (no VBA) that will display the name of the zodiac sign in column B based on the date of birth (dd/mm/yyyy) entered in column A according to the following criteria:

Dec. 22 - Jan. 19 - Capricorn
Jan. 20 - Feb. 17 - Aquarius
Feb. 18 - Mar. 19 - Pisces
March 20 - April 19 - Aries
April 20 - May 19 - Taurus
May 20 - June 20 - Gemini
June 21 - July 21 - Cancer
July 22 - Aug. 22 - Leo
Aug 23 - Sept. 21 - Virgo
Sept. 22 - Oct. 22 - Libran
Oct. 23 - Nov. 21 - Scorpio
Nov. 22 - Dec. 21 - Sagittarius

Can this be done?

Brian from Maui · Jul 23, 2003

mgana said:
I need a formula (no VBA) that will display the name of the zodiac sign in column B based on the date of birth (dd/mm/yyyy) entered in column A according to the following criteria:

Dec. 22 - Jan. 19 - Capricorn
Jan. 20 - Feb. 17 - Aquarius
Feb. 18 - Mar. 19 - Pisces
March 20 - April 19 - Aries
April 20 - May 19 - Taurus
May 20 - June 20 - Gemini
June 21 - July 21 - Cancer
July 22 - Aug. 22 - Leo
Aug 23 - Sept. 21 - Virgo
Sept. 22 - Oct. 22 - Libran
Oct. 23 - Nov. 21 - Scorpio
Nov. 22 - Dec. 21 - Sagittarius

Can this be done?

Haven't tested...too late

Book1

A

B

C

D

1

Jan.20

Aquarius

21-Feb

Pisces

2

Feb.18

Pisces

3

Mar.20

Aries

4

Apr.20

Taurus

5

May.20

Gemini

6

Jun.21

Cancer

7

Jul.22

Leo

8

Aug.23

Virgo

9

Sep.22

Libran

10

Oct,23

Scorpio

11

Nov,22

Sag

12

Dec,22

Capricorn

Sheet1

mgana · Jul 23, 2003

I need a formula within which the names are embedded. There should be no need for an external reference of names. Could you help me?

Brian from Maui · Jul 23, 2003

mgana said:
I need a formula within which the names are embedded. There should be no need for an external reference of names. Could you help me?

No VBA, which I don't know anyway, no external references, and names embedded. Maybe someone else can think of a way, I can't!

x-amount · Jul 23, 2003

Use nested if's in the formula

Ie. split the first and second 6 dates with the first If, and then the next row of ifs splits the first, second, third and fourth 3 dates etc

Mudface · Jul 23, 2003

mgana said:
I need a formula within which the names are embedded. There should be no need for an external reference of names.

Why?

mgana · Jul 23, 2003

I know it will have to use nesed IFs. Would anyone care to give the formula?

eliW · Jul 23, 2003

Hi Mgana,

Here is my solution using VLOOKUP function.

The beauty of this (if I may say so) is that you can enter the birthdate including the year and get the Zodiac symbol:

The formula is:

=VLOOKUP(VALUE(TEXT(A1,"m.d")),$D$1:$E$13,2,1)

Eli

<CENTER><TABLE ALIGN=CENTER BORDER=1><TR><TD BGCOLOR=#0C266B COLSPAN=6>Microsoft Excel - Book1_______________Running: xl97 : OS = Windows (32-bit) 4.10</TD></TR><TR><TD BGCOLOR=#D4D0C8 COLSPAN=6>(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp</TD></TR><TR><TD ALIGN=CENTER COLSPAN=2 BGCOLOR=White>B1</TD><TD ALIGN=CENTER BGCOLOR=#D4D0C8 >=</TD><TD COLSPAN=3 BGCOLOR=White>=VLOOKUP(VALUE(TEXT(A1,"m.d")),$D$1:$E$13,2,1)</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER> </TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>A</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>B</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>C</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>D</TD><TD BGCOLOR=#D4D0C8 ALIGN=CENTER>E</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>1</TD><TD BGCOLOR=#FFFF00 ALIGN=Center VALIGN=BOTTOM >21/12/46</TD><TD BGCOLOR=#FF00FF ALIGN=Center VALIGN=BOTTOM ><A HREF=javascript:alert("=VLOOKUP(VALUE(TEXT(A1,"m.d")),$D$1:$E$13,2,1)")>Sagittarius</A></TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >1</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Capricorn </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>2</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >1.21</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Aquarius </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>3</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >2.18</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Pisces </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>4</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >3.2</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Aries </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>5</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >4.2</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Taurus </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>6</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >5.2</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Gemini </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>7</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >6.21</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Cancer </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>8</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >7.22</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Leo </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>9</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >8.23</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Virgo </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>10</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >9.22</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Libran </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>11</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >10.23</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Scorpio </TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>12</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >11.22</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Sagittarius</TD></TR><TR><TD WIDTH=5 BGCOLOR=#D4D0C8 ALIGN=CENTER>13</TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFFFFF> </TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >12.22</TD><TD BGCOLOR=#FFCC00 ALIGN=Center VALIGN=BOTTOM >Capricorn </TD></TR><TR><TD COLSPAN=6>Sheet1</TD></TR></TABLE>
To see the formula in the cells just click on the cells hyperlink

The above image was automatically generated by [HtmlMaker V1.23]
If you want this code, <A HREF=mailto:corosuke@chan.co.jp>click here</A> and Colo will email the file to you.
This code was graciously allowed to be modified: by <A HREF=mailto:ivanmoala@xtra.co.nz>Ivan F Moala</A> All credit to Colo
</CENTER>

BAlGaInTl · Jul 23, 2003

I figured out the problem using a formula similar to this... (unfinished)

=IF(AND(MONTH(A1)&DAY(A1)>="120",MONTH(A1)&DAY(A1)<="217"),"Aquarius",IF(AND(MONTH(A1)&DAY(A1)>="218",MONTH(A1)&DAY(A1)<="319"),"Pisces",IF(AND(MONTH(A1)&DAY(A1)>="320",MONTH(A1)&DAY(A1)<="419"),"Aries",IF(AND(MONTH(A1)&DAY(A1)>="420",MONTH(A1)&DAY(A1)<="519"),"Taurus",IF(AND(MONTH(A1)&DAY(A1)>="520",MONTH(A1)&DAY(A1)<="620"),"Gemini",IF(AND(MONTH(A1)&DAY(A1)>="621",MONTH(A1)&DAY(A1)<="721"),"Cancer",IF(AND(MONTH(A1)&DAY(A1)>="722",MONTH(A1)&DAY(A1)<="822"),"Leo","Capricorn")))))))

However, the inherent problem is that excel only allows for 7 nested IF statements I believe. Here we are up to 8 and it fails... I wasn't even done yet. If someone can figure out how to use this method and limit it to 7 IFs, you could do it.

I think your only bet is to use a table as others have suggested. You could always put your table on another sheet and hide it if you don't want people to see it.

Aladin Akyurek · Jul 23, 2003

sdible said:
I figured out the problem using a formula similar to this... (unfinished)...

It's even not worth trying...

I think your only bet is to use a table as others have suggested. You could always put your table on another sheet and hide it if you don't want people to see it.

You bet. If you don't have a table anywhere, you can convert Eli's proposal into a formula that includes the table as an array constant...

=VLOOKUP(--TEXT(A1,"m.d"),{1.1,"Capricorn";1.2,"Aquarius";2.18,"Pisces";3.2,"Aries";
4.2,"Taurus";5.2,"Gemini";6.21,"Cancer";7.22,"Leo";8.23,"Virgo";
9.22,"Libran";10.23,"Scorpio";11.22,"Sagittarius";12.22,"Capricorn"},2,1)

where A1 houses a birth date like 3/1/1969.

Formula to determine the Zodiac sign

mgana

Board Regular

Excel Facts

Brian from Maui

MrExcel MVP

mgana

Board Regular

Brian from Maui

MrExcel MVP

x-amount

Active Member

Mudface

MrExcel MVP

mgana

Board Regular

eliW

Well-known Member

BAlGaInTl

Well-known Member

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Formula to determine the Zodiac sign

Board Regular

Excel Facts

MrExcel MVP

Board Regular

MrExcel MVP

Active Member

MrExcel MVP

Board Regular

Well-known Member

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock