Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
Hello Forum Hope you can help me solve this one. I pack fruit in 200 and 40o gram cartons. This is pure fruit with the waste taken off. EG - 1 pineapple will weigh 1200 grams whole. Once I have prepared this then the following is the breakdown (Waste skin is 800g) and (pure fruit 400 grams). Therefore if a customer orders 70 boxes of the 200 gram carton and 40 cartons of the 400 grams I need to calculate how much whole pineapple in weight do I have to order. Hope you understand and please someone help me. Thanks Martin -- AKFRUIT ------------------------------------------------------------------------ AKFRUIT's Profile: http://www.excelforum.com/member.php...o&userid=33796 View this thread: http://www.excelforum.com/showthread...hreadid=535640 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
You would set up a sheet for all the fruit you process, showing the Details
of your products, such as Gross weight and Nett weight. It is then simply a matter of multiplying the number of boxes by its (the box's) weight, and deviding by the nett weight per item to determine the number of items to order. If you need to order by weight, then multiply the obtained number by the gross weight. Say A2 is the name of the product, B2 the Gross weight, C2 the waste, then D2 = =B2-C2 to get the nett weight. Say you have your boxes listed in A10 (200 grm) and A11 (400 gram) In B10 and B11 list the order quantities. In C10 enter =B10*A10/D2 In C11 enter =B11*A11/D2 Ideally, you would however set up your product list on one sheet, and do your calculations on another. You would the use VLOOKUP to reference the products list, with offset columns.Lets say your products list is called products, and is made up of a range A2:D20. On Sheet 2, in A1 you enter ITEM, in A2, you type in the product you wish to provide. B1 bears 200 and C1 bears 400. In B2 and C2 you enter the number of boxes required. In B3 you enter =IF(B2="","",B2*B1/VLOOKUP(A2,products,4,FALSE))This will give you the number of items to order.If you want weight, iso qty, change this formula to read =IF(B2="","",B2*B1/VLOOKUP(A2,products,4,FALSE)*VLOOKUP(A2,products,2 ,FALSE)). That will give you weight required. "AKFRUIT" wrote: Hello Forum Hope you can help me solve this one. I pack fruit in 200 and 40o gram cartons. This is pure fruit with the waste taken off. EG - 1 pineapple will weigh 1200 grams whole. Once I have prepared this then the following is the breakdown (Waste skin is 800g) and (pure fruit 400 grams). Therefore if a customer orders 70 boxes of the 200 gram carton and 40 cartons of the 400 grams I need to calculate how much whole pineapple in weight do I have to order. Hope you understand and please someone help me. Thanks Martin -- AKFRUIT ------------------------------------------------------------------------ AKFRUIT's Profile: http://www.excelforum.com/member.php...o&userid=33796 View this thread: http://www.excelforum.com/showthread...hreadid=535640 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
On Mon, 24 Apr 2006 13:35:56 -0500, AKFRUIT
wrote: Hello Forum Hope you can help me solve this one. I pack fruit in 200 and 40o gram cartons. This is pure fruit with the waste taken off. EG - 1 pineapple will weigh 1200 grams whole. Once I have prepared this then the following is the breakdown (Waste skin is 800g) and (pure fruit 400 grams). Therefore if a customer orders 70 boxes of the 200 gram carton and 40 cartons of the 400 grams I need to calculate how much whole pineapple in weight do I have to order. Hope you understand and please someone help me. Thanks Martin Unless I've missed something then with A1=400 Representing the 400g carton and B1=200 Representing the 200g carton and A2 = 40 The number of 400g cartons and B2= 70 The number of 200g cartons Put in C2 =(A2*A1+B2*B1)/1000*3 to give the total weight in Kg of pineapples you should order. Better still if your waste factor should vary, put the waste factor in C1, 0.66667 (800 as % of 1200) in this example, and change C2 to: =(A2*A1+B2*B1)/1000*1/(1-C1) HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
Richard Thanks for that it works a treat. May I ask you another question? From your solution I have been trying to complete it and update the formula but keep getting stuck. If I have say a 200 or 400g carton that has a mixture of fruit say:- e.g. The 200g carton 80 grams of pineapple 40 grams of Kiwi 80 grams of grapes How would I implement this into a formula? Hope you can help and once again thanks Martin -- AKFRUIT ------------------------------------------------------------------------ AKFRUIT's Profile: http://www.excelforum.com/member.php...o&userid=33796 View this thread: http://www.excelforum.com/showthread...hreadid=535640 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
Perhaps a more generalised solution would be preferable. Try the
following B1 = No of Cartons C1= 70 D1= 40 B2= Carton Size (grams) C2= 200 D2= 400 B3= Waste Factor E3= Total Purchase Weight (Kg) A4= Pineapple B4= 0.66667 C4= 80 D4= 400 E4= =SUMPRODUCT(C4:D4,$C$1:$D$1)/1000*1/(1-B4) Copy E4 down to E5 & F5 E4:E6 represents the total weight of each of your fruits. I've assumed the waste factor is the same for each fruit, i.e. 0.6667, but just change B4:B6 where necessary if they are different. You might also like to put in a sum of C4:C6 and D4:D6 in say C8 & D8 just to check that the individual fruit weights add up to the carton weight. Hope this is useful. Next time I'm your way I'll come and sample some :-) Regards, Richard Buttrey |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Numbers of boxes of fruit to buy
Oops, sorry, missed out
A5= Kiwi B5= 0.66667 C5= 40 A6 = Grapes B6= 0.6667 C6= 80 Rgds On 26 Apr 2006 16:21:06 -0700, "Richard" wrote: Perhaps a more generalised solution would be preferable. Try the following B1 = No of Cartons C1= 70 D1= 40 B2= Carton Size (grams) C2= 200 D2= 400 B3= Waste Factor E3= Total Purchase Weight (Kg) A4= Pineapple B4= 0.66667 C4= 80 D4= 400 E4= =SUMPRODUCT(C4:D4,$C$1:$D$1)/1000*1/(1-B4) Copy E4 down to E5 & F5 E4:E6 represents the total weight of each of your fruits. I've assumed the waste factor is the same for each fruit, i.e. 0.6667, but just change B4:B6 where necessary if they are different. You might also like to put in a sum of C4:C6 and D4:D6 in say C8 & D8 just to check that the individual fruit weights add up to the carton weight. Hope this is useful. Next time I'm your way I'll come and sample some :-) Regards, Richard Buttrey __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why are 1/2 my numbers imported as text and the rest as numbers? | Excel Discussion (Misc queries) | |||
VLOOKUP should compare numbers stored as text to plain numbers. | Excel Worksheet Functions | |||
Checking Winning Numbers in the Lottery. | Excel Discussion (Misc queries) | |||
Sorting alphanumeric numbers | Excel Discussion (Misc queries) | |||
looking to assign different numbers in column B to different words in column A | Excel Worksheet Functions |