More syntax headaches

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Nick Xylas - 23 Jan 2007 14:32 GMT

Once again, I need the help of the clever people on this group in
parsing a formula. Here's the setup:

Cell A1 is the cell the formula goes in.

If cell AA7 equals the text value "YES" (itself the result of a
calculated formula), then A1=1.
If cell AA7 is zero, and AB7 contains an asterisk (again the result of
a calculated formula), then A1 is left blank.
If AA7 is zero and AB7 is blank, the A1=0
If AA7 contains any other numeric value besides zero, then that value
is reproduced in A1.

AB7 either contains an asterisk or is left blank, so testing whether
AB7="*" or testing whether it is nonblank will both work equally as
well.

Thanking you in advance.

Reply to this Message

KC Rippstein - 23 Jan 2007 14:51 GMT

=IF(AA7="YES",1,IF(AA7=0,IF(AB7="",0,""),IF(ISNUMBER(AA7),AA7,NA()))

If it helps you, you can break out these nested IF formulas vertically using
Alt+Enter and indenting spaces. Sometimes that's easier to visualize the
logic.
=IF(AA7="YES",
1,
IF(AA7=0,
IF(AB7="",
0,
"" ),
IF(ISNUMBER(AA7),
AA7,
NA() )
)

> Once again, I need the help of the clever people on this group in
> parsing a formula. Here's the setup:
[quoted text clipped - 14 lines]
>
> Thanking you in advance.

Reply to this Message

Pete_UK - 23 Jan 2007 14:57 GMT

Try this:

=IF(AA7=0,IF(AB7="*","",IF(AB7="",0,"not
specified")),IF(AA7="YES",1,IF(ISNUMBER(AA7),AA7,"not specified")))

You can see that there are a few situations that your description does
not cover.

Hope this helps.

Pete

> Once again, I need the help of the clever people on this group in
> parsing a formula. Here's the setup:
[quoted text clipped - 14 lines]
>
> Thanking you in advance.

Reply to this Message

Rate this thread: