Results 1 to 10 of 10
Thread: Nested IF or equivalent

051707, 07:19 #1Registered User
 Join Date
 Feb 2007
 Location
 Devon, UK
 Posts
 37
Unanswered: Nested IF or equivalent
i have a calculation that produces and average price of sales of a grp of products what i need is an statement that flashed up an error if a result is + 5 pounds more than the expected out come
eg
average price is 96.79
expected average is 105.00
average is more than 5 pounds under expeced average so flash up a warning!
i need something that checks either way, i could do them separately but i am assuming it can be done together
thanks in advance

051707, 08:35 #2Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 789
Provided Answers: 1Hi
I little more info needed.
Is this in VBA or worksheet functions ?
However, to get the ball rolling a worksheet function like this ??
=IF(ABS(E3105)>5,"Average not within accepted range.","")
where E3 holds the calculated average ??
MTB

051707, 08:49 #3Registered User
 Join Date
 Feb 2007
 Location
 Devon, UK
 Posts
 37
sorry for the type of post, i come from a programming background, gave up IT to train as an accountant and never had to use excel until i started this job
in some sort of pseudo programming language this is what i need
and definetly no VB as i havent gotten anywhere near that for now
IF X > 95 or < 90 then, "Error Out of Range" else "ok",
something like that, you must assume that i know what x is and i also know the average number in my head, the two numbers will just be 5 either way, a statement will be written for each grp, so all i will changes is the two numbers to fit the expected price of each product range.
thanks again for the reply

051707, 09:04 #4Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 789
Provided Answers: 1Hi
You have changes the rules! This is + or  2.5 not 5
Give you last example doesn't this do it ?
=IF(ABS(X92.5)>2.5,"Error Out of Range","ok")
??
MTB

051707, 10:50 #5Registered User
 Join Date
 Feb 2007
 Location
 Devon, UK
 Posts
 37
thanks mike ,this does appear to do what i wanted.

051807, 11:41 #6Registered User
 Join Date
 Feb 2007
 Location
 Devon, UK
 Posts
 37
just one more question, i was looking at the ABS term in the excel help to try to better understand how what you tell me works, i can see that it does but dont see how that term performs the task. If you could just say in basic terms how it does its job that would be a great help. I'm gradually learning more about excel, taking courses etc and this sort of thing would be handy thanks.
Lovin

051807, 11:59 #7Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 789
Provided Answers: 1Excel Help states
"ABS
Returns the absolute value of a number. The absolute value of a number is the number without its sign."
ie Abs(number). In this case number can be a single number or the result of and expression (calculation) as in the above posts X9.25
Whatever the value of X (greater than 92.5 or less than 92.5) ABS will always return the VALUE of the difference only (which is the same a always a PLUS/POSITVE value). Therefore, whatever the difference between X and 92.5 (+ve or ve) it can alway be compared to +2.5 to see if the difference is greater that +2.5.
Does that make any more sense ?
MTB

052107, 08:12 #8Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 789
Provided Answers: 1Hi again
There are, of course, other ways of doing this without nested IFs that are more obvious to understand ie
=IF(OR(X>95,X<90),"Error Out of Range","ok")
or
=IF(AND(X<=95,X>=90),"ok","Error Out of Range")
Just idle thoughts
MTB

052107, 08:24 #9Registered User
 Join Date
 Feb 2007
 Location
 Devon, UK
 Posts
 37
thanks mike, all a great help,
but how can a number be less than say 95 and also greater than 105 at the same time, just a thought?
i can see how the or statement would work but not the other??
lovin

052107, 08:34 #10Registered User
 Join Date
 Apr 2004
 Location
 Derbyshire, UK
 Posts
 789
Provided Answers: 1Originally Posted by lovinfeelin
It can. however, be less than 95 AND greater than 90 (second example), in which case AND(X<=95,X>=90) is TRUE and "ok" is displayed.
OK now ??
MTB