Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: Formula help

  1. #1
    Board Regular
    Join Date
    Sep 2009
    Posts
    693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Post 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. #2
    Board Regular
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    361
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    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!

  3. #3
    Board Regular
    Join Date
    Sep 2009
    Posts
    693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    Board Regular
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    361
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

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

  5. #5
    Board Regular
    Join Date
    Sep 2009
    Posts
    693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    361
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular
    Join Date
    Sep 2009
    Posts
    693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    yes. is this possible?. thanks.

  8. #8
    Board Regular
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    361
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

    Spreadsheet Formulas
    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. #9
    Board Regular
    Join Date
    Sep 2009
    Posts
    693
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula help

    Quote Originally Posted by alvin-chung View Post
    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

    Spreadsheet Formulas
    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. #10
    Board Regular
    Join Date
    Nov 2013
    Location
    Singapore
    Posts
    361
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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)?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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