Round Price Up or Down to nearest $.99 based on a Condition

**agent46** · 04-23-2013, 04:10 PM

Hello folks,

I don't know if there's any direct way to do this in Excel using some kind of nested formula in a single column or if there are other strategies you might suggest.

Basically, I have a price list. I need to test each price in the column and round down to the nearest $.99 if the price is greater than $15.00, and round up if the price is equal to or less than $15.00. I want to do this in the fewest steps possible using Excel functions.

Any suggestions are greatly appreciated.

Thanks in Advance,
~46

**Lemice** · 04-23-2013, 04:31 PM

Assuming that price is in Cell A1, try this

Please Login or Register  to view this content.

**Melvinrobb** · 04-23-2013, 04:37 PM

This may work for you:

Formula:

Please Login or Register  to view this content.

**agent46** · 04-23-2013, 04:47 PM

Thanks Guys,

Both your approaches appear to work. I didn't know how to nest Excel functions. You both gave good examples. Unfortunately, I didn't know I could only add to one of your reputations. I owe you one LEM. Yours is the simpler solution in fact.

All the Best!

~46

**Lemice** · 04-23-2013, 04:50 PM

The solution Melvin provided is universally better, because it can avoid case like price 17.99 being rounded down to 16.99, that's why it's a bit longer, but the later part is the same.

However, I cannot seem to get his formula to work, and have to tweak it into

Please Login or Register  to view this content.

They both are the same if you ask me, but for some reason on my Excel, when I test 16.99 his formula returns 15.99 instead, and I have to tweak it like the formula above to make it work

**Melvinrobb** · 04-23-2013, 05:02 PM

I've been testing the formula and running into some issues myself. Perhaps try this one:

Formula:

Please Login or Register  to view this content.

I believe the green part is needed to avoid having 16.99 being rounded down to 15.99.
Trial and error will be your best bet.

**Melvinrobb** · 04-23-2013, 05:07 PM

You MAY run into issues because the ROUND and INT function are not exact. If I have 16.99 in B2, =B2-INT(B2) is actually returning 0.98999999999999800000
You may want to use =IF(ROUND(B2-INT(B2),2)=.99,xxxxxxxxxxxxxx)

**daddylonglegs** · 04-23-2013, 05:56 PM

Originally Posted by agent46

.....round down to the nearest $.99 if the price is greater than $15.00, and round up if the price is equal to or less than $15.00. I want to do this in the fewest steps possible using Excel functions..

If you take this literally it would lead to what I would regard as inconsistent results, wouldn't $15.98 round down, giving you $14.99......but $15.00 will round up to $15.99? I would suggest that you make the cutoff point $14.99, try this formula

=ROUND(B2+0.01+IF(B2<14.99,1,-1)/2,0)-0.01