Formula to determine the Zodiac sign

[mgana]

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?

 

[mgana]

Thanks a lot Aladin! It works perfectly now with all date formats.Really appreciate it.

As for sdibles formula, it dosen't seem to be working. Thanks for trying anyway.

Thanks you all!

 

[naramapak]

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.

I'm a real beginner in excel,this zodiac formula works great .but I need to be able to explain how it works.Can anyone enlighten me

 

[Yogi Bear]

I got a solution

Hey there,

here is your solution:

A B C

1 Begin Date End Date Zodiac

2

3 12-22 1-19 Capricorn

4 1-20 2-17 Aquarius

5 2-18 3-19 Pisces

.
.
.
.
.
14 11-22 12-21 Sagittarius

15 Please enter your birth date below (MM-DD)

16 USER DEFINED

17 Your zodiac sign is:

18 see formula below


=========================


Basically, create a lookup table exactly as above. Cell A18 contains the only formula you need.


=+LOOKUP(A16,A3:A14,C3:C14)

where:

A16 = the reference cell where the user will be asked to enter their birthday.
A3:A14 = the lookup range
C3:C14 = the results range

Have fun with it!


Yog Bear

 

[eliW]

I'm a real beginner in excel,this zodiac formula works great .but I need to be able to explain how it works.Can anyone enlighten me

To naramapak,

Oh, from almost 3 years distance here it is again…. I must confess that this was one of my best solutions that I made since, and I am proud of it until now.

The main problem in this thread by “Mgana” was how to convert any birth date (which is actually a number) to something that may be used in LOOKUP formulas as a continuous number which represents the MONTH and the DAY in the month – no matter what is the actual YEAR (as only MONTH and DAY define the zodiac sign)

The idea was to use TEXT() formula that can take a date and give it any desired format needed. So

=TEXT(date,”m.dd”)

will convert any date to a number that its left portion represents the MONTH, while the decimal portion represents the day in the month. This was the heart of the solution!

After this anything went easy. I had to convert the text format to numeric value (which can be used as continuous reference in VLOOKUP formula, and then build a table to fit the numbers to the zodiac sign.

I must admit that Aladin has taken all the glory when he suggested incorporating an ARRAY table into the same formula instead of using external table as I did, and afterwards he also corrected my original format to “m.dd” instead of “m.d” – these of course had improved the formula and it is fine with me.

Ok, I hope that the explanation is clear enough,

Eli

 

[sir.jazz.kc]

Now for the BIGGIE!... How can I modify this to find the Chinese Zodiac? The "dd.mm.yyyy" is not working. It should be a simple transition. Any help?

Also, what are the limitations of a formula array within one cell?

Sir ~Jazz~

 

[Biz]

Aladin ..Nice vlookup formula

 

[CASSYCOOPER]

Easy - here goes!

=LOOKUP(--TEXT(A1,"mdd"),{101,121,220,321,421,521,621,724,824,924,1024,1123,1222;
"Capricorn","Aquarius","Pisces","Aries","Taurus","Gemini","Cancer","Leo","Virgo","Libra","Scorpio","Sagittarius","Capricorn"})


Where date is dd/mm/yyyy in cell A1

 

[Carel Smith]

In office 356 it doesn't work
As soon as I use the -- I get a value error

 

[Peter_SSs]

it doesn't work

Welcome to the MrExcel board!

What doesn't work? (There has been more than one suggestion in the thread )

 

[Carel Smith]

As I explained when using the double - I get a value error, in other words while I try to force the text value to convert to a number value using text formula with the "--" I get an error

=--TEXT (A1,"m.dd")
Results = #VALUE