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

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

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

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

thanks mike ,this does appear to do what i wanted.

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

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

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

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

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