Solved If a range contains one of few strings and another, numbers

August 7, 2017 at 13:14:23
Specs: Windows 10
Hello.

This is a question similar to a previous one that I had, but with a little more logic required to determine the output. I am referencing another sheet called 'Comparison'. I'm looking through cells K94 through K125 and through K136 through K167.

Between cells K125 and through K136: Cells could be either EQUAL, VARIANCE or MISSING.
Between cells K94 through K125: Cells may have numeric values (rounding errors) that should be less than $3.

I got help here previously to return the following values:
A value of EQUAL only if ALL cells from K136 through K167 are marked 'EQUAL'.
A value of VARIANCE if ANY of the cells from K136 through K167 are marked 'VARIANCE'.
A value of NEEDS REVIEW if NONE of the above criteria are met (such as all missing, some missing, but not those that have variance).

Formula for output required with above conditions:
=IF(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),"Equal",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW"))

I would to add the logic:
IF the output is 'VARIANCE' BUT
IF any of the cells between cells K94 through K125 is a rounding difference anywhere between +$3.00 through -$3.00 I'd like the formula to return 'EQUAL'.

I will need to extrapolate this formula over a number of other ranges, but would like it to ideally work with this one first.


See More: If a range contains one of few strings and another, numbers

Reply ↓  Report •

✔ Best Answer
August 9, 2017 at 07:09:49
Here are 2 versions that should work for you:

=IF(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),"EQUAL",
IF(AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3") - COUNTIF(Comparison!K94:K125,">3"))=ROWS(K94:K125)),"EQUAL",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW")))

=IF(OR(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),
AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3")-COUNTIF(Comparison!K94:K125,">3"))=ROWS(K94:K125))),"EQUAL",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
August 7, 2017 at 17:47:17
Try this:

=IF(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),"EQUAL",
IF(AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3") - COUNTIF(Comparison!K94:K125,">3"))>0),"EQUAL",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW")))

Explanation of new clause:

IF(AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3") - COUNTIF(Comparison!K94:K125,">3"))>0),"EQUAL",

The AND means that both Logical Tests must be True in order for the IF to be True

Logical Test 1: COUNTIF(Comparison!K136:K157,"=VARIANCE")>0

That should be self-explanatory.

Logical Test 2: (COUNTIF(Comparison!K94:K125,">-3")-COUNTIF(Comparison!K94:K125,">3"))>0)

COUNTIF(Comparison!K94:K125,">-3")

This will count all values that are greater than -3.

COUNTIF(Comparison!K94:K125,">3")

This will count all values that are greater than 3.

If you subtract the "greater than 3" result from the "greater then -3" result, the overall result is the count of values between -3 and 3.

If that count is greater than 0, then there is at least 1 value in the range that is between -3 and 3 and Logical Test 2 will be True. When both Logical Tests are True, the IF will be True and return Equal.


Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
August 8, 2017 at 06:28:22
Thank you much for your help, DerbyDad03. The formula seems to only return: EQUAL regardless of whether there are numbers in cells K94:K125 greater or lesser than either + or - $3.

Within part 2 of logical test 2: should the sign be "<" instead of ">"? However the first logical test does not
seem to produce the result either.

"" If you subtract the "greater than 3" result from the "greater then -3" result, the overall result is the count of values between -3 and 3.
If that count is greater than 0, then there is at least 1 value in the range that is between -3 and 3 and Logical Test 2 will be True. When both Logical Tests are True, the IF will be True and return Equal." ""

I'm sorry if I may not have described the requirement suitably. We're trying to not flag rounding errors between + or - $3 within cells K94:K125 as a VARIANCE. Inputting the formula I get the result EQUAL if there is a cells that has a value of either +9 or -5.

The statement


Reply ↓  Report •

#3
August 8, 2017 at 19:56:20
First, let's deal with this question, just as a general explanation as how to count the number of occurrences of values that are between 2 other values:

"Within part 2 of logical test 2: should the sign be "<" instead of ">"?

No. As I tried to explain earlier, you if what to count how many values are between 2 values, you first count all the numbers that are greater than the lower value and then subtract the count of all the values above the higher value.

For example, if we want to find out how many values in A1:A5 are between -3 and 3 we would use:

=COUNTIF(A1:A5,">-3") - COUNTIF(A1:A5,">3")

Let's say the values are: -4, -1, 2, 4, 5

=COUNTIF(A1:A5,">-3") will return 4 because of -1, 2, 4 & 5

=COUNTIF(A1:A5,">3") will return 2 because of 4 & 5

If we subtract 2 from 4, we get 2 which is correct because only -1 & 2 are between -3 & 3.


Now as for your most recent requirements, perhaps I don't understand what you are asking for. Let me tell you what I think you want and you can tell me where (if) I went wrong.

