# Round Value Based On Criteria

##### New Member
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.

### Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

#### FormR

##### MrExcel MVP
Hi, welcome to your first post!

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

#### jorismoerings

##### Well-known Member
additional if you want to pursue the INDEX/MATCH setup you've already started.

<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

Last edited:

##### New Member
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.

Replies
1
Views
103
Replies
3
Views
91
Replies
12
Views
136
Replies
4
Views
103
Replies
6
Views
105

1,141,203
Messages
5,704,941
Members
421,372
Latest member
Jamie11

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back