Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions and 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=F 1,(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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions and conditions
I also have used =IF(B1=E1,(REPLACE(C13,1,1,"*0")), B1) and that works but I
need 2 conditions. "Biocellguy" wrote: 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=F 1,(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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions and conditions
Perhaps this:
= IF(OR(B1 = E1, B1 = F1), "*" & B1, B1) Regards, Greg "Biocellguy" wrote: 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=F 1,(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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple Functions and conditions
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 "Biocellguy" wrote: I also have used =IF(B1=E1,(REPLACE(C13,1,1,"*0")), B1) and that works but I need 2 conditions. "Biocellguy" wrote: 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=F 1,(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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I add multiple conditions to a formula? | Excel Worksheet Functions | |||
Summary Counts Functions to filter multiple conditions | Excel Worksheet Functions | |||
Conditional Sum and multiple conditions across different sheets | Excel Worksheet Functions | |||
Nested IF functions and 3 conditions | Excel Worksheet Functions | |||
Count with multiple conditions | Excel Worksheet Functions |