Thanks:  0
Likes:  0

# Thread: Formula help

1. ## Formula help

hi, i have some weights(kg) in cell A2. so i have two type of packages to fill that weights.one package is 1.5kg & other one is 1 kg. i want use that two pckage to fill that A2 cell weights with less different. but there are two conditions.
01. always 0.5 kg need to add to A2 cell weights.
02. for weight adjusting u can use only maximum 2 of 1 kg packages.so as a example to fill 13.8 kg result should like bellow..(my excel sheet also data appearing like bellow table)

=13.8+0.5 = 14.3

so i have to use 9,1.5 pks & 1, 1 kg pks. result will be - 14.5 ( that mean only different 0.2kg with actual requirement) if i use 10 of 1.5 kg, then result is =15 ,that mean i have used more than 0.7kg. best option is first one.

 13.8 1.5 kg 9 1 kg 1

so i don't no this is can manage with excel formula or not. so pls help me. thanks a lot...

2. ## Re: Formula help

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

 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!

3. ## Re: Formula help

thanks lot fot your help. i have two cells below the weight. so one for 1.5kg and other one for 1 kg. i want to auto fill that two cells with best options. if this possible?. thanks.

4. ## Re: Formula help

I'm afraid I couldn't understand your question, could you try illustrate with a table?

5. ## Re: Formula help

it's like this. a2 cell weight.
a3 cell 1.5kg (text)
a4 cell 1.0kg(text)

so i want answer to cell b3 (how many 1.5 pkgs) & cell b4( how many 1kg pkgs). is this clear.? i'm sending this from my phone. so can't make table.

6. ## Re: Formula help

You mean if you key in 13.8 in A1, you want B3 to show 9 and B4 to show 1?

7. ## Re: Formula help

yes. is this possible?. thanks.

8. ## Re: Formula help

That's possible by shifting the index position, you can hide column D:G thereafter if need so.

 A B C D E F G 1 13.8 Original Weight 13.8 kg 2 Weight New Weight 14.3 kg 3 1.5 kg 9 4 1.0 kg 1 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

 Cell Formula E1 =A1 E2 =E1+0.5 B3 {=INDEX(E6:G6,0,MATCH(MIN(E8:G8),E8:G8,0))} B4 {=INDEX(E5:G5,0,MATCH(MIN(E8:G8),E8:G8,0))} E6 =ROUNDUP((\$E\$2-(\$D\$5*E5))/\$D\$6,0) F6 =ROUNDUP((\$E\$2-(\$D\$5*F5))/\$D\$6,0) G6 =ROUNDUP((\$E\$2-(\$D\$5*G5))/\$D\$6,0) E7 =(\$D\$5*E5)+(\$D\$6*E6) F7 =(\$D\$5*F5)+(\$D\$6*F6) G7 =(\$D\$5*G5)+(\$D\$6*G6) E8 =E7-\$E\$2 F8 =F7-\$E\$2 G8 =G7-\$E\$2 E10 {=INDEX(E4:G4,0,MATCH(MIN(E8:G8),E8:G8,0))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

9. ## Re: Formula help

Originally Posted by alvin-chung
That's possible by shifting the index position, you can hide column D:G thereafter if need so.

 A B C D E F G 1 13.8 Original Weight 13.8 kg 2 Weight New Weight 14.3 kg 3 1.5 kg 9 4 1.0 kg 1 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

 Cell Formula E1 =A1 E2 =E1+0.5 B3 {=INDEX(E6:G6,0,MATCH(MIN(E8:G8),E8:G8,0))} B4 {=INDEX(E5:G5,0,MATCH(MIN(E8:G8),E8:G8,0))} E6 =ROUNDUP((\$E\$2-(\$D\$5*E5))/\$D\$6,0) F6 =ROUNDUP((\$E\$2-(\$D\$5*F5))/\$D\$6,0) G6 =ROUNDUP((\$E\$2-(\$D\$5*G5))/\$D\$6,0) E7 =(\$D\$5*E5)+(\$D\$6*E6) F7 =(\$D\$5*F5)+(\$D\$6*F6) G7 =(\$D\$5*G5)+(\$D\$6*G6) E8 =E7-\$E\$2 F8 =F7-\$E\$2 G8 =G7-\$E\$2 E10 {=INDEX(E4:G4,0,MATCH(MIN(E8:G8),E8:G8,0))}
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!
hi, thanks a lot for your formula. i have found another two formulas. but need support column. (column "B"). can u pls check this, i want get this answers without supporting column (column "B"). thanks..
Download Weights v2.xlsx from Sendspace.com - send big files the easy way

10. ## Re: Formula help

In your spreadsheet try replace Bn with An+0.5
E.g. C2=IF((A2+0.5)<2,"",IF(MOD(A2+0.5,\$C\$1)<=1,INT((A2+0.5)/\$C\$1),INT((A2+0.5)/\$C\$1)+1))
E.g. D2=IF((A2+0.5)<=1,1,IF((A2+0.5)<=2,2,IF(A2+0.5-(C2*\$C\$1)=0,"",IF(A2+0.5-\$C\$1*C2<0,"",1))))

ps: The formula looks inaccurate to me, say in your spreadsheet row 7-11 wouldn't it be better if you choose 1 1.5kg instead of 2 1kg (which is stated in your criteria to select the best option by having least difference)?

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•