How to divide and send whole number to next row

[Superjakeny]

I am trying to make a spreadsheet to figure how many pieces I need. each piece has a certain number of marks on it.

There are several pieces -8marks 6 marks 4 marks 2 marks 1 mark and a half mark. I want to put in a number in the field and let excel figure out how many of each piece I need to fulfill the order.

You always use as many 8marks as you can example.

I have an order of 100 how many marks do i need. I would need 12 mark 8's and 1 mark 4. how do I get excel to do this for me

eample

order is 87 I need 10 mark 8's 1 mark 6 and 1 mark 1. How do I get excel to do this?

Thanks for the help everyone!

 

[XOR LX]

Hi,

If you put 8, 6, 4, 2, 1 and 0.5 in A1:A6 (in descending order, so that 8 is in A1) and your order quantity in D1, then you can put this formula in B1:

=INT($D$1/A1)

And then this in B2:

=INT(($D$1-SUMPRODUCT(A$1:A1,B$1:B1))/A2)

Copy this last one down to B6.

That should give you the required quantities.

Regards

 

[MickG]

If you have a numbers list in column "A" row 2 on, then this should provide result in columns "B to G."

[COLOR="Navy"]Sub[/COLOR] MG31Aug45
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] str [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String[/COLOR]
[COLOR="Navy"]Dim[/COLOR] p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Dim[/COLOR] nums [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] Temp
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
nums = Array(8, 6, 4, 2, 1, 0.5)
Range("B1:G1").Value = nums
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    n = Dn.Value
    [COLOR="Navy"]For[/COLOR] p = 0 To UBound(nums)
        str = str & "," & Int(n / nums(p))
        n = (n / nums(p) - Int(n / nums(p))) * nums(p)
    [COLOR="Navy"]Next[/COLOR] p
Dn.Offset(, 1).Resize(, 6) = Split(Mid(str, 2), ",")
str = ""
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick