Earlier this week I saw an interesting rounding question on the famous MrExcel Forum. And there was a twist!

# Excel File

Download the Excel file and follow along.

# Original Question

## Link

Link to the original post on MrExcel’s amazing AND free help forum.

## Description

I created this Excel file to explain:

Original values in cells A2:A5 (I added more below). Solution in column B.

## Solution

Cell B2 formula:

=ROUNDUP(A2,-INT(LOG10(A2)))

A brilliant formula from one of the forum members. It rounds like this:

- $12,036,219.22 to $20,000,000.00
- $437,379.70 to $500,000.00

# The Twist

## Twist Definition

$12,036,219.22 (numbers with 8 digits to the left of decimal) should round to $13,000,000.00 (not $20,000,000.00). This is the only exception.

## Twist Solution 1

With only 1 exception why not use the IF function? Formula in cell D2:

=ROUNDUP(A2,IF(-INT(LOG10(A2))=-7,-6,-INT(LOG10(A2))))

**-INT(LOG10(A2))** determines the rounding level. IF function tests for the exception changing rounding level only for the exception.

## Twist Solution 2

Multiple exceptions? How can I make it easy to modify them?

=ROUNDUP(A2,INDEX(list!$B$1:$B$8,MATCH(-INT(LOG10($A2)),list!$A$1:$A$8,0)))

Formula looks into column A and retrieves desired rounding level in column B. It’s easy to change rounding level in column B.

Note: cell A8 value of -7 is the exception. It rounds to -6 (not -7).

## Twist Solution 3

Can I remove the lookup table? I created this:

=ROUNDUP(A2,INDEX({0;-1;-2;-3;-4;-5;-6;-6},MATCH(-INT(LOG10($A2)),{0;-1;-2;-3;-4;-5;-6;-7},0)))

It uses two array constants instead of a lookup table.

Modify desired rounding levels inside INDEX function as needed: **INDEX({0;-1;-2;-3;-4;-5;-6;-6}**

# Creating an Array Constant

Twist Solution 3 has two array constants. I could type values directly in the formula but the syntax is tricky. I modified Twist Solution 2 formula.

Twist Solution 2: =ROUNDUP(A2,INDEX(list!$B$1:$B$8,MATCH(-INT(LOG10($A2)),list!$A$1:$A$8,0))) --see "5 Steps..." further down to convert formula Twist Solution 2 into formula Twist Solution 3: Twist Solution 3: =ROUNDUP(A2,INDEX({0;-1;-2;-3;-4;-5;-6;-6},MATCH(-INT(LOG10($A2)),{0;-1;-2;-3;-4;-5;-6;-7},0)))

5 Steps to convert sheet list references into array constants:

- in formula bar highlight
**list!$B$1:$B$8** - press F9 key (on laptop: hold Fn key then press F9 key) to hard code values
- still in formula bar highlight
**list!$A$1:$A$8** - press F9 key (on laptop: hold Fn key then press F9 key) to hard code values
- press Enter key (to permanently hard codes values)

The formula should now look like this:

=ROUNDUP(A2,INDEX({0;-1;-2;-3;-4;-5;-6;-6},MATCH(-INT(LOG10($A2)),{0;-1;-2;-3;-4;-5;-6;-7},0)))

Finally, show a co-worker your amazing formula ðŸ™‚

# MrExcel Help Forum

Bill Jelen (aka MrExcel) created his forum over 20 years ago. It’s an incredible free source to answer Excel related questions. Bill also writes/publishes books, offers consulting services, and has a popular YouTube channel.

# About Me

I’m a Data Analyst and major Excel fan. I live in Markham Ontario Canada.