Formula to Round Prices

**rnbblsmm** · 03-23-2015, 02:57 PM

Hey there! So my housemate helped me create the formula below. What I needed was a formula that would take the prices in one column and then round each price up or down to the nearest 9. Does anyone have a suggestion of how to include a nesting function that would make an exception to the rule below (.03 and below round down, and .04 and up, round up). The exception would be that if that anything is priced within .10 above $1.00 should be rounded down to the nearest .99. (for example 4.09 rounded down to 3.99)

=IF(J2-FLOOR(J2,0.1)>0.03,FLOOR(J2,0.1)+0.09,FLOOR(J2,0.1)-0.01)

Any help would be greatly appreciated!

Thank you!

Hope everyone is having a lovely day

**mikeTRON** · 03-23-2015, 03:27 PM

...the additional logic, you want it to be implemented BEFORE any of the formula above?

**rnbblsmm** · 03-23-2015, 03:51 PM

It would be an either or kind of thing. I confess that I am kind of an excel laymen. The formula might need to be rewritten in order to include all the logic I am looking for.

Ideally, the formula would judge whether the prices in column X were within .10 of the dollar and would then round those numbers down to the nearest .99 OR for any price that did not meet that criteria, excel would round it up or down based on the rule I mentioned in my original post (.03 and below round down, and .04 and up, round up).

Here's a screen shot of the spreadsheet.

Screen shot 2015-03-23 at 3.57.47 PM.png

I'm looking for a formula that would keep all these prices the way they have been calculated, but if there was a price that was >.10 from a $1.00, I would want it rounded to the nearest .99. For example: anything between $3.01-$3.10, I would want it rounded down to $2.99.