Rounding to a specific number

J

jgarland

Board Regular
Joined
Nov 6, 2005
Messages
50
I've searched the web and this forum but cannot find out how to do the following.

If the cost of an item is between, say 5.01 & 5.50 then round it to 5.50.

If the cost is between 5.51 and 6.00 then round it to 5.95.

EXAMPLES:

5.01 would be 5.50
5.51 would be 5.95
6.15 would be 6.50
6.80 would be 6.95

cost would be in column A. I've tried all kinds of "IF", "AND", "ROUND", & "ROUNDUP combinations but can't get it to work.

Any ideas?
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Sort by date Sort by votes
Maybe something like this
=IF(A1-INT(A1)<=0.5,(INT(A1)+0.5),(INT(A1)+0.95))

Sometimes I get regional errors since I work in spanish excel, so if you see a ; instead of , or you don't write 0.5 with a dot but with a , make the change pls ^^
 
Last edited:
Upvote 0
Solution
I know this is probobly not the best solution, but thsi should do what you need:

=IF(RIGHT(A1,2)<"50",REPLACE(A1,3,2,50),SUM(A1+(ROUNDUP(A1,0))-A1-0.05))

Cheers, BMRC
 
Upvote 0
Try

=IF(MOD(A1,1)<0.5,INT(A1)+CEILING(MOD(A1,1),0.5),INT(A1)+CEILING(MOD(A1,1),0.95))
 
Upvote 0
el_ja said:
Maybe something like this
=IF(A1-INT(A1)<=0.5,(INT(A1)+0.5),(INT(A1)+0.95))

Sometimes I get regional errors since I work in spanish excel, so if you see a ; instead of , or you don't write 0.5 with a dot but with a , make the change pls ^^
Click to expand...

Thanks - works great!!!!

One last option. Can you make this formula do a markup on the price and then round up to either .50 or .95?

Example:

cost - 28.00
marked up to 35.22 using a multiplier
round result up to 35.50

or

cost - 28.00
marked up to 35.61 using a multiplier
round result up to 35.95

Also thanks to the other folks for their input. All work to some degree but this one best fits my needs.
 
Upvote 0
I don't quite understand that part but if you have
price 28 in A1
multiplier 1.26 in A2
and final price 35.5 in A3
And want to round up the final outcome point the last formula in A3.
Althought I think this is not what you need.
 
Upvote 0
el_ja said:
I don't quite understand that part but if you have
price 28 in A1
multiplier 1.26 in A2
and final price 35.5 in A3
And want to round up the final outcome point the last formula in A3.
Althought I think this is not what you need.
Click to expand...

enter 28.00 in A1

A2 contains the multiplier of 1.26 (variable)

result in A3 would be 35.28 (A1 x A2) - this result would then be rounded up to 35.50 using the formula in your first post (or 35.95 if the A3 result was more then 35.50 and less then 36.00)

I may be explaining it badly but hope this makes sense.
 
Upvote 0
Quote:
<table border="0" cellpadding="6" cellspacing="0" width="100%"> <tbody><tr> <td style="border: 1px inset;" class="alt2"> Originally Posted by mikerickson
How about
=Int(a1) + CEILING(MOD(A1,1), 0.49) +.01
</td> </tr> </tbody></table>
thanks for the reply but it doesen't accomplish what I'm trying
Click to expand...
to do.

How doesn't it? Just change the cell reference to A3.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Conditional formatting using formula to always round up a number
Replies
1
Views
370
arthurbr
arthurbr
A
  • Solved
Comparing two excel files for matching / mismatching rows (long text strings - match() wont work
Replies
4
Views
979
ahaye
A
G
  • Solved
Index Match & round vs specific cell by cell calculation
Replies
5
Views
439
Fluff
Fluff
N
  • Solved
Problem populating table based off two criteria from a different table
Replies
1
Views
874
Nade85
N
C
  • Question Question
Week number issues and filtering data
Replies
0
Views
512
CS297
C

Forum statistics

Threads
1,224,518
Messages
6,179,258
Members
452,901
Latest member
LisaGo

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top