Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Plus/Minus 2*Standard Deviation IF OR Statement
The formula I have requires a value to fall between an average plus or minus
two times a standard deviation. The formula looks like this =IF(N165O165+2*(P165)+OR(N165<O165-2*(P165)),"Flag",""). What I just discovered is that the "flag" will only appear when the value is greater than the ave + 2*standard diviation, not when it is less than the average minus 2*standard diviation. I would like to keep the formula together so I do not have to add additional columns to the sheet. I certainly could use some clarification to this formula. Each part works as a seperate formula but not together. Thank you |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Plus/Minus 2*Standard Deviation IF OR Statement
The OR operator comes before the inputs: OR(A,B) rather than A OR B.
So to flag entries outside two standard deviations: =IF(OR(N165(O165+2*P165),N165<(O165-2*P165)),"Flag","") "sm416" wrote: The formula I have requires a value to fall between an average plus or minus two times a standard deviation. The formula looks like this =IF(N165O165+2*(P165)+OR(N165<O165-2*(P165)),"Flag",""). What I just discovered is that the "flag" will only appear when the value is greater than the ave + 2*standard diviation, not when it is less than the average minus 2*standard diviation. I would like to keep the formula together so I do not have to add additional columns to the sheet. I certainly could use some clarification to this formula. Each part works as a seperate formula but not together. Thank you |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Plus/Minus 2*Standard Deviation IF OR Statement
You are not using the OR function correctly, but I wouldn't do what you want
using it anyway. Try it this way... =IF(ABS(O165-N165)2*P165,"Flag","") Rick "sm416" wrote in message ... The formula I have requires a value to fall between an average plus or minus two times a standard deviation. The formula looks like this =IF(N165O165+2*(P165)+OR(N165<O165-2*(P165)),"Flag",""). What I just discovered is that the "flag" will only appear when the value is greater than the ave + 2*standard diviation, not when it is less than the average minus 2*standard diviation. I would like to keep the formula together so I do not have to add additional columns to the sheet. I certainly could use some clarification to this formula. Each part works as a seperate formula but not together. Thank you |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Plus/Minus 2*Standard Deviation IF OR Statement
That did the trick. So simple, but the simple things are the hardest to find!
thanks bpeltzer! "bpeltzer" wrote: The OR operator comes before the inputs: OR(A,B) rather than A OR B. So to flag entries outside two standard deviations: =IF(OR(N165(O165+2*P165),N165<(O165-2*P165)),"Flag","") "sm416" wrote: The formula I have requires a value to fall between an average plus or minus two times a standard deviation. The formula looks like this =IF(N165O165+2*(P165)+OR(N165<O165-2*(P165)),"Flag",""). What I just discovered is that the "flag" will only appear when the value is greater than the ave + 2*standard diviation, not when it is less than the average minus 2*standard diviation. I would like to keep the formula together so I do not have to add additional columns to the sheet. I certainly could use some clarification to this formula. Each part works as a seperate formula but not together. Thank you |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Plus/Minus 2*Standard Deviation IF OR Statement
No. Most of these simple things are *easy* to find. Excel has a help
facility. If you don't know the syntax of the OR function, type the word OR into Excel help. The same applies for any Excel function (except DATEDIF). -- David Biddulph "sm416" wrote in message ... That did the trick. So simple, but the simple things are the hardest to find! thanks bpeltzer! "bpeltzer" wrote: The OR operator comes before the inputs: OR(A,B) rather than A OR B. So to flag entries outside two standard deviations: =IF(OR(N165(O165+2*P165),N165<(O165-2*P165)),"Flag","") "sm416" wrote: The formula I have requires a value to fall between an average plus or minus two times a standard deviation. The formula looks like this =IF(N165O165+2*(P165)+OR(N165<O165-2*(P165)),"Flag",""). What I just discovered is that the "flag" will only appear when the value is greater than the ave + 2*standard diviation, not when it is less than the average minus 2*standard diviation. I would like to keep the formula together so I do not have to add additional columns to the sheet. I certainly could use some clarification to this formula. Each part works as a seperate formula but not together. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
standard deviation | Charts and Charting in Excel | |||
standard deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions | |||
Standard Deviation | Excel Worksheet Functions |