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

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
Joined
Aug 18, 2011
Messages
6,556
Office Version
  1. 365
Platform
  1. Windows
Hi, welcome to your first post!

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

<b>Excel 2013/2016</b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">item</td><td style=";">qty</td><td style=";">desc</td><td style="text-align: right;;"></td><td style=";">fruit</td><td style=";">input</td><td style=";">output</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Apples</td><td style="text-align: right;;">100</td><td style=";">box</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">15</td><td style="text-align: right;background-color: #FFFF00;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Apples</td><td style="text-align: right;;">50</td><td style=";">large bag</td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">15</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Apples</td><td style="text-align: right;;">20</td><td style=";">small bag</td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Oranges</td><td style="text-align: right;;">50</td><td style=";">box</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">45</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Oranges</td><td style="text-align: right;;">25</td><td style=";">bag</td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">30</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Watermelons</td><td style="text-align: right;;">1</td><td style=";">each</td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">86</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">5</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">=IF(<font color="Blue">F2=0,0,IF(<font color="Red">VLOOKUP(<font color="Green">E2,$A$2:$C$7,3,0</font>)="Each",F2,AGGREGATE(<font color="Green">15,6,(<font color="Purple">$B$2:$B$7</font>)/(<font color="Purple">(<font color="Teal">$A$2:$A$7=E2</font>)*(<font color="Teal">$B$2:$B$7>=F2</font>)</font>),1</font>)</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
 

jorismoerings

Well-known Member
Joined
Jul 4, 2014
Messages
1,478
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
additional if you want to pursue the INDEX/MATCH setup you've already started.

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="font-weight: bold;;">item</td><td style=";">qty</td><td style=";">desc</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">fruit</td><td style=";">input</td><td style=";">output</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="color: #0000FF;;">Apples</td><td style="text-align: right;;">100</td><td style=";">box</td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">15</td><td style="text-align: right;;">20</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="color: #0000FF;;">Apples</td><td style="text-align: right;;">50</td><td style=";">large bag</td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">15</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="color: #0000FF;;">Apples</td><td style="text-align: right;;">20</td><td style=";">small bag</td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="color: #0000FF;;">Oranges</td><td style="text-align: right;;">50</td><td style=";">box</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style="color: #0000FF;;">Oranges</td><td style="text-align: right;;">25</td><td style=";">bag</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="color: #0000FF;;">Watermelons</td><td style="text-align: right;;">1</td><td style=";">each</td><td style="text-align: right;;"></td><td style="font-weight: bold;;">fruit</td><td style=";">input</td><td style=";">output</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style="text-align: right;color: #0000FF;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">45</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">30</td><td style="text-align: right;;">50</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="font-weight: bold;;">fruit</td><td style=";">input</td><td style=";">output</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Apples</td><td style="text-align: right;;">86</td><td style="text-align: right;;">100</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Oranges</td><td style="text-align: right;;">5</td><td style="text-align: right;;">25</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Watermelons</td><td style="text-align: right;;">0</td><td style="text-align: right;;">0</td></tr></tbody></table><p style="width:3,6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">G2</th><td style="text-align:left">{=IF(<font color="Blue">INDEX(<font color="Red">$C$2:$C$7,MATCH(<font color="Green">E2,$A$2:$A$7,0</font>)</font>)="each",F2,(<font color="Red">INDEX(<font color="Green">$B$2:$B$7,MATCH(<font color="Purple">F2,IF(<font color="Teal">$A$2:$A$7=E2,$B$2:$B$7,""</font>),-1</font>)</font>)</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 
Last edited:

joshuad350

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

Forum statistics

Threads
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?

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
Top