Round last integer to the nearest value from a list

eblack93

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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.
EXCEL_JogIVSZFre.png


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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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.
 

eblack93

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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.

My head is about to burst lol
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 
Solution

eblack93

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
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
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,630
Office Version
  1. 365
Platform
  1. Windows
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.
FromToResult
01.480.97
1.493.983.97
3.996.985.97
6.999.999.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

New Member
Joined
Oct 1, 2021
Messages
11
Office Version
  1. 2019
Platform
  1. Windows
Sir, words cannot thank you enough for explaining and for your time!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,394
Messages
5,769,828
Members
425,574
Latest member
grimeslisa

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