Excel returning a False response-CodePudding

=IF(B12<>"",(IF(U12="Subscription","Subscribe",IF(U12="T.E.H.","Subscribe",IF(U12="ESA","Subscribe",IF(U12="Perpetual","Buy",IF(U12=" "," ")))))))

I get a "False" value in the cell with this formula in it when the value in U12 is not one of the options and I want to get a blank Appreciate any help Thanks

CodePudding user response：

Change this: IF(U12=" ", " ") to this: IF(U12=" ", " ", ""). Here's a demo in Google Sheets, but the formulas are the same. https://docs.google.com/spreadsheets/d/10iFqq0PNt9VhGKMPLCxsI1df0x4nDSxCaCGSdEEktO8/edit#gid=0

However, I don't think this is exactly right. For one thing, it looks like this clause:

IF(U12=" ", " ")

is only there to try to generate the blank you are looking for. If that's the case, then change it to:

IF(U12=" ", " ", "")

However, I think I would probably use some array constants, something like this:

=IF(B12<>"", CHOOSE(SUM(--(U12={"Subscription","T.E.H","ESA"}))   IF(U12="Perpetual", 2)  1, "", "Subscribe", "Buy"))

To break this down a little, let me reformat so it's easier to see:

=IF(
  B12 <> "", 
  CHOOSE(
    SUM(--(U12={"Subscription","T.E.H","ESA"}))   
    IF(U12="Perpetual", 2)   1
    "", "Subscribe", "Buy"
  )
)

I'm going to use CHOOSE, which takes an index number N and a list of choices, and it returns the Nth element in the choice list. To generate the index, I'm going to use a couple of techniques. First look at this:

U12 = {"Subscription", "T.E.H", "ESA"})

This is going to test U12 against all of the choices in the list, and return an array of TRUE/FALSE values. If U12 matches any of the elements in the array constant, then one of those values will be TRUE. We use -- to coerce that array from TRUE/FALSE to 0/1, and then we use SUM to get the array into a single value. It will either be 0 if U12 doesn't match any of the options, or 1 if it does.

Then I'm going to use a standard IF to check if U12 = Perpetual, and return 2 if it does. Adding that result to the previous sum will give us a final number that is either 0, 1 or 2.

CHOOSE is 1 indexed, meaning that it expects the index to be 1 or greater, so we add 1 to the number we just generated and pass it to CHOOSE along with a list of options.

The advantage here is that if the Subscribe options change you can just change the list, instead of having to alter a bunch of nested IFs. Also, if you need to support multiple buy options, you can do it with a similar construction:

SUM(--(U12 = {"Subscription", "T.E.H", "ESA"}))  
IF(SUM(--(U12 = {"Perpetual", "Buy Now"})), 2)   1

and if you needed to add return values just keep extending:

CHOOSE(
  SUM(--(U12 = {"Subscription", "T.E.H", "ESA"}))  
  IF(SUM(--(U12 = {"Perpetual", "Buy Now"})), 2)  
  IF(SUM(--(U12 = {"Release", "Dropping"})), 3)   1,
  "", "Subscribe", "Buy", "Sell"
)

If you think this is something that might need to be maintained or the options might change, I would set it up like this, as opposed to nested IFs.

CodePudding user response：

Is this a little clearer?

=IF(B12=""," ",IfError(VLookup(U12,{"ESA","Subscribe";"Perpetual","Buy";"Subscription",Subscribe";"T.E.H.","Subscribe"},2,false)," "))

This is really just a table lookup, hence the use of VLookup. The lookup array is a two dimensional array in curly brackets - the commas and semicolons are very important for defining a 2x4 array for VLookup. The IFError function tells how to return your desired " " result if the lookup fails.