Results 1 to 4 of 4

Thread: Round Value Based On Criteria

  1. #1
    New Member
    Join Date
    Feb 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Round Value Based On Criteria

    Hi, I would like to be able to take a quantity and "round" it based on a list of rounding criteria.
    I am having trouble trying to find an exact example of what I an trying to do nor am I having any luck myself with the different formulas.

    I have created an example using fruit if someone could please point me in the right direction.
    I have the following criteria for each fruit as below. I have named the cells in blue data1 if this helps in the formula.
    I apologies in advance for the info below as I have not delved into how to add images yet.

    data1
    item qty desc
    Apples 100 box
    Apples 50 large bag
    Apples 20 small bag
    Oranges 50 box
    Oranges 25 bag
    Watermelons 1 each

    The following examples show what the user would input in red and what the resultant be after processing the criteria.
    In the case of the Apples and Oranges the output value is different due to the criteria however with the Watermelons the output value needs to be the same as the input value. The '1' criteria for Watermelons can change if need be to suit a formula in order to make it work.
    I do not need to handle the input value being greater than the largest value in the criteria as in my actual application the input qty is derived from the fraction of a resultant based on the largest value. If the formula can return a zero for a zero input value that would be great however I can handle this outside the formula if need be.

    example 1
    fruit input output
    Apples 15 20
    Oranges 15 25
    Watermelons 2 2



    example 2
    fruit input output
    Apples 45 50
    Oranges 30 50
    Watermelons 5 5


    example 3
    fruit input output
    Apples 86 100
    Oranges 5 25
    Watermelons 0 0

    I have been able to use the index and match formulas as below but it limits me to one fruit type.
    =INDEX(B10:B12,MATCH(B3,B10:B12,-1) where B10:B12 is the 3 values against the 3 apples and B3 is an input value.
    =INDEX({100, 50, 20},MATCH(15,{100, 50, 20},-1)) which returns 20.

    The criteria could also be modified to handle a zero input value as below.
    data1
    item qty desc
    Apples 100 box
    Apples 50 large bag
    Apples 20small bag
    Apples 0
    Oranges 50 box
    Oranges 25 bag
    Oranges 0
    Watermelons 1 each
    Watermelons0

    Any input or examples would be greatly appreciated. Thanks.

  2. #2
    MrExcel MVP FormR's Avatar
    Join Date
    Aug 2011
    Location
    UK
    Posts
    6,092
    Post Thanks / Like
    Mentioned
    24 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Round Value Based On Criteria

    Hi, welcome to your first post!

    This makes the assumption that items with an "each" description will only appear once in the criteria list.

    Excel 2013/2016
    ABCDEFG
    1itemqtydescfruitinputoutput
    2Apples100boxApples1520
    3Apples50large bagOranges1525
    4Apples20small bagWatermelons22
    5Oranges50boxApples4550
    6Oranges25bagOranges3050
    7Watermelons1eachWatermelons55
    8Apples86100
    9Oranges525
    10Watermelons00

    Sheet1



    Worksheet Formulas
    CellFormula
    G2=IF(F2=0,0,IF(VLOOKUP(E2,$A$2:$C$7,3,0)="Each",F2,AGGREGATE(15,6,($B$2:$B$7)/(($A$2:$A$7=E2)*($B$2:$B$7>=F2)),1)))

    [code]your code[/code]

  3. #3
    Board Regular
    Join Date
    Jul 2014
    Location
    The Netherlands
    Posts
    1,263
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Round Value Based On Criteria

    additional if you want to pursue the INDEX/MATCH setup you've already started.

    ABCDEFG
    1itemqtydescfruitinputoutput
    2Apples100boxApples1520
    3Apples50large bagOranges1525
    4Apples20small bagWatermelons22
    5Oranges50box
    6Oranges25bag
    7Watermelons1eachfruitinputoutput
    8Apples4550
    9Oranges3050
    10Watermelons55
    11
    12
    13fruitinputoutput
    14Apples86100
    15Oranges525
    16Watermelons00

    Sheet1



    Array Formulas
    CellFormula
    G2{=IF(INDEX($C$2:$C$7,MATCH(E2,$A$2:$A$7,0))="each",F2,(INDEX($B$2:$B$7,MATCH(F2,IF($A$2:$A$7=E2,$B$2:$B$7,""),-1))))}
    Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
    Note: Do not try and enter the {} manually yourself

    Last edited by jorismoerings; Aug 21st, 2019 at 04:23 AM.
    --------------------------------------------------------------------------------
    If you found my answer helpful, please hit the Like or Thank button.

    Please follow the forum Rules and Guidelines and use Code tags around your VBA code.

    Remember: test VBA code always on a copy of your file because usually you can't use <undo>

  4. #4
    New Member
    Join Date
    Feb 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Round Value Based On Criteria

    Hi and thanks for the welcome. I finally made it back.

    Your example worked a treat and handled the adjustments to suit my application.
    I have not yet had the time to work through and understand it but it is doing what I need.

    Thanks for your help.

Some videos you may like

User Tag List

Tags for this Thread

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
  •