Round Value Based On Criteria

joshuad350

New Member
Joined
Feb 12, 2019
Messages
2
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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
Cell Formulas
RangeFormula
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)))
 
Upvote 0
additional if you want to pursue the INDEX/MATCH setup you've already started.


Book1
ABCDEFG
1itemqtydescfruitinputoutput
2Apples100boxApples1520
3Apples50large bagOranges1525
4Apples20small bagWatermelons22
5Oranges50box
6Oranges25bag
7Watermelons1eachfruitinputoutput
8Apples4550
9Oranges3050
10Watermelons55
11
12
13fruitinputoutput
14Apples86100
15Oranges525
16Watermelons00
Sheet1
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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?

Disable AdBlock

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
Back
Top