Round last integer to the nearest value from a list

[eblack93]

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.

 

[jasonb75]

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.

 

[jasonb75]

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.

=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.

=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]

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]

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
	H	I
1416	5.7	5.97
1417	216.64	219
Sheet1

Cell Formulas
Range	Formula
I1416:I1417	I1416	=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
	H	I
1416	5.7	5.97
1417	216.64	219
Sheet1

Cell Formulas
Range	Formula
I1416:I1417	I1416	=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]

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
	H	I
1416	5.7	5.97
1417	216.64	219
Sheet1

Cell Formulas
Range	Formula
I1416:I1417	I1416	=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:-

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

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!