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.

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.

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.

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.

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.

Mohan, if you have formatted the cells to text use COUNTA() which will work
for both text/numerics instead of COUNT()

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


"Jacob Skaria" wrote:

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.