|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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,
Thank
Peter
|
|
Hello:
That's the limit. In most cases you can solve the problem
with a lookup table. Look in help for an example. It should
work for you. List the months in 12 rows and list
the number of days in the next column.
As an example list the months from A1:A12, list the
number of days in B1:b12. Assume the month you want
the date for is in D1 then in E1 enter:
=VLOOKUP(D1,A1:B12,2,FALSE)
If d1 contains: Nov
e1 will return 30
Pieter Vandenberg
Bigpond News <Pbraun1[ at ]bigpond.net.au> wrote:
: 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,
: Thank
: Peter
|
|
Bigpond News schrieb:
[Quoted Text] > 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,
>
> Thank
>
>
> Peter
>
>
If your data allow it, another possibility would be:
if(cond1;X;Y)+if2(cond2;Z;A)+...
this way you can if until the last judgement
drefsa
|
|
drefsa <drefsa[ at ]gmx.de> wrote in
news:#V4pFabgGHA.4932[ at ]TK2MSFTNGP03.phx.gbl:
[Quoted Text] > Bigpond News schrieb:
>> 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,
>>
>> Thank
>>
>>
>> Peter
>>
>>
> If your data allow it, another possibility would be:
> if(cond1;X;Y)+if2(cond2;Z;A)+...
> this way you can if until the last judgement
> drefsa
>
Peter,
Just a suggestion, if you've not yet been able to crack this one
satisfactorily: use Excel's VLOOKUP function.
1) Somewhere (could be on any worksheet, even in a separate Excel file) set
up your table of data in the range A1: B12. Column A will hold the name of
the months, sorted alphabetically (V IMPORTANT IF YOU WANT THIS TO WORK!),
and column B will hold the number of days for the relevant month, eg:
Col A
Apr
Aug
Dec
Feb
(etc, etc)
Col B
30
31
31
28
(you'll need to change this for leap years, or put in some logic to test
for same)
(etc, etc)
2) Now, set up your main worksheet e.g. range X1:Y4
Col X (the months you need to use for your calculations)
Dec
Jan
Nov
Feb
Col Y
=VLOOKUP(X1,$A$1:$B$12,2,FALSE)
=VLOOKUP(X2,$A$1:$B$12,2,FALSE)
=VLOOKUP(X3,$A$1:$B$12,2,FALSE)
=VLOOKUP(X4,$A$1:$B$12,2,FALSE)
If this is all OK, you will see displayed in COL Y:
31
31
30
28
Explanation about what is going on in the brackets in COL Y:
(apologies if I'm explaining stuff you already know - this might be read by
someone not already familiar with it)
* X1, X2, etc: this is the cell holding the month that you want to look for
in the table;
* $A$1:$B:$12: This is the absolute address of your table of data;
* 2: if you find a match in column 1 of your table, display the contents of
the cell in column 2 of the table;
* FALSE: you want an EXACT match between Col X and Col A (or have #N/A
displayed if no exact match can be found). The alternative is "TRUE" -
display the largest value that is less than or equal the value from Col X.
You can leave this blank, and Excel works as if you had used "TRUE".
It took me a while to get my head around the various LOOKUP functions the
first time I came across them, but I found that playing around with them
for a while helped me to understand (never could follow M/soft's
explanations in the "Help" facility).
I mentioned earlier that you could put your table in a separate file. This
means that "$A$1:$B:$12" becomes something like
'[table file name.xls]Sheet1'!$A$1:$B:$12
(including the ' and !). This might seem complicated, but it does mean that
you can share this (and similar reference data) with other worksheets. It
also means that when the next Leap Year comes along, you only need to make
one change, and it should be fairly easy to find where the change is
needed!!
I hope that has not been too long-winded, but feel free to come back with
any queries.
Good luck!
Merlin
Garden of England
(turn left at the compost heap)
|
|
Merlin <kmoore9788R-E-M-O-V-E[ at ]aol.com> wrote in
news:Xns97E674661B5Emerlinwashere[ at ]80.5.182.99:
[Quoted Text] > drefsa <drefsa[ at ]gmx.de> wrote in
> news:#V4pFabgGHA.4932[ at ]TK2MSFTNGP03.phx.gbl:
>
>> Bigpond News schrieb:
>>> 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,
>>>
>>> Thank
>>>
>>>
>>> Peter
>>>
>>>
>> If your data allow it, another possibility would be:
>> if(cond1;X;Y)+if2(cond2;Z;A)+...
>> this way you can if until the last judgement
>> drefsa
>>
>
> Peter,
>
> Just a suggestion, if you've not yet been able to crack this one
> satisfactorily: use Excel's VLOOKUP function.
>
snip
>
> I hope that has not been too long-winded, but feel free to come back
> with any queries.
>
> Good luck!
>
> Merlin
> Garden of England
> (turn left at the compost heap)
>
Sorry guys - I spotted Pieter Vandenberg's earlier response (proposing a
VLOOKUP approach) only after I posted my own contribution.
It was late, and I guess the brain cells were getting tired.
Best wishes,
Merlin
|
|
This is a way
Always good to know
letter number in the alphabet
a
=IF(OR(B2="a",B2="b",B2="c",B2="d",B2="e",B2="f"),IF(B2="a",1,IF(B2="b",2,I
F
(B2="c",3,IF(B2="d",4,IF(B2="e",5,6))))),IF(OR(B2="g",B2="h",B2="i",B2="j",
B
2="k",B2="l"),IF(B2="g",7,IF(B2="h",8,IF(B2="i",9,IF(B2="j",10,IF(B2="k",11
,
12))))),IF(OR(B2="m",B2="n",B2="o",B2="p",B2="q",B2="r"),IF(B2="m",13,IF(B2
=
"n",14,IF(B2="o",15,IF(B2="p",16,IF(B2="q",17,18))))),IF(OR(B2="s",B2="t",B
2
="u",B2="v",B2="w"),IF(B2="s",19,IF(B2="t",20,IF(B2="u",21,IF(B2="v",22,23)
)
)),IF(B2="x",24,IF(B2="y",25,IF(B2="z",26,"error - type a letter")))))))
z 26
f 6
b 2
d 4
j 10
t 20
58 error - type a letter
Formula IF's-26 in one equation
"Merlin" <kmoore9788R-E-M-O-V-E[ at ]aol.com> escribió en el mensaje
news:Xns97E6E0F89A508merlinwashere[ at ]80.5.182.99...
[Quoted Text] > Merlin <kmoore9788R-E-M-O-V-E[ at ]aol.com> wrote in
> news:Xns97E674661B5Emerlinwashere[ at ]80.5.182.99:
>
> > drefsa <drefsa[ at ]gmx.de> wrote in
> > news:#V4pFabgGHA.4932[ at ]TK2MSFTNGP03.phx.gbl:
> >
> >> Bigpond News schrieb:
> >>> 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,
> >>>
> >>> Thank
> >>>
> >>>
> >>> Peter
> >>>
> >>>
> >> If your data allow it, another possibility would be:
> >> if(cond1;X;Y)+if2(cond2;Z;A)+...
> >> this way you can if until the last judgement
> >> drefsa
> >>
> >
> > Peter,
> >
> > Just a suggestion, if you've not yet been able to crack this one
> > satisfactorily: use Excel's VLOOKUP function.
> >
>
> snip
>
> >
> > I hope that has not been too long-winded, but feel free to come back
> > with any queries.
> >
> > Good luck!
> >
> > Merlin
> > Garden of England
> > (turn left at the compost heap)
> >
>
> Sorry guys - I spotted Pieter Vandenberg's earlier response (proposing a
> VLOOKUP approach) only after I posted my own contribution.
>
> It was late, and I guess the brain cells were getting tired.
>
> Best wishes,
>
> Merlin
|
|
|