Results 1 to 10 of 10
  1. #1
    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. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #3
    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. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #5
    Join Date
    Feb 2007
    Location
    Devon, UK
    Posts
    37
    thanks mike ,this does appear to do what i wanted.

  6. #6
    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. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #8
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    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. #9
    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. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Quote 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
  •