Multiple Functions and conditions

[Biocellguy]

I want to make a combined function, but am not sure the best way. I
successfully created a calculation to determine data that is a statistical
outlier under the heading Min and Max (Sheet 1 E1 and F1 respectively). I
also have been able to create a conditional format to highlight the outlier
(Sheet 1 B1 is in bold red and C1 in bold blue). What I now want to do is in
another sheet have a condition where an asterisk is placed in front the
outlier IF it is an outlier as text will not be graphed. If it is not an
outlier then I want it to be the normal number.

Sheet 1 A B C D E [Min] F
[Max]
1 0.0051 0.0042 0.0051 0.005 0.0042 --
2 0.0041 0.0044 0.10 0.0043 0.10

What I have figured out is =REPLACE(B1,1,1,"*0") will put what I want
(*0.0042) and an IF function can have a formula used as the condition, and OR
can be used for two different logical arguments. I have tried
=OR((IF(B1=E1,(REPLACE(B1,1,1,"*0")),B1)),(IF(B1=F1,(REPLACE(B1,1,1,"*0")),B1)))
This results in “TRUE†being displayed. I have used various combinations,
but none worked.

Sheet 2 shows how I want the result to be

Sheet 2 A B C D
1 0.0051 *0.0042 0.0051 0.005
2 0.0041 0.0044 *0.10 0.0043

 

[Biocellguy]

I also have used =IF(B1=E1,(REPLACE(C13,1,1,"*0")), B1) and that works but I
need 2 conditions.

 

[Greg Wilson]

Perhaps this:

= IF(OR(B1 = E1, B1 = F1), "*" & B1, B1)

Regards,
Greg

 

[JonR]

Try this

=IF(B1=E1,REPLACE(C13,1,1,"*0"),(IF(B1=F1,REPLACE(B1,1,1,"*0"),B1)))

If the first IF corteria is met, it will use the first REPLACE function. If
it is not met, then Excel will evaluate the second IF statement, and if that
is met, will use the second REPLACE function. If both are not met, then B1.

--
HTH

JonR

I also have used =IF(B1=E1,(REPLACE(C13,1,1,"*0")), B1) and that works but I
need 2 conditions.

I want to make a combined function, but am not sure the best way. I
successfully created a calculation to determine data that is a statistical
outlier under the heading Min and Max (Sheet 1 E1 and F1 respectively). I
also have been able to create a conditional format to highlight the outlier
(Sheet 1 B1 is in bold red and C1 in bold blue). What I now want to do is in
another sheet have a condition where an asterisk is placed in front the
outlier IF it is an outlier as text will not be graphed. If it is not an
outlier then I want it to be the normal number.

Sheet 1 A B C D E [Min] F
[Max]
1 0.0051 0.0042 0.0051 0.005 0.0042 --
2 0.0041 0.0044 0.10 0.0043 0.10

What I have figured out is =REPLACE(B1,1,1,"*0") will put what I want
(*0.0042) and an IF function can have a formula used as the condition, and OR
can be used for two different logical arguments. I have tried
=OR((IF(B1=E1,(REPLACE(B1,1,1,"*0")),B1)),(IF(B1=F1,(REPLACE(B1,1,1,"*0")),B1)))
This results in “TRUE†being displayed. I have used various combinations,
but none worked.

Sheet 2 shows how I want the result to be

Sheet 2 A B C D
1 0.0051 *0.0042 0.0051 0.005
2 0.0041 0.0044 *0.10 0.0043