You started with 3 requirements:

1 - A value of EQUAL only if ALL cells from K136 through K167 are marked 'EQUAL'.
2 - A value of VARIANCE if ANY of the cells from K136 through K167 are marked 'VARIANCE'.
3 - A value of NEEDS REVIEW if NONE of the above criteria are met

My original formula met those requirements.

Then you said:

4 - I would to add the logic:
IF the output is 'VARIANCE' BUT
IF any of the cells between cells K94 through K125 is a rounding difference anywhere between +$3.00 through -$3.00 I'd like the formula to return 'EQUAL'.

Since you use the word add, I read that to mean that you now have a 4th condition. I read that to mean that you are now looking for 2 different results related to having VARIANCE in any cell in K136:K167, as follows:

If any cell in K136:K167 contains VARIANCE, then the result should be VARIANCE unless at the same time that any cell in K136:K167 contains VARIANCE, any cell in K94:K125 contains a value that is between -3 & 3. In that case (at least 1 VARIANCE and at least 1 value between -3 and 3) the result should be EQUAL.

As an example, this is what I *think* you want, but only over a much shorter range:

Based on requirement #1, this should return EQUAL

K94: 7
K95: -4

K136: EQUAL
K137: EQUAL

Based on Requirement #2, this should return VARIANCE

K94: 7
K95: -4

K136: VARIANCE
K137: EQUAL

Based on Requirement #3, this should return NEEDS REVIEW

K94: 7
K95: -4

K136:
K137: EQUAL

Based on Requirement #4, this should return EQUAL

K94: 7
K95: 0

K136: VARIANCE
K137: EQUAL

If those examples are correct, then the latest formula that I posted meets all four of your requirements, at least in the sheet that I tested it in.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
August 9, 2017 at 05:29:40
Good morning. Thank you again.

The way the requirements are worded- they sound right. But the example of Requirement # 4 does not appear right. Let's look at a couple variations of requirement # 4, if we may.

1)

Based on Requirement #4, this should return VARIANCE [since one of the numbers: 7 is outside of the numeric range of (-3,3)]

K94: 7
K95: 0
K136: VARIANCE
K137: EQUAL

2)

Based on Requirement #4, this should return EQUAL [since 2 and -1 are within the numeric range of (-3,3)]

K94: 2
K95: -1
K136: VARIANCE
K137: EQUAL

The verbiage you reiterated to describe both these examples, I think? Please let me know of your thoughts, when possible.


Reply ↓  Report •

#5
August 9, 2017 at 06:26:27
This example does not meet the new requirement (#4) as you previously stated it.

K94: 7
K95: 0
K136: VARIANCE
K137: EQUAL

You said: "IF any of the cells between cells K94 through K125 is a rounding difference anywhere between +$3.00 through -$3.00 I'd like the formula to return 'EQUAL'."

Note your use of the word any. In this example, K95 is that any cell that is between -3 & 3.

Based on your most recent examples, you appear to want to return EQUAL when all cells in the range contain values between -3 & 3, even if VARIANCE appears at least once in K136:K137.

Please confirm that is it an all situation related to -3 & 3 not an any situation.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
August 9, 2017 at 06:49:11
Thank you. Yes- this is exactly the need. I agree- 'Any' was a wrong choice of word.

We do want to return EQUAL when all cells in the range: K94:K125 contain values between -3 & 3, even if VARIANCE appears at least once in K136:K167.



Reply ↓  Report •

#7
August 9, 2017 at 07:09:49
✔ Best Answer
Here are 2 versions that should work for you:

=IF(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),"EQUAL",
IF(AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3") - COUNTIF(Comparison!K94:K125,">3"))=ROWS(K94:K125)),"EQUAL",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW")))

=IF(OR(COUNTIF(Comparison!K136:K157,"=EQUAL")=ROWS(K136:K157),
AND(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,
(COUNTIF(Comparison!K94:K125,">-3")-COUNTIF(Comparison!K94:K125,">3"))=ROWS(K94:K125))),"EQUAL",
IF(COUNTIF(Comparison!K136:K157,"=VARIANCE")>0,"VARIANCE",
"NEEDS REVIEW"))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
August 9, 2017 at 07:46:48
Thank you again- Both work great!

Reply ↓  Report •

#9
August 9, 2017 at 07:52:15
Your next task is to dissect the formulas and determine how they work so that you can use the techniques in other situations.

The Evaluate Formula feature on the Formulas ribbon will allow you to single step through a formula and watch as it evaluates each portion individually.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#10
August 9, 2017 at 10:30:01
Yes sir. Thanks for the helpful tip, regarding 'Evaluate Formula'.

Reply ↓  Report •

Ask Question