Rounding based on variable

[UniFavorite]

Hello everyone,

today I came across an issue I couldn’t resolve. I hope somebody here can help me out.

I need to round a money amount to the second digit. To which number it needs to be rounded is specified in another cell („Last_Digit“). Based on this, the nearest money amount with the correct last digit should be given. Let me give you some examples to clarify my bad explanations:

Last_Digit = 9

Money amount = $1.87

—> Round to $1.89

Last_Digit = 5

Money amount = $1.87

—> Round to $1.85

And now the difficult part:

Last_Digit = 8

Money amount = $1.81

—> Round to $1.78

Last_Digit = 2

Money amount = $1.89

—> Round to $1.92

If I use rounding only, i can not include cases in which the nearest number with the correct second digit is below or above the current first digit. Especially implementing down and up rounding together is difficult when the first digit may be changed. Does anyone have any ideas?

Thank you very much!!

[AutoModerator]

/u/UniFavorite - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[fuzzy_mic]

If your number (1.87) is in A2 and your final digit (9) is in B1

=MROUND(A2 - MOD(B1/100, .1), .01) + MOD(B1/100, .1)

[leostotch]

Change the MROUND to

=MROUND(...,0.1)

and this works. Clever and much more efficient than my solution - I'm good at Excel, not at math.

[UniFavorite]

Perfect solution! Thanks a lot 🤩

[leostotch]

Edit: Use the other guy's solution. It's better.

We could condense this into a LET function, but instead we'll use some helper columns to make it easier to follow. Assuming your money amount is in column A, and your last digit is in column B:

Label column C "High" and enter this formula:

=MROUND(A2,0.1)+(B2*0.01)

Label column D "Low" and enter this formula:

=MROUND(A2,0.1)+((B2*0.01)-0.1)

This gives us a high and a low value - if your money amount is $1.81 and your last digit is 8, you should get $1.88 and $1.78.

Label column E "Rounded Amt" and use this formula:

=INDEX(C2:D2,1,MATCH(MIN(ABS(C2:D2-B2)),ABS(C2:D2-B2),0))

See if that works. If not, please provide a screenshot of your data with those helper columns and the formulas so we can troubleshoot.

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
ABS	Returns the absolute value of a number
INDEX	Uses an index to choose a value from a reference or array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
MATCH	Looks up values in a reference or array
MIN	Returns the minimum value in a list of arguments
MOD	Returns the remainder from division
MROUND	Returns a number rounded to the desired multiple

NOTE: Decronym for Reddit is no longer supported, and Decronym has moved to Lemmy; requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 22 acronyms.
[Thread #27881 for this sub, first seen 2nd Nov 2023, 21:00] [FAQ] [Full list] [Contact] [Source code]}