# Thread: Nested IF or equivalent

1. Registered 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

2. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi

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(E3-105)>5,"Average not within accepted range.","")

where E3 holds the calculated average ??

MTB

3. Registered 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

4. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi

You have changes the rules! This is + or - 2.5 not 5

Give you last example doesn't this do it ?

=IF(ABS(X-92.5)>2.5,"Error Out of Range","ok")

??

MTB

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

6. Registered 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

7. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Excel 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 X-9.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

8. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Hi 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

9. Registered 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

10. Registered User
Join Date
Apr 2004
Location
Derbyshire, UK
Posts
805
Provided Answers: 2
Originally Posted by lovinfeelin
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??
A number cannot be less than 95 and greater than 105.

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•