If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Round up or down to $X.X9 or up to $X.X5
From your description & examples, I thought we were only dealing with dollars
& cents. From my further testing, it looks like only Sheeloo's second formula works correctly. Here is a revised version of mine that also works: =IF(MOD(CEILING(ROUND(A1,2),0.05)*100,10)0.005,CE ILING(ROUND(A1,2),0.05)-0.01,CEILING(ROUND(A1,2),0.05)) Besides the need to round of the extra decimals, I saw that MOD was not returning zero, but a very small value like 0.000000000000000567. Those two factors caused the erroneous results in my previous formula. Hutch "GoBucks" wrote: Tom your formula was close, but there were still a few rounding issues: $3.40 remained $3.40 $2.50 became $2.55 $2.38 became $2.40 I'm guessing that I will need to truncate the original value at only two decimal points and then see if the formula works. What would I need to do to truncate the original value two only two decimal points? (e.g. 4.487563 becomes 4.49) Thanks "Tom Hutchins" wrote: This isn't elegant, but seems to work: =IF(MOD(CEILING(E4,0.05)*100,10)=0,CEILING(E4,0.05 )-0.01,CEILING(E4,0.05)) Hope this helps, Hutch "GoBucks" wrote: I'm working on a pricing worksheet that calculates a product cost + freight+margin to come up with a suggested product price. What I'd like to be able to do is write a formula that converts all pricing to end with a 5 or 9. If the price already ends in a 5 or 9, nothing should change. If the price ends in a 1,2,3 or 4, it should roundup to a 5. If the price ends in 6,7,8 it should round up to 9. If the pricing ends in zero, it should round down to 9. For instance: $4.24 becomes $4.25 $3.26 becomes $3.29 $2.00 becomes $1.99 I'm guessing this will result in a doozy of a formula, but thought I'd check. Thanks in advance for your replies! |
|
Thread Tools | |
Display Modes | |
|
|