--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+ENTER
=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
---------------
Jacob Skaria
"Mohan Babu" wrote:
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" wrote:
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.