IF, OR and AND Nested Formula with a Threshold

[beloshi]

Hello Excel Gurus,

I have put the Formula to compare the cell based on the cell value using a threshold of ± 30%, in some cell Formula is working fine but in some it is not at all, I cannot figure this out need help.

Here is the Formula I am using;

Formula =IF(OR(AND(H17=$F$16);AND(H17<=$F$16+($D$153*$F$16));AND(H17<=$F$16-($D$153*$F$16)));TRUE;FALSE)

Where

$D$153 = Threshold % i.e. 0.3
H17= actual cell to compare i.e. -44470
AND $F$16= absolute value to compare with i.e. -790

Here is the screenshot of the formula results which is very weird

Here is the screenshot of the formula which is working correctly

Quick help would be much appreciated. Apologies if this question is too stupid to ask here.

BR

BeLoShI

 

[Joe4]

You formula does not appear to be written very efficiently (and may actually be incorrecting).
I do not understand why you are using AND at all, all your AND functions only have one argument. You would only use AND or OR when you have more than one thing you want to check within that particular function.

For example, this:

AND(H17=$F$16)

is only checking one condition, so using AND is totally unnecessary.
It looks like your other two AND statements have the same issue.

I cannot see any of your screenshots. Rather than trying to work with a formula that may be incorrect, can you simply explain exactly what you are trying to do, in plain English? Be sure to include references to all the cells your formula is using in your explanation.

 

[beloshi]

You formula does not appear to be written very efficiently (and may actually be incorrecting).
I do not understand why you are using AND at all, all your AND functions only have one argument. You would only use AND or OR when you have more than one thing you want to check within that particular function.

For example, this:

AND(H17=$F$16)

is only checking one condition, so using AND is totally unnecessary.
It looks like your other two AND statements have the same issue.

I cannot see any of your screenshots. Rather than trying to work with a formula that may be incorrect, can you simply explain exactly what you are trying to do, in plain English? Be sure to include references to all the cells your formula is using in your explanation.

Hi Thanks for the quick Reply

Here are the screenshots

Formula working correctly

https://imgur.com/hroC09c

Formula Not working

https://imgur.com/w2YW8B7


Actually I am comparing two cells for example in the above screenshot example

Cell A1 = -790

Cell C1 = -44470

C2= -888

In column B1 I have applied the above formula to compare Cell C1, C2 ........ so on and so forth with Cell A1 to see if the value of C1 is either equal to A1 or +30% of C1 or -30% of C1 which is the threshold

Hope this helps to clear my question

 

[Joe4]

I cannot see your images, as my company security blocks all those file and image sharing sites.

But, I think this might do what you want, based on your last example:

=OR(C1<(A1*(1+0.3)),C1>(A1*(1-0.3)))

On your original example, I think it would look like:

=OR(H17<($F$16*(1+$D$153)),H17>($F$16*(1-$D$153)))

Note that you do not need to use IF, as AND or OR functions already return True/False be default. So there is no reason to say if True, return True, if False return False (redundant and not necessary).

 

[beloshi]

I cannot see your images, as my company security blocks all those file and image sharing sites.

But, I think this might do what you want, based on your last example:

=OR(C1<(A1*(1+0.3)),C1>(A1*(1-0.3)))

On your original example, I think it would look like:

=OR(H17<($F$16*(1+$D$153)),H17>($F$16*(1-$D$153)))

Note that you do not need to use IF, as AND or OR functions already return True/False be default. So there is no reason to say if True, return True, if False return False (redundant and not necessary).

Thanks the above formula is still showing TRUE. I want it to just stay with exactly 30% threshold i.e exactly 0.3 above or below.

Please see the screenshot below for my explanation

https://imgur.com/TEt2nN4

 

[Joe4]

Please see the screenshot below for my explanation

I already mentioned that I cannot see these!

Thanks the above formula is still showing TRUE. I want it to just stay with exactly 30% threshold i.e exactly 0.3 above or below.

If you only want it to show TRUE if it is EXACTLY the 30% threshhold, then just change the > and < signs to =.

And if you want it to be TRUE if they are EXACTLY equal with no threshhold, then just add that first condition back in (like you did in your original formula):

=OR([COLOR=#ff0000]H17=$F$16[/COLOR],H17=($F$16*(1+$D$153)),H17=($F$16*(1-$D$153)))

 

[jtakw]

Hi,

It gets tricky if you use the >= (greater than or equal) and <= (less than or equal) tests for Both positive And negative values...

i.e.

8 <= 9 is TRUE
-8 <= -9 is Not TRUE

So you would Have to test the values for positive or negative Also.

Here's my suggestion, see if it works for you:

Book1
	D	E	F	G	H
15	0.3
16			-790
17				FALSE	-44470
18				TRUE	-888
19				TRUE	-790
20				TRUE	-553
21				TRUE	-1027
22				FALSE	-1028
23				FALSE	-552
24				TRUE	-554
25
26			13
27				TRUE	11
28				FALSE	18
29				TRUE	14
30				TRUE	13
31				FALSE	17
Sheet680

Cell Formulas
Range	Formula
G17	=H17=MEDIAN(F$16*(1+D$15),H17,F$16*(1-D$15))

Change D$15 to D$153 according to your setup.

 

[jtakw]

Too late to edit.

You may need to replace the commas with semicolons in my formula above for your regional settings.

 

[beloshi]

Hi,

It gets tricky if you use the >= (greater than or equal) and <= (less than or equal) tests for Both positive And negative values...

i.e.

8 <= 9 is TRUE
-8 <= -9 is Not TRUE

So you would Have to test the values for positive or negative Also.

Here's my suggestion, see if it works for you:

	D	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]15[/TD]
[TD="align: right"]0.3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-790[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]-44470[/TD]

[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]-888[/TD]

[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]-790[/TD]

[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]-553[/TD]

[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]-1027[/TD]

[TD="align: center"]22[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]-1028[/TD]

[TD="align: center"]23[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]-552[/TD]

[TD="align: center"]24[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]-554[/TD]

[TD="align: center"]25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]26[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]27[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]11[/TD]

[TD="align: center"]28[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]18[/TD]

[TD="align: center"]29[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]14[/TD]

[TD="align: center"]30[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]TRUE[/TD]
[TD="align: right"]13[/TD]

[TD="align: center"]31[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]FALSE[/TD]
[TD="align: right"]17[/TD]

</tbody>

Sheet680​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]G17[/TH]
[TD="align: left"]=H17=MEDIAN(F$16*(1+D$15),H17,F$16*(1-D$15))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Change D$15 to D$153 according to your setup.

Thank You @jtakw for the solution yes you are right its tricky when it comes to values that are negative -8 and -9 or -790 and -44470 in my case. But never the less your solution worked like a charm for me.

Thanks for the efforts @Joe4 and taking out time.

Best Regards

BeLoShI

 

[jtakw]

You're welcome, thanks for the feedback.