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

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
>
>

On Mon, 24 Apr 2006 13:35:56 -0500, AKFRUIT
<[email protected]> 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
__________________________

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

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

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" <[email protected]>
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
__________________________