If Array

[Bigpond News]

Can anyone tell me how to put more than 9 if statements into a single
formula ? I can get 9 om a row but not 9
for instance if i want to detect the numbers of days in a mont on a large
spreadsheet I may enter
=if(a1="Jan",31,if(a1="Feb",28,if(a1="Mar",31,0))) and so on, I can get it
to work upto September with it working then it comes up with an error,

PS sorry also posted this on Excel worksheet functions page by mistake
Thank


Peter

 

[Guest]

BigPond,

Try another way of doing this - have a look at vlookup in help

Another neat way which works if the source cell (A1) is actually a date is:

Make sure that the analysis toolpak is loaded, then in your destination cell
you can use the formula
=EOMONTH(A1,0) and set the format to Custom, DD then you'll get 31 for Jan,
etc.

If you need this as a number to work with then use =day(eomonth(a1,0)) which
will give you the last day of the month (31 for Jan etc) but will also work
if the source date is in a leap year.

HTH

 

[Robert]

Hi Peter,

Try the folow:

Make two columns with all the IF variables, i.e:
A B
Jan 31
Feb 28
March 31
April 30

Subsequently you can use the following formula

A1= Feb

=SUMIF(A1:B4,A1,B1:B4)
Result: 28

Hope this helps.

Rgds,
Robert

 

[Bob Phillips]

Hi Peter,

This will get it for you

=DAY(DATE(YEAR(TODAY()),VLOOKUP(LEFT(A1,3),{"Jan",2;"Feb",3;"Mar",4;"Apr",5;
"May",6;"Jun",7;"Jul",8;"Aug",9;"Sep",10;"Oct",11;"Nov",12;"Dec",13},2,FALSE
),0))

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)