Last few transactions

[Mohan Babu]

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.

 

[T. Valko]

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.

 

[Jacob Skaria]

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]

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!

 

[Jacob Skaria]

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]

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

 

[Mohan Babu]

Hi Jacob & Valko,

I should correct myself here. It's last 10 boxes shipped and the
transactions can be any number. The formula should calculate untill we get
the last 10 boxes and defects calculated.

I have tried both the array formulas and the results are below:

2nd Formula: Result - "C should have been Good"
Month/Vendor A B C D E.
Result Bad Bad Bad Bad Bad
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
June 2.1 1.1 1.2 2.0 1.0
May 0.0 2.2 1.0 1.1 0.0
April 1.1 0.0 0.0 3.1 0.0
March 1.1 1.2 2.0 1.0 2.1
February 2.2 1.0 1.1 0.0 0.0
January 0.0 0.0 3.1 0.0 1.1
December 2.1 1.1 1.2 2.0 1.0
November 0.0 2.2 1.0 1.1 0.0
October 1.1 0.0 0.0 3.1 0.0
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
June 2.1 1.1 1.2 2.0 1.0
May 0.0 2.2 1.0 1.1 0.0
April 1.1 0.0 0.0 3.1 0.0


1st Formula: Result - "C should have been Bad"
Month/Vendor A B C D E.
Result Bad Bad Bad OK OK
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
June 2.1 1.1 1.2 2.0 1.0
May 0.0 2.2 1.0 1.1 0.0
April 1.1 0.0 0.0 3.1 0.0
March 1.1 1.2 2.0 1.0 2.1
February 2.2 1.0 1.1 0.0 0.0
January 0.0 0.0 3.1 0.0 1.1
December 2.1 1.1 1.2 2.0 1.0
November 0.0 2.2 1.0 1.1 0.0
October 1.1 0.0 0.0 3.1 0.0
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
June 2.1 1.1 1.2 2.0 1.0
May 0.0 2.2 1.0 1.1 0.0
April 1.1 0.0 0.0 3.1 0.0

-Mohan

 

[Jacob Skaria]

--The earlier formula consider entries from top to bottom where as your data
is just in the reverse order..and hence the difference..

--The below formula in cell B2 will look at the bottom 10 rows and picks the
average defects for 10 boxes.

'Array entered using CTRL+SHIFT+ENTE
=IF(SUM(INT(OFFSET(B2,1,0,10)))/(SUM(MOD(OFFSET(B2,1,0,10),1))*10)>10/3,"OK","Bad")


OR

If you are interested in a VBA solution; the below will do what you are
looking for..

Sub MyMacro()
Dim lngRow As Long, lngCol As Long, intBox As Integer, intDef As Integer
For lngCol = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
lngRow = 3: Do: intBox = intBox + Int(Cells(lngRow, lngCol))
intDef = intDef + (Cells(lngRow, lngCol) - Int(Cells(lngRow, lngCol))) * 10
lngRow = lngRow + 1: Loop Until intBox >= 10
Cells(2, lngCol) = IIf(intBox / intDef > 10 / 3, "OK", "Bad")
intBox = 0: intDef = 0
Next
End Sub


Incase if you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


If this post helps click Yes

 

[Mohan Babu]

Hey Jacob & Valko,

The 1st 2 solutions helped after sorting the data in the reverse order. Will
try the 3rd one given by Jacob. Could any of you explain the 1st and 2nd
solution to me. It was mentioned that you are calculating average defect!!!!
But i wanted the actual defect count and not the average. But please explain
the formulas so that im aware of the logic used here.

Truly appreciate the quick help. You guys rock!!!

Thanks,
-Mohan