Range reference and return

[PritiOne]

Hello. Hope all had a wonderful holiday!

I am looking for help on the folowing:

I have 3 defined ranges, say Range 1, Range 2, and Range 3 defined on a sheet in a workbook.
On another sheet "Input Sheet" I have a list input by a user.

I need to know how to write a formula (could be elsewhere in the workbook) so that if the user chooses at lease 1 "product" from Rang 1, and at least one "product" from Range 2, and at least one "product" from Range 3 on the "Input Sheet" a result would follow as "1% discount".

Please let me know if you need any further information & Thanks in advance for the help!!!

 

[Aladin Akyurek]

...
=IF(COUNT(MATCH(NewProdList,MedMains,0))*COUNT(MATCH(NewProdList,Connect,0))*COUNT(MATCH(NewProdList,Assessment,0))*(COUNT(MATCH(NewProdList,NotEligible,0))=0),1%,0%)
...

The logic the formula embodies is:

Anything from NewProdList is in MedMains and in Connect and in Asseesment, but not in NotEligible ==> 1%

Otherwise 0%.

 

[PritiOne]

Here's my joined formula:

=IF(COUNT(MATCH(NewProdList,MSDiscMain,0))*COUNT(MATCH(NewProdList,Connect,0))*COUNT(MATCH(NewProdList,Assessment,0))*(COUNT(MATCH(NewProdList,NotEligible,0))=0)*COUNT(MATCH(ExpList,MSDiscMain,0))*COUNT(MATCH(ExpList,Connect,0))*COUNT(MATCH(ExpList,Assessment,0))*(COUNT(MATCH(ExpList,NotEligible,0))=0),10%,0%)

 

[PritiOne]

Got It!

Sorry to keep osting.. as I am figuring it out.

As always you have been a life saver!!!!

All the best, PritiOne

 

[tusharm]

Do you know why the formula works?

Will you remember when, at some point in the future, you have to maintain the worksheet?

Will someone else who has to maintain your work know?

Here are four issues to consider:

(1) Unlike others, I tend to shy away from implicit conversions and an understanding of esoteric XL conventions.

A simplified version of the problem you have can be stated as:

If in list1 and in list2 and not in list3 then...

where the last in the 'not eligible' check.

In XL, the MATCH function will yield a #N/A error if the value is *not* found in the table being searched. So, your problem becomes

if not ISNA(match1) and not isna (match2) and isna(match3)

One could code that directly. IMO, it would be the most obvious and straightforward solution that also happens to be a *normal* formula:

=IF(AND(NOT(ISNA(MATCH(H3,MSDiscMain,FALSE))),NOT(ISNA(MATCH(H3,Connect,0))),ISNA(MATCH(H3,NotEligible,0)))),10%,0%)

IMO, the above is far more understandable to someone (even yourself at a later date) than
=IF(COUNT(MATCH(NewProdList,MSDiscMain,0))*COUNT(MATCH(NewProdList,Connect,0))*(COUNT(MATCH(NewProdList,NotEligible,0))=0),10%,0%)

Why do I prefer the AND() approach? Just figure out how much knowledge about XL is required to understand the array formula. Contrast that with how *little* is left unstated in the AND() formula.

(2) Even if one was hell bent on saving a few key strokes...

Apply two of the basic laws of logic:
a = not not a
and
(not a) and (not b) = not (a or b)
to your condition to get:
if not (isna(match1) or isna (match2) or not isna(match3))

Hence, in this case, your problem collapses to another *normal* condition:
NOT(OR(ISNA(MATCH(H3,MSDiscMain,FALSE)),ISNA(MATCH(H3,Connect,0)),NOT(ISNA(MATCH(H3,NotEligible,0)))))

(3) On a slightly different note:

If you want, leverage the fact that in XL any expression within an arithmetic formula can be any sub-formula, however complex. This is overlooked by many, even most experts. So, one doesn't need
if(xxx,0.1,0); just 0.1*xxx will suffice -- or to be picky, 0.1*N(xxx).

Effectively, and using the original AND() formula, you would get the *regular* formula
=0.1*AND(NOT(ISNA(MATCH(H3,MSDiscMain,FALSE))),NOT(ISNA(MATCH(H3,Connect,0))),ISNA(MATCH(H3,NotEligible,0)))

(4) Something else that very few use, or even know -- and I have just started experimenting with -- is that an XL formula can be broken into multiple lines and can include spaces. [One caveat: since I shy away from overly complex formulas and also have just started playing with this, I don't know what, if anything, this technique breaks.] And, yes, below is exactly how the formula looks like in the XL cell or formula bar!

=IF(AND(
       NOT(ISNA(MATCH(H3,MSDiscMain,0))),
       NOT(ISNA(MATCH(H3,Connect,0))),
       ISNA(MATCH(H3,NotEligible,0))),
    10%,0%)

What can be easier to understand than this?

 

[Aladin Akyurek]

Do you know why the formula works?

Will you remember when, at some point in the future, you have to maintain the worksheet?...

A simple advise like: "Add a comment to the first formula cell that COUNT() ignores #N/A values MATCH() would return," should suffice.

 

[tusharm]

DUH! I was so fixated on ISNA that I overlooked ISNUMBER, which *might* make the formula more transparent.

=IF(AND(
       ISNUMBER(MATCH(H3,MSDiscMain,FALSE)),
       ISNUMBER(MATCH(H3,Connect,0)),
       ISNA(MATCH(H3,NotEligible,0))),
    10%,0%)

{snip}

=IF(AND(
       NOT(ISNA(MATCH(H3,MSDiscMain,0))),
       NOT(ISNA(MATCH(H3,Connect,0))),
       ISNA(MATCH(H3,NotEligible,0))),
    10%,0%)

What can be easier to understand than this?

 

[fairwinds]

tusharm

Are you not missing that we need to match an array of values into different arrays, not a single value?

 

[tusharm]

I assume from the OP's silence that you are correct. I interpreted "If at least 1 product from MedMains, AND Connect, AND Assessment are ordered then customer receives a 10% discount." to mean that a single product had to be in each of the lists and not in the final list.

However, even if we assume that at least one product has to be from each of the lists (but not necessarily the same product) and no product can be from the 'not eligible' list, which is what your array formula solves, I would still use a more elaborate layout.

Why? It makes good business sense. Given the single array formula, the best the OP can conclude is whether the user (customer?) gets a discount or not. A very reasonable response from a customer not getting a discount would be, "Can you tell me why?" The OP's answer would be...what? I have no clue? Duh? Because. Because the computer said so? Because I said so?" S/he sure as heck cannot tell which rule was not satisfied/violated and how.

Now, imagine the list of products being checked is in column A. Then, each of the corresponding cells in columns B, C, D, and E contain a boolean indicating whether the product satisfies/violates the 4 separate rules. A final cell can compute the discount. And, yes, the OP can use 'out of the way columns' for the intermediate results. That way, only if one has to look up the details does one have to scroll to those columns.

An additional bonus of this layout is the simplicity of verifying whether or not the XL worksheet really supports the business process.

For practically no extra work there is both an increase in transparency in how XL supports the business process and both the OP's company and the customer get valuable additional information.

tusharm

Are you not missing that we need to match an array of values into different arrays, not a single value?

 

[PritiOne]

Thank you all - for your help and comments. You guys sure are smart!!!

 

[fairwinds]

tusharm:
On your last post I totally agree.

PritiOne:
Hope you got
http://www.mrexcel.com/board2/viewtopic.php?t=118361&highlight=
to work. Else post back in this thread.