triggering based on range-2

[ZOHAR]

Respected
----
Problem previously was solved(by Xlent -THANK YOU VERY VERY MUCH) but I faced a new thing when I applied it in real data
----
https://www.excelforum.com/excel-for...ml#post5083121

Previous post was An event has to be triggered based on the input, that is dependent on value in other cell

say A1 has 100
if b1 is entered in range of 99.90 to 100.10
c1 should say TRUE ,

all other value less than 99.90 and greater than 100.10
OR alphabet
OR special sign inserted in B1
should say FALSE


So similarly calculation should work when A1 is 150 and 149.85 to 150.15 is okay(TRUE)
and for 49, 48.95 to 49.05 is okay(TRUE)



Zohar Batterywala

[ZOHAR]

PLEASE PLEASE help

A start value B end value up C end value down *****B Will always be greater than A and C will always be less than A *****
55 55.1 54.95 difference from B-A is 0.1 while that of A-C is 0.05
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b3

55 55.05 54.9 difference from B-A is 0.05 while that of A-C is 0.1
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2




483 483.4 482.65 difference from B-A is 0.4 while that of A-C is 0.35
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b3

483 483.35 482.6 difference from B-A) is 0.35 while that of A-C is 0.4
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2


6090 6096 6085 difference from B -A is 4 while that of A-C is 5
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2
6090 6096 6087 difference from B-A is 6 while that of A-C is 3
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b2


Make one function that gives required result on all 6 cases

the formula which I was suggested is '=ROUND(ABS(A1-B1),2)<=ROUND(A1/1000,2)
After making changes I was trying to use it but it is not working but ITS NOT WORKING
=IF(ROUND(ABS(A5-B5),2)<=ROUND((A5*100)/100000,2),(IF(AND((B5-A5)>(A5-C5),B5>A5),"▲",IF(AND((ROUND(ABS(C5-A5),2)<=ROUND((A5*100)/100000,2)),(B5-A5)<(A5-C5),(C5<A5)),"▼"))),"")

[ZOHAR]

PLEASE PLEASE help


*****B Will always be greater than A and C will always be less than A *****
A start value B end value up C end value down
1ST CASE
55 55.1 54.95
difference from B-A is 0.1 while that of A-C is 0.05
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b3

55 55.05 54.9
difference from B-A is 0.05 while that of A-C is 0.1
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2

2ND CASE

483 483.4 482.65
difference from B-A is 0.4 while that of A-C is 0.35
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b3

483 483.35 482.6
difference from B-A is 0.35 while that of A-C is 0.4
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2

3RD CASE
6090 6096 6085
difference from B -A is 4 while that of A-C is 5
so here A-C is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▼ is to be displayed in b2

6090 6096 6087 difference from B-A is 6 while that of A-C is 3
so here B-A is given preference
Then it is checked whether it is in 0.01% of A1 , if so ▲ is to be displayed in b2


Make one function that gives required result on all 6 cases

the formula which I was suggested is '=ROUND(ABS(A1-B1),2)<=ROUND(A1/1000,2)
After making changes I was trying to use it but it is not working but ITS NOT WORKING
=IF(ROUND(ABS(A5-B5),2)<=ROUND((A5*100)/100000,2),(IF(AND((B5-A5)>(A5-C5),B5>A5),"▲",IF(AND((ROUND(ABS(C5-A5),2)<=ROUND((A5*100)/100000,2)),(B5-A5)<(A5-C5),(C5<A5)),"▼"))),"")

[Bo_Ry]

Not sure I get this right.
Please try D2
=IF(A2="","",IF(MAX(INDEX(ABS(B2:C2-A2),))/A2>0.01,"",IF(MMULT(ABS(B2:C2-A2),{-1;1})>0,"▼","▲")))

3RD CASE
6090 6096 6085
difference from B -A is 4 [should be 6] while that of A-C is 5

[ZOHAR]

Respected Bo_RY,
NEEDS your further help as there are problem in implementation

The function do not correctly detect 0.1%,(it was my mistake to mention it as 0.01%)

SUGGESTION - i think that can be done by 0.001 as I have experienced many times (am not sure)

Also RATHER than using ABS we can make two if functions,
for detecting between 100 & 100.1 and
for detecting between 99.9 &100

SORRY IN ADVANCE FOR GIVING SUGGESTION WHEN YOU ARE MUCH MORE EXPERIENCED THAN ME.

Zohar Batterywala

[AliGW]

This thread is marked as solved - is it?

[Bo_Ry]

What are the results for these ?

100 100.1 99.9
100 100.05 99.9

[ZOHAR]

Respected ,
Reading the comments I thought that answer/solution was given applying all conditions .And so marked as SOLVED then and also clicked ADD REPUTATION.
but later when I tried to use it in other conditions I realized that one of that major thing was not evaluating.
So it cant be considered as SOLVED.
Zohar Batterywala

[ZOHAR]

Results for first case, both 100.1 and 99.9 is correct but further considering the volume inflow from other reservoirs is also considered but this things has probability on 1 in million so first is considered -100.1
Resut of 2nd case 99.9 as B-A < A-C(B will always be more than A and C will always be lesser than A)
Zohar Batterywala

[Bo_Ry]

Please try at L2
=IFERROR(IF((J2-I2<I2*0.1%)*(I2-K2<I2*0.1%),IF(J2-I2<I2-K2,"▼","▲"),IF((J2-I2)/I2<0.1%,"▲",IF((I2-K2)/I2<0.1%,"▼",""))),"")

[ZOHAR]

Respected Bo_RY,
THANK YOU VERY VERY MUCH FOR YOUR HELP
This solution shows correct result for all the cases.
THANK YOU AGAIN
Zohar Batterywala