Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default 3 nested IFs causes error

I have the following formula:

=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info")))

As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.
Thanks for the help,
Bob

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,440
Default 3 nested IFs causes error

Hi Bob,

<Excel allows up to 7 nested IF statements

A common misunderstanding. Excel allows 7 nested functions, no matter what sort. You reached that limit in your first formula
version.
There usually are many possible workarounds. Start he

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bob" wrote in message ...
|I have the following formula:
|
|
=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info")))
|
| As soon as I add one more IF statement to the front of the formula:
|
|
=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"))))
|
| I get an error (and the FIND function is highlighted)! Can anyone tell me
| why? I thought Excel allows up to 7 nested IF statements. My formula has
| only 3.
| Thanks for the help,
| Bob
|


  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 35,218
Default 3 nested IFs causes error

It's not 7 nested IF's. It's 7 levels of nesting--no matter what the function.


IF(
ISERROR(
LEFT(C8,
FIND("--",C8,10)-2)),
HYPERLINK("#"&
CELL("address",
INDEX('Activity

Bob wrote:

I have the following formula:

=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info")))

As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.
Thanks for the help,
Bob


--

Dave Peterson
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 733
Default 3 nested IFs causes error

Bob wrote...
....
As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0) ),"",
IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.


Others have already mentioned that the limit is 7 nested function
calls - ANY function calls. As for your particular formula, you could
try rewriting it, perhaps as

=IF(OR(ISBLANK($B8),ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)) ),"",
HYPERLINK("#"&CELL("Address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(IF(COUNTIF(C8,"??????????*--*"),LEFT(C8,FIND("--",C8,10)-2),C8),
'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))

Note that only your first reference to cell C8 is column-absolute,
$C8, while all the others are fully relative, C8. Is that really what
you want?

Also, FIND("--",$C8,10)-2 looks like a bug because it ends the
substring *2* chars before the first hyphen. For example, if C8 were

1234567890--xyz

FIND("--",$C8,10)-2 would return 9, and LEFT($C8,FIND("--",$C8,10)-2)
would return "123456789" rather than "1234567890". Is that really what
you want?

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default 3 nested IFs causes error

Niek,
Thanks for the clarification. Now I understand why I received the error.
Bob


"Niek Otten" wrote:

Hi Bob,

<Excel allows up to 7 nested IF statements

A common misunderstanding. Excel allows 7 nested functions, no matter what sort. You reached that limit in your first formula
version.
There usually are many possible workarounds. Start he

http://www.j-walk.com/ss/excel/usertips/tip080.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Bob" wrote in message ...
|I have the following formula:
|
|
=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info")))
|
| As soon as I add one more IF statement to the front of the formula:
|
|
=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(C8,'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
| Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
| Desc.'!$A$2:$A$43,0),1)),"Info"))))
|
| I get an error (and the FIND function is highlighted)! Can anyone tell me
| why? I thought Excel allows up to 7 nested IF statements. My formula has
| only 3.
| Thanks for the help,
| Bob
|





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default 3 nested IFs causes error

Dave,
Thanks for the clarification. Now I understand why I received the error
message.
Bob


"Dave Peterson" wrote:

It's not 7 nested IF's. It's 7 levels of nesting--no matter what the function.


IF(
ISERROR(
LEFT(C8,
FIND("--",C8,10)-2)),
HYPERLINK("#"&
CELL("address",
INDEX('Activity

Bob wrote:

I have the following formula:

=IF(ISNA(VLOOKUP($C8,INDIRECT(VLOOKUP($B8,PhaseLoo kup,$BM$9*2,0)),2,0)),"",IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info")))

As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,INDIRECT(V LOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)),"",IF(ISE RROR(LEFT(C8,FIND("--",C8,10)-2)),HYPERLINK("#"&CELL("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(C8,'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"),HYPERLINK("#"&CEL L("address",INDEX('Activity
Desc.'!$E$2:$E$43,MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity
Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.
Thanks for the help,
Bob


--

Dave Peterson

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Bob is offline
external usenet poster
 
Posts: 972
Default 3 nested IFs causes error

Harlan,
Thanks for suggesting using the OR function.
Also, the string I'm searching for the "--" in looks like this:

C8 = 1234567890 -- xyz

So LEFT(C8,FIND("--",C8,10)-2) would return 1234567890, which is what I want.
Thanks again,
Bob


"Harlan Grove" wrote:

Bob wrote...
....
As soon as I add one more IF statement to the front of the formula:

=IF(ISBLANK($B8),"",IF(ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0) ),"",
IF(ISERROR(LEFT(C8,FIND("--",C8,10)-2)),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(C8,'Activity Desc.'!$A$2:$A$43,0),1)),"Info"),
HYPERLINK("#"&CELL("address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(LEFT(C8,FIND("--",C8,10)-2),'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))))

I get an error (and the FIND function is highlighted)! Can anyone tell me
why? I thought Excel allows up to 7 nested IF statements. My formula has
only 3.


Others have already mentioned that the limit is 7 nested function
calls - ANY function calls. As for your particular formula, you could
try rewriting it, perhaps as

=IF(OR(ISBLANK($B8),ISNA(VLOOKUP($C8,
INDIRECT(VLOOKUP($B8,PhaseLookup,$BM$9*2,0)),2,0)) ),"",
HYPERLINK("#"&CELL("Address",INDEX('Activity Desc.'!$E$2:$E$43,
MATCH(IF(COUNTIF(C8,"??????????*--*"),LEFT(C8,FIND("--",C8,10)-2),C8),
'Activity Desc.'!$A$2:$A$43,0),1)),"Info"))

Note that only your first reference to cell C8 is column-absolute,
$C8, while all the others are fully relative, C8. Is that really what
you want?

Also, FIND("--",$C8,10)-2 looks like a bug because it ends the
substring *2* chars before the first hyphen. For example, if C8 were

1234567890--xyz

FIND("--",$C8,10)-2 would return 9, and LEFT($C8,FIND("--",$C8,10)-2)
would return "123456789" rather than "1234567890". Is that really what
you want?


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
error bars display Amy Charts and Charting in Excel 1 December 23rd 06 12:22 AM
Nested subtotal display error phil Excel Discussion (Misc queries) 3 October 24th 06 09:16 AM
error message when opening excel 2000 Lem Excel Discussion (Misc queries) 3 August 31st 06 11:06 PM
nested if statement returns #value error [email protected] Excel Discussion (Misc queries) 3 August 10th 06 04:27 PM
Formula Problem - interrupted by #VALUE! in other cells!? Ted Excel Worksheet Functions 17 November 25th 05 06:18 PM


All times are GMT +1. The time now is 02:03 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"