Since you only have 3 options (e.g. 0/1/2 of 1kg pks, you may want to try the following to select the best option.

A B C D 1 Original Weight 13.8 kg 2 New Weight 14.3 kg 3 4 Type Option 1 Option 2 Option 3 5 1.0 kg 0 1 2 6 1.5 kg 10 9 9 7 Total 15.0 kg 14.5 kg 15.5 kg 8 Difference 0.7 0.2 1.2 9 10 Best Option Option 2

Spreadsheet Formulas

Cell Formula B2 =B1+0.5 B6 =ROUNDUP(($B$2-($A$5*B5))/$A$6,0) C6 =ROUNDUP(($B$2-($A$5*C5))/$A$6,0) D6 =ROUNDUP(($B$2-($A$5*D5))/$A$6,0) B7 =($A$5*B5)+($A$6*B6) C7 =($A$5*C5)+($A$6*C6) D7 =($A$5*D5)+($A$6*D6) B8 =B7-$B$2 C8 =C7-$B$2 D8 =D7-$B$2 B10 {=INDEX(B4:D4,0,MATCH(MIN((B8:D8>=0)*(B8:D8)),B8:D8,0))} Formula Array:

Produce enclosing{ }by entering

formula with CTRL+SHIFT+ENTER!

## Like this thread? Share it with others