Round Value Based On Criteria

[joshuad350]

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 20 small bag
Apples 0
Oranges 50 box
Oranges 25 bag
Oranges 0
Watermelons 1 each
Watermelons 0

Any input or examples would be greatly appreciated. Thanks.

 

[FormR]

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
	A	B	C	D	E	F	G
1	item	qty	desc		fruit	input	output
2	Apples	100	box		Apples	15	20
3	Apples	50	large bag		Oranges	15	25
4	Apples	20	small bag		Watermelons	2	2
5	Oranges	50	box		Apples	45	50
6	Oranges	25	bag		Oranges	30	50
7	Watermelons	1	each		Watermelons	5	5
8					Apples	86	100
9					Oranges	5	25
10					Watermelons	0	0
Sheet1

Cell Formulas
Range	Formula
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)))

 

[jorismoerings]

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

Book1
	A	B	C	D	E	F	G
1	item	qty	desc		fruit	input	output
2	Apples	100	box		Apples	15	20
3	Apples	50	large bag		Oranges	15	25
4	Apples	20	small bag		Watermelons	2	2
5	Oranges	50	box
6	Oranges	25	bag
7	Watermelons	1	each		fruit	input	output
8					Apples	45	50
9					Oranges	30	50
10					Watermelons	5	5
11
12
13					fruit	input	output
14					Apples	86	100
15					Oranges	5	25
16					Watermelons	0	0
Sheet1

Cell Formulas
Range	Formula
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))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[joshuad350]

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.