[mso] Re: Anyone see the error? (Excel XP)

Robert,

Even with the addition of a 'E' in the last 'ISRROR' (which was what caused
the #NAME error), this will give a "Y" for all years which cannot be
evaluated and a "N" for all valid years, as far as I can see (by using your
[corrected] formula and testing with different years).

I would think you want to first test for a valid year (i.e. one which
consists of numerals and not queries) then, only if it's valid, evaluate it
with the series of MOD functions to show whether it's a leap year or not.
In pseudocode:

IF [Year string is not numeric]
THEN [write "N"]
ELSE
        IF [Year string is a leap year]
        THEN [write "Y"]
        ELSE [write "N"]
        ENDIF
ENDIF

The test for a leap year might be done with OR() and AND() or with further
nested IF()s - remember that you can nest up to 7 IF()s.

When writing formulae, especially a formula which is going to be repeated in
many cells, the following should be considered:

        1) An IF() function will not attempt to evaluate the ELSE part if
the first statement is true, so it makes sense to have it decide the most
important criterion first (in this case, whether or not the string can be
evaluated).  I believe (but would stand corrected by the more experienced)
that an AND() will only evaluate to the first FALSE, so it would seem
sensible to put the most important criterion ('is the year divisible by 4?')
first.

        2) The formula should accomplish its end in as few characters as
possible.  (Yours does contain several redundant brackets inside the VALUE()
functions).

I hope this helps.

Andrew



> -----Original Message-----
> From: mso-bounce@xxxxxxxxxxxxx 
> [mailto:mso-bounce@xxxxxxxxxxxxx] On Behalf Of Robert Carneal
> Sent: 26 July 2004 22:37
> To: mso@xxxxxxxxxxxxx
> Subject: [mso] Anyone see the error? (Excel XP)
> 
> If B15 contains "1950-12-20" in text, and D15 contains:
> =IF(OR(ISERROR(AND(MOD(VALUE((LEFT(B15,4))),4)=0,MOD(VALUE((LE
FT(B15,4))
> ),100)<>0)),ISRROR(MOD(VALUE((LEFT(B15,4))),400)=0)),"Y","N")
> 
> Does anyone see my mistake, the error? It currently evaluates 
> to #NAME?
> error, and I didn't want that. Basically, I am trying to it 
> to tell me if the year is indeed a leap year. I discussed 
> this before, and I thought this was the formula suggested to 
> me. I wrote it out on paper, hoping the error would jump at 
> me, but gracious, I do not see it.
> 
> Even if B15 were to contain "????-08-30" I would not want 
> that "#NAME?"
> I would prefer it display a simple "N".
> 
> Thank you.
> 
> Robert
> 
> 

*************************************************************
You are receiving this mail because you subscribed to mso@xxxxxxxxxxxxx or 
MicrosoftOffice@xxxxxxxxxxxxxxxx

To send mail to the group, simply address it to mso@xxxxxxxxxxxxx

To Unsubscribe from this group, send an email to 
mso-request@xxxxxxxxxxxxx with the word "unsubscribe" (without the quotes) in 
the subject line.

Or, visit the group's homepage and use the dropdown menu.  This will also allow 
you to change your email settings to digest or vacation (no mail).
http://www.freelists.org/webpage/mso

To be able to use the files section for sharing files with the group, send a 
request to mso-moderators@xxxxxxxxxxxxx and you will be sent an invitation with 
instructions.  Once you are a member of the files group, you can go here to 
upload/download files:
http://www.smartgroups.com/vault/msofiles
*************************************************************

Other related posts: