Plot within ±1 for all variable and the given variable

[thilag]

Hello Friends

MS Excel Version : 2010
File Name : Plus or minus.xlsx

Requirement 1
If the given value is less than / greater than 1 (i.e., ± 1) within any
other value for any variable then it should plot the name of the variable
in the column C, other wise it should plot nil. Also the count should be
plotted in Cell C15.

Requirement 2
If the given values for variables is within ± 2 with the variable X3
then it should be plotted in Cell D4, Also the Cound should be plotted
in Cell D15.

thanks in advance

thilag

[Fotis1991]

Hello

Try this.

Please Login or Register  to view this content.

[thilag]

Hello Fotis1991

thanks for your immediate reply.

It partially fullfilles my requirement 1. If the double variable comes in a single cell then it shows only one variable Example : In Cell C32 the result should be X3 X6 but the formula shows only X6.


again thanks for your help.

thilag

[thilag]

Hello Fotis1991

thanks for your immediate reply.

Your formula helps me to do the requirement 1 partially. If the 2 variables has to be plotted in the single cell then this formula is plotting only one variable. Example : In Cell C32 the formula value is to be X3 X6 (or) X6 X3 but the formula plots X6 only.

Again thanks for your immediate reply.

thilag

[Fotis1991]

To be honest i noticed only range A2:A13 and gave my suggestion.

For what i see that you need, i don't think that you can do using one formula.

We'll need some helper columns for this or(even better) a VBA solution which(vba solution) i don't know.

Of course this is only my opinion. Let's wait to see if anyone else has a solution to suggest.

[thilag]

Hello Fotis1991

thanks for your reply.

I my self did a formula for try the second requirement, and it is working fine.

Formula is

<=TRIM((IF((IF(AND(B1>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B1<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A1,"Not Applicable"))="Not Applicable","",A1))&REPT(CHAR(10),1)&(IF((IF(AND(B2>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B2<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A2,"Not Applicable"))="Not Applicable","",A2))&REPT(CHAR(10),1)&(IF((IF(AND(B3>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B3<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),"Not Applicable","Not Applicable"))="Not Applicable","",A3))&REPT(CHAR(10),1)&(IF((IF(AND(B4>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B4<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A4,"Not Applicable"))="Not Applicable","",A4))&REPT(CHAR(10),1)&(IF((IF(AND(B5>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B5<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A5,"Not Applicable"))="Not Applicable","",A5))&REPT(CHAR(10),1)&(IF((IF(AND(B6>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B6<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A6,"Not Applicable"))="Not Applicable","",A6))&REPT(CHAR(10),1)&(IF((IF(AND(B7>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B7<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A7,"Not Applicable"))="Not Applicable","",A7))&REPT(CHAR(10),1)&(IF((IF(AND(B8>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B8<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A8,"Not Applicable"))="Not Applicable","",A8))&REPT(CHAR(10),1)&(IF((IF(AND(B9>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B9<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A9,"Not Applicable"))="Not Applicable","",A9))&REPT(CHAR(10),1)&(IF((IF(AND(B10>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B10<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A10,"Not Applicable"))="Not Applicable","",A10))&REPT(CHAR(10),1)&(IF((IF(AND(B11>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B11<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A11,"Not Applicable"))="Not Applicable","",A11)&(IF((IF(AND(B12>((VLOOKUP(A$3,A$1:B$12,2,FALSE))-2.1),B9<((VLOOKUP(A$3,A$1:B$12,2,FALSE))+2.1)),A9,"Not Applicable"))="Not Applicable","",A9))&REPT(CHAR(10),1)))>

Kindly refer the atached file <Plus or minus.xlsx>.

thanks

thilag