A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Reload this Page Problem with nested logical formula.
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Problem with nested logical formula.


« Previous Thread | Next Thread »

 
 
Thread Tools Display Modes
  #1  
Old September 26th, 2005, 02:26 AM
Bill R
external usenet poster
  
Posts: n/a
Default Problem with nested logical formula.
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
  #2  
Old September 26th, 2005, 03:21 AM
Rowan
external usenet poster
  
Posts: n/a
Default
Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,I F(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU2 9)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint" ,ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)), BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF( AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU 29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU 29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
  #3  
Old September 26th, 2005, 03:43 AM
duane
external usenet poster
  
Posts: n/a
Default
I think this - but note you do not have a result for not meeting the last if
condition - maybe the last if is not needed.

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,
IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU 29)),SL2!E19,
IF(AND(NOT(ISBLANK(BU26 )),BX26="Joint",ISBLANK(BU29)),JL2!E19,
IF(AND(NOT(ISBLANK(BU26 )),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,
IF(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK (BU29))),'JL&AH2'!E19,
IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19))))) )

"Rowan" wrote:

Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,I F(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU2 9)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint" ,ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)), BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF( AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU 29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU 29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
  #4  
Old September 26th, 2005, 03:52 AM
Bill R
external usenet poster
  
Posts: n/a
Default
Thanks Rowan, It almost works. Everything works except checking both boxes.
That gives me a #Ref error.

"Rowan" wrote:

Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,I F(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU2 9)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint" ,ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)), BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF( AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU 29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU 29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
  #5  
Old September 26th, 2005, 04:06 AM
Bill R
external usenet poster
  
Posts: n/a
Default
I made an error when copying your formula. When entered correctly it works
perfectly. Thank you very much.

"Rowan" wrote:

Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19,I F(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(BU2 9)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Joint" ,ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26)), BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,IF( AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK(BU 29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK(BU 29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
  #6  
Old September 26th, 2005, 04:22 AM
Rowan
external usenet poster
  
Posts: n/a
Default
You're welcome.

Bill R wrote:
I made an error when copying your formula. When entered correctly it works
perfectly. Thank you very much.

"Rowan" wrote:


Maybe like this:

=IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29))),AH!E19 ,IF(AND(NOT(ISBLANK(BU26)),BX26="Single",ISBLANK(B U29)),SL2!E19,IF(AND(NOT(ISBLANK(BU26)),BX26="Join t",ISBLANK(BU29)),JL2!E19,IF(AND(NOT(ISBLANK(BU26) ),BX26="Single",NOT(ISBLANK(BU29))),'SL&AH2'!E19,I F(AND(NOT(ISBLANK(BU26)),BX26="Joint",NOT(ISBLANK( BU29))),'JL&AH2'!E19,IF(AND(ISBLANK(BU26),ISBLANK( BU29)),NONE3!E19,""))))))

Hope this helps
Rowan

Bill R wrote:

Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The formula
should pull the correct payment from the correct sheet based on the options
selected by a customer who will put an "x" in one, or both of two boxes and
choosing either "single" or "joint" life Ins. I have put several hours into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH! E19),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBL ANK(BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(B X26="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(I SBLANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))) ,'SL&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Jo int"),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19) ))

Thanks.
  #7  
Old September 26th, 2005, 04:36 AM
Biff
external usenet poster
  
Posts: n/a
Default
Hi!

Try this:

=IF(AND(BU26="",BU29""),AH!E19,IF(AND(BU26"",B X26="Single",BU29=""),SL2!E19,IF(AND(BU26"",BX26 ="Joint",BU29=""),JL2!E19,IF(AND(BU26"",BX26="Si ngle",BU29""),'SL&AH2'!E19,IF(AND(BU26"",BX26= "Joint",BU29""),'JL&AH2'!E19,IF(AND(BU26="",BU29 =""),None3!E19,""))))))

Biff

"Bill R" wrote in message
...
Why can't I get the formula below to work? There are 6 sheets in my
worksheet, each of which calculates a payment with different options. The
formula is located on a 7th sheet that is in the same workbook. The
formula
should pull the correct payment from the correct sheet based on the
options
selected by a customer who will put an "x" in one, or both of two boxes
and
choosing either "single" or "joint" life Ins. I have put several hours
into
this one formula; any suggestions? Please.

=IF(IF(AND(ISBLANK(BU26),NOT(ISBLANK(BU29)),AH!E19 ),
(IF(AND(NOT(ISBLANK(BU26)),(BX26="Single"),ISBLANK (BU29)),SL2!E19),IF(AND((NOT(ISBLANK(BU26))),(BX26 ="Joint"),ISBLANK(BU29)),JL2!E19),IF(AND((NOT(ISBL ANK(BU26))),(BX26="Single"),NOT(ISBLANK(BU29))),'S L&AH2'!E19),IF(AND(NOT(ISBLANK(BU26)),(BX26="Joint "),NOT(ISBLANK(BU29))),'JL&AH2'!E19),
(IF(AND(ISBLANK(BU26),ISBLANK(BU29)),NONE3!E19)))

Thanks.
 



« Previous Thread | Next Thread »

Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Formula problem Steved Worksheet Functions 2 July 5th, 2005 10:53 PM
Formula problem Giff General Discussion 5 March 31st, 2005 11:23 PM
Excel Lookup formula problem RodG Worksheet Functions 2 April 16th, 2004 05:32 AM
Excel XP Nested If Function Problem DaffyD® Worksheet Functions 2 March 6th, 2004 06:46 PM
Formula Bar Problem... Cloaked Setting up and Configuration 3 October 4th, 2003 01:04 AM


All times are GMT +1. The time now is 11:52 AM.

Feed Icon - Contact Us - OfficeFrustration excel & word help home - FAQ - Links - Privacy Statement - Top

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