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.

✔ 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.

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")>0That 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

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

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

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

2because of 4 & 5If 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 Ithinkyou 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 metMy original formula met those requirements.

Then you said:

4 - I would to

the logic:add

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

, I read that to mean that you now have a 4th condition. I read that to mean that you are now looking foradd2 different resultsrelated to having VARIANCE in any cell in K136:K167, as follows:If

anycell in K136:K167 contains VARIANCE, then the result should be VARIANCEunlessat the same time thatanycell in K136:K167 contains VARIANCE,anycell 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: -4K136: EQUAL

K137: EQUALBased on Requirement #2, this should return VARIANCE

K94: 7

K95: -4K136: VARIANCE

K137: EQUALBased on Requirement #3, this should return NEEDS REVIEW

K94: 7

K95: -4K136:

K137: EQUALBased on Requirement #4, this should return EQUAL

K94: 7

K95: 0K136: VARIANCE

K137: EQUALIf 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.

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: EQUAL2)

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: EQUALThe verbiage you reiterated to describe both these examples, I think? Please let me know of your thoughts, when possible.

This example does not meet the new requirement (#4) as you previously stated it. K94: 7

K95: 0

K136: VARIANCE

K137: EQUALYou said: "IF

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'."anyNote your use of the word

. In this example, K95 is thatanycell that is between -3 & 3.anyBased on your most recent examples, you appear to want to return EQUAL when

cells in the range contain values between -3 & 3, even if VARIANCE appears at least once in K136:K137.allPlease confirm that is it an

situation related to -3 & 3 not anallsituation.any

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

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.

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.

Thank you again- Both work great!

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.

Yes sir. Thanks for the helpful tip, regarding 'Evaluate Formula'.

Ask Your Question

Weekly Poll

Do you agree with Google's decision to shut down Google+?

Discuss in The Lounge

Poll History