Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 25th 09, 04:28 PM posted to microsoft.public.excel.newusers
tom tom is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 570
Default #Value! Error in Nested IF Function

I have to IF Functions that work independently and I am trying to combine
them into one function. My desired end result is either the text "BUY" or
"SELL".

1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")

If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.

What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error

This is really a circular reference error as I want the true result of these
2 nested IF functions to be displayed. I just do not know how to do it.

OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY",""), show the true result - "BUY", "SELL" or blank.

I hope this makes sense.
Any help is appreciated.

Regards,
Tom




  #2   Report Post  
Old March 25th 09, 05:22 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2007
Posts: 175
Default #Value! Error in Nested IF Function

Hi
try this

=IF(AND(B13="High",I13="S"),"SELL",IF(AND(C13="Low ",I13="L"),"BUY",""))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Tom" wrote:

I have to IF Functions that work independently and I am trying to combine
them into one function. My desired end result is either the text "BUY" or
"SELL".

1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")

If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.

What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error

This is really a circular reference error as I want the true result of these
2 nested IF functions to be displayed. I just do not know how to do it.

OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY",""), show the true result - "BUY", "SELL" or blank.

I hope this makes sense.
Any help is appreciated.

Regards,
Tom



  #3   Report Post  
Old March 25th 09, 06:18 PM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default #Value! Error in Nested IF Function

"Tom" wrote:
What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error


Because you cannot OR() text values, which is the result of each IF() function.


1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")
If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.


But combine them how? Actually, don't focus on "combine" (syntax). Simply write in English under what conditions you want "BUY", "SELL" and blank. Your example is unclear.

Perhaps the following is close to what you want:

=IF(AND($B$13="High",$I$13="S"),"SELL",
IF(AND($C$9="Low",$I$9="L"),"BUY",""))

Now, however, that I have presumed a priority between the two conditions. A priori, the sell and buy conditions are not mutually exclusive. So the following might give different results:

=IF(AND($C$9="Low",$I$9="L"),"BUY",
IF(AND($B$13="High",$I$13="S"),"SELL",""))

Only you can decide which is better. Or perhaps that is not the right logic at all.


----- original message -----

"Tom" wrote in message ...
I have to IF Functions that work independently and I am trying to combine
them into one function. My desired end result is either the text "BUY" or
"SELL".

1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")

If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.

What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error

This is really a circular reference error as I want the true result of these
2 nested IF functions to be displayed. I just do not know how to do it.

OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY",""), show the true result - "BUY", "SELL" or blank.

I hope this makes sense.
Any help is appreciated.

Regards,
Tom

  #4   Report Post  
Old March 25th 09, 06:19 PM posted to microsoft.public.excel.newusers
tom tom is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 570
Default #Value! Error in Nested IF Function

Joe:

THANK YOU!

The first formula works as I want are mutually execulsive.

However, I still do not understand why it worked. My guess is I am just not
writing out the order of execution processes.

My understanding of if functions a
=if(logical_test,value_if_true, value_if_false)

In your first example, you pulled this apart by

=IF(AND($B$13="High",$I$13="S"),"SELL",
then you created another IF statement for the false of:
IF(AND($C$9="Low",$I$9="L"),"BUY","")).

Thanks!

Tom



"JoeU2004" wrote:

"Tom" wrote:
What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error


Because you cannot OR() text values, which is the result of each IF() function.


1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")
If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.


But combine them how? Actually, don't focus on "combine" (syntax). Simply write in English under what conditions you want "BUY", "SELL" and blank. Your example is unclear.

Perhaps the following is close to what you want:

=IF(AND($B$13="High",$I$13="S"),"SELL",
IF(AND($C$9="Low",$I$9="L"),"BUY",""))

Now, however, that I have presumed a priority between the two conditions. A priori, the sell and buy conditions are not mutually exclusive. So the following might give different results:

=IF(AND($C$9="Low",$I$9="L"),"BUY",
IF(AND($B$13="High",$I$13="S"),"SELL",""))

Only you can decide which is better. Or perhaps that is not the right logic at all.


----- original message -----

"Tom" wrote in message ...
I have to IF Functions that work independently and I am trying to combine
them into one function. My desired end result is either the text "BUY" or
"SELL".

1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")

If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.

What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error

This is really a circular reference error as I want the true result of these
2 nested IF functions to be displayed. I just do not know how to do it.

OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY",""), show the true result - "BUY", "SELL" or blank.

I hope this makes sense.
Any help is appreciated.

Regards,
Tom


  #5   Report Post  
Old March 26th 09, 01:02 AM posted to microsoft.public.excel.newusers
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2007
Posts: 2,059
Default #Value! Error in Nested IF Function

