Calculating number of seed bags need for planting

Question

Assuming that i have seed bag sizes of 1kg, 2 kg, and 5kg. And my clients typically have a land size between 0.25 and 5 acres and will plant 10kgs per acre.

What formualar can i use to calculate for each client the number of 5kg, 2kg and 1kg seed bags they need for planting. In the calculation, i want to try to assign the larger bags first while avoiding to assign half bags to a client as much as possible.

Answer 1

I don't see at this time how a single formula will solve this.

A single formula is insufficient, you need an entire algorithm.

You will need to create a spreadsheet or write a computer program.

I did it in excel,and saved the spreadsheet for you if you want it.

 

Here is a description of the sheet.

 

CELL               Contents                      Description/Comments

 

C2                  "# of acres                        prompts the user to input the # of acres

 

D2                                                           where the user will input the # of acres

 

C4                   # of kilograms needed          labels the # of kilograms of seeds needed

 

D4                    =D2*10                             multiplies the # of acres by 10

 

C6                  # of whole kilograms needed   labels te # of kilograms needed after throwing the fraction away

 

D6                  = FLoor(D4,1)                        throws away the fraction in the result of D4

 

F6                  fractions kg                             labels the fraction of a kilogram  needed

 

G6                 =D4-D6                                  calculates the fractional part 

 

C8                  # of 5 kg bags needed             labels the # of 5 kg bags needed

 

D8            =FLOOR(d6/5,1)                            calculates the # of WHOLE 5 kg bags needed

 

F8                remainder                                    labels how many kgs are left to go

 

G8               =MOD(d6,5)                                 calculates how much weight remains

 

 C10        # of 2 kg bags needed                        labels the # of 2 kg bags needed

 

D10          =FLOOR(g8/2,1)                               calculates the # of WHOLE 2 kg bags needed

 

F10              remainder                                       labels the remaining weight

 

G10           = MOD(g8,2)                                   calculates the remaining weight

 

c12             # of bags needed                               labels the # of WHOLE 1 kg bags needed

 

d12                = g10

 

c14              # of half pound bags needed                labels the # of half pound bags needed

 

D14               =G6/0.5