# Round last integer to the nearest value from a list

#### eblack93

##### New Member
Hi, my boss wants me to create a formula for the prices of his products. He wanted all prices below 99.99 to be rounded up to xx.97 and the values from 99.99 and greater to be rounded up to xx.00 and I have managed to put together a round formula like this: "=IF(H1416<99.99,ROUND(H1416,0)-0.03,IF(H1416>=99.99,ROUND(H1416,0)))" and that worked for the decimals. However, I need to change the last integer before the decimal point while keeping the condition described above.

For all prices below 99.99, the last integer should end in 0, 3, 5, or 9.
For example:
21.97 would be 20.97 since zero is closer than 3, 5, and 9
56.97 would be 55.97 since five is closer than 0, 3, and 9

For all prices from 99.99 and greater, the last integer should end in 0, 3, or 9. (No 5 for this selection)
For example:
174.00 would be 173.00 since three is closer than 0 and 9

I tried using Index and Match but that got me nowhere and adding another condition to the original IF formula posted above, got me with the popup that I had too many arguments. Any ideas on what to do next? I feel like I have reached a roadblock.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
What about prices where the nearest integer is equidistant whether rounding up or down?
For prices below 99.99, if the last digit of the integer is 7, should it be rounded down to 5 or up to 9?
Same question applied to prices above 99.99 when the last digit is 6, round down to 3 or up to 9?

• eblack93
What about prices where the nearest integer is equidistant whether rounding up or down?
For prices below 99.99, if the last digit of the integer is 7, should it be rounded down to 5 or up to 9?
Same question applied to prices above 99.99 when the last digit is 6, round down to 3 or up to 9?
Good question! I forgot to add it but ideally (and if possible), it would be the nearest and greatest value.

For prices below 99.99: If the last integer is 7, the ideal rounded-up number would be 9 instead of 5.
From 99.99 and above: If the last integer is 6, the ideal rounded-up number would be 9 instead of 3.

There are still ways that the logic could be misinterpreted, I think that this should be somewhere close but it is possible that there may be some discrepancies depending on whether the 'correct' method would be to adjust the integer or the decimal first.
Excel Formula:
``=ROUNDDOWN(H1416,10)+LOOKUP(MOD(H1416,10),IF(H1416<=99.99,{0,1.49,3.99,6.99},{0,1.49,5.99}),IF(H1416<=99.99,{0,3,5,9},{0,3,9}))+IF(H1416<=99.99,0.97)``

• eblack93
There are still ways that the logic could be misinterpreted, I think that this should be somewhere close but it is possible that there may be some discrepancies depending on whether the 'correct' method would be to adjust the integer or the decimal first.
Excel Formula:
``=ROUNDDOWN(H1416,10)+LOOKUP(MOD(H1416,10),IF(H1416<=99.99,{0,1.49,3.99,6.99},{0,1.49,5.99}),IF(H1416<=99.99,{0,3,5,9},{0,3,9}))+IF(H1416<=99.99,0.97)``
Thank you but this would not work.

I put the formula on a 5.70 price and a 216.64 price and ideally, the formula would change them to 5.97 and 215.00 (or 219.00) respectively but the prices I ended up with were 11.67 and 225.64.

I was thinking of maybe applying a formula to the integers first, have the resulting value in a separate cell and from that cell applying another formula just for the decimals.

Thank you but this would not work.
It will, I had a brainfart and used the wrong function at the start of the formula.
Book1
HI
14165.75.97
1417216.64219
Sheet1
Cell Formulas
RangeFormula
I1416:I1417I1416=FLOOR(H1416,10)+LOOKUP(MOD(H1416,10),IF(H1416<=99.99,{0,1.49,3.99,6.99},{0,1.49,5.99}),IF(H1416<=99.99,{0,3,5,9},{0,3,9}))+IF(H1416<=99.99,0.97)

• eblack93
It will, I had a brainfart and used the wrong function at the start of the formula.
Book1
HI
14165.75.97
1417216.64219
Sheet1
Cell Formulas
RangeFormula
I1416:I1417I1416=FLOOR(H1416,10)+LOOKUP(MOD(H1416,10),IF(H1416<=99.99,{0,1.49,3.99,6.99},{0,1.49,5.99}),IF(H1416<=99.99,{0,3,5,9},{0,3,9}))+IF(H1416<=99.99,0.97)
SIR! you are the goat. This did the trick! THANK YOU!!!

Is there any chance you could explain to me the syntax used? I would love to learn so I can modify it if necessary or apply it for any future task lol

Is there any chance you could explain to me the syntax used?
The question that I always dread someone asking ? I can write formulas easier than I can explain them. In hindsight, I should have written it slightly differently to make it easier for others to follow.
Book1
HI
14165.75.97
1417216.64219
Sheet1
Cell Formulas
RangeFormula
I1416:I1417I1416=FLOOR(H1416,10)+IF(H1416<=99.99,LOOKUP(MOD(H1416,10),{0,1.49,3.99,6.99},{0.97,3.97,5.97,9.97}),LOOKUP(MOD(H1416,10),{0,1.49,5.99},{0,3,9}))
Hopefully this will make some sense.

The original formula was split into 3 parts that are added together to give you the final figure, the new one is done in 2 parts with the 3rd part being included in the 2nd. The 1st part is fairly simple:-
Excel Formula:
``FLOOR(H1416,10)``
this rounds the original figure down to the nearest integer of 10.

The 2nd part makes the adjustments to the last digit and the decimal as needed. First of all, a simple IF to determine if the value is above or below 99.99, followed by 2 LOOKUP formulas, 1 for each result of the IF logical test. Taking the 1st lookup for values <=99.99
Excel Formula:
``LOOKUP(MOD(H1416,10),{0,1.49,3.99,6.99},{0.97,3.97,5.97,9.97})``
The MOD function is used to extract the last digit of the integer and the decimal part. The formula then looks at the first array {0,1.49,3.99,6.99} to find the lowest value that is less than or equal to the MOD value then returns the corresponding value from the second array.

The values in the first array are set as the upper limit for rounding down (the first value must be 0 to prevent errors), the second value is set at 1.49, meaning that 0 to 1.49 will round down to 0, anything from 1.49 to 3.98 will round down to 1.49 (second item in the array, which will then return 3.97 as the second item from the second array as the final result.

The table below might make it easier to follow.
 From To Result 0 1.48 0.97 1.49 3.98 3.97 3.99 6.98 5.97 6.99 9.99 9.97
The second lookup works in the same way for values greater than 99.99 with the figures in the array adjusted accordingly.

Now that I've gone through it to explain it, I'm not entirely convinced that I have used the correct figures in the array so it may need some adjustments.

• eblack93
Sir, words cannot thank you enough for explaining and for your time!

Replies
4
Views
86
Replies
3
Views
229
Replies
6
Views
534
Replies
2
Views
497
Replies
10
Views
358

### Forum statistics

1,203,739
Messages
6,057,075
Members
444,903
Latest member
Mavericx ### 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.

### Which adblocker are you using?    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

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