Thread
:
Last few transactions
View Single Post
#
3
Posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
Posts: 8,520
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.
Reply With Quote
Jacob Skaria
View Public Profile
Find all posts by Jacob Skaria