"Tom" wrote:
However, I still do not understand why it worked.
[....]
My understanding of if functions a
=if(logical_test,value_if_true, value_if_false)


That's right. IF() is a function, not a "statement", in Excel. So the first "logical_test" and "value_if_true" is:

=IF(AND($B$13="High",$I$13="S"), "SELL"

The first "value_if_false" can be any expression. In your case, it happens to be an IF() function. So the form of the solution I provided is:

=if(logical_test1, value_if_true1, if(logical_test2, value_if_true2, value_if_false2))

If logical_test1 is TRUE, first IF() returns value_if_true1. If logical_test1 is FALSE, IF() returns value_if_false1 (not shown), which is an IF(). In evaluating value_if_false1, if logical_test2 is TRUE, the second IF() function returns value_if_true2; otherwise, it returns value_if_false2.

But it is important to understand that the use of IF() functions is not limited to this nested form. As I said, IF() is simply a function. You can use it wherever a value could be used.

For example, suppose you want the following: if B1<10, then B1*A1, otherwise B1*C1. That can be written two ways (among others):

=if(B1<10, B1*A1, B1*C1)

=B1 * if(B1<10, A1, C1)

HTH.


----- original message -----

"Tom" wrote:
Joe:

THANK YOU!

The first formula works as I want are mutually execulsive.

However, I still do not understand why it worked. My guess is I am just not
writing out the order of execution processes.

My understanding of if functions a
=if(logical_test,value_if_true, value_if_false)

In your first example, you pulled this apart by

=IF(AND($B$13="High",$I$13="S"),"SELL",
then you created another IF statement for the false of:
IF(AND($C$9="Low",$I$9="L"),"BUY","")).

Thanks!

Tom



"JoeU2004" wrote:

"Tom" wrote:
What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error


Because you cannot OR() text values, which is the result of each IF() function.


1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")
If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.


But combine them how? Actually, don't focus on "combine" (syntax). Simply write in English under what conditions you want "BUY", "SELL" and blank. Your example is unclear.

Perhaps the following is close to what you want:

=IF(AND($B$13="High",$I$13="S"),"SELL",
IF(AND($C$9="Low",$I$9="L"),"BUY",""))

Now, however, that I have presumed a priority between the two conditions. A priori, the sell and buy conditions are not mutually exclusive. So the following might give different results:

=IF(AND($C$9="Low",$I$9="L"),"BUY",
IF(AND($B$13="High",$I$13="S"),"SELL",""))

Only you can decide which is better. Or perhaps that is not the right logic at all.


----- original message -----

"Tom" wrote in message ...
I have to IF Functions that work independently and I am trying to combine
them into one function. My desired end result is either the text "BUY" or
"SELL".

1st Function - IF(AND($B$13="High",$I$13="S"),"SELL","")

If cell B13 has the text "High" and I13 has the text "S", then generate the
word "SELL". If this is false, then leave the cell blank.

2nd Function - IF(AND($C$9="Low",$I$9="L"),"BUY","")
If cell C13 has the text "low" and I13 has the text "L", then generate the
word "BUY". If this is false, then leave the cell blank.

Issue - Combine these 2 independently working functions.
Desired result when combined is either "BUY", "SELL", or blank.

What I tried:
OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY","")

Result a #VALUE! Error

This is really a circular reference error as I want the true result of these
2 nested IF functions to be displayed. I just do not know how to do it.

OR(IF(AND($B$17="High",$I$17="S"),"SELL",""),IF(AN D($C$17="Low",$I17="L"),"BUY",""), show the true result - "BUY", "SELL" or blank.

I hope this makes sense.
Any help is appreciated.

Regards,
Tom




  #6   Report Post  
Old November 10th 17, 06:20 AM
Junior Member
 
First recorded activity by ExcelBanter: Nov 2017
Posts: 1
Smile Similar issue

I am also facing similar issue but due to blanks in my source cells.

https://docs.google.com/spreadsheets...g5A/edit#gid=0

I have shared the link of my document. Please see if you could help me out.


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
Nested IF Function returns #NAME? Error bw Excel Worksheet Functions 8 October 29th 08 12:35 PM
Nested IF error kd Excel Worksheet Functions 3 March 28th 07 02:44 AM
Nested IF error Greg Wilson Excel Discussion (Misc queries) 0 March 28th 07 12:08 AM
3 nested IFs causes error Bob Excel Worksheet Functions 6 January 29th 07 07:43 PM
Nested IF AND OR function Inexplicable error MichaelC Excel Worksheet Functions 7 August 22nd 05 10:46 PM


All times are GMT +1. The time now is 05:43 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017