Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
--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. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Last few transactions
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 "Jacob Skaria" wrote: --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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
APR formula for irregular transactions | Excel Worksheet Functions | |||
Vlookup, Can I only have it bring up a transactions once? | Excel Discussion (Misc queries) | |||
Log of Transactions | Excel Discussion (Misc queries) | |||
Count transactions below a threshold | Excel Discussion (Misc queries) | |||
couting transactions for same time | Excel Worksheet Functions |