View Single Post
  #3   Report Post  
Posted to microsoft.public.excel.newusers
joeu2004 joeu2004 is offline
external usenet poster
 
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