Thread: Round Value Based On Criteria Thanks:  2 Post #5329105 (1)Post #5329103 (1) Likes: 0

1. 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. 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)))

3. 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

4. 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.