Yes thats right.

The best way I think; the OP should look at this should be to take an
average of the last 10 transactions and then check the number of defects per
10 boxes...

=IF((SUM(INT(OFFSET(B2,COUNT(B2:B100)-1,,-10))))/(SUM(MOD(OFFSET(B2,COUNT(B2:B100)-1,,-10),1))*10)10/3,"Good","Bad")

Please note that this is an array formula. You create array formulas in the
same way that you create other formulas, except you press CTRL+SHIFT+ENTER to
enter the formula. If successful in 'Formula Bar' you can notice the curly
braces at both ends like "{=<formula}"

If this post helps click Yes
---------------
Jacob Skaria


"T. Valko" wrote:

OP has asked for more than 3 defects in the last 10 boxes;
(not the last 10 transactions).

I need to check the last 10 transaction
If i have more than 3 defects in the last 10 boxes

I guess it could be interpreted both ways!

--
Biff
Microsoft Excel MVP


"Jacob Skaria" wrote in message
...
Biff, OP has asked for more than 3 defects in the last 10 boxes; (not the
last 10 transactions). One transaction can have more than 1 box


"T. Valko" wrote:

You can use something like this...

A2:An = months
B2:Bn = shippment data

Array entered** :

=IF(SUM(MOD(OFFSET(B2,COUNT(B2:B100)-1,,-10),1))0.3,"Bad","OK")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


"Mohan Babu" <Mohan wrote in message
...
Need Help. Every month i will have apples shipped from a few vendors.
All
apples comes in a box and we usually find some with defects. 1 box can
have 1
defect apple and sometimes 3 to 4.

So on a monthly basis we enter the shipment details in an excel against
each
vendor like the one below:

Month/Vendor A B C D E.
September 1.1 1.2 2.0 1.0 2.1
August 2.2 1.0 1.1 0.0 0.0
July 0.0 0.0 3.1 0.0 1.1

Note 1: 1.1 means 1 box of apple with 1 defect apple
1.2 means 1 box of apple with 2 defect apple
2.2 means 2 box of apple with 2 defect apple
0.0 means there were no shipments that month.

Note 2: I have more vendors and more than 10 years information but this
is
the general picture.

I need to check the last 10 transaction with each of these vendors and
categorize them with the simple rules below:
If i have more than 3 defects in the last 10 boxes sent then he will be
a
BAD vendor. If he has 3 or less than 3 defects, he is a good vendor.

This calculation should be done on a monthly basis before i order the
shipment. All shipments are ordered on the last day of the month. Could
you
help me with this.