Rounding to a specific number

[jgarland]

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?

 

[shg]

How about,

=INT(A1)+0.5+0.45*(MOD(A1,1)>0.5)

 

[jgarland]

to do.

How doesn't it? Just change the cell reference to A3.

Sorry, I should have explained it better.

I may be looking at this wrong, if so please enlighten me. I need to restrict the whole equation to no more than 3 cells.
A1 - cost of 28.00
A2 - multiplier of 1.26
A3 - enter formula here that takes the cost in A1, multiplies it by the multipler in A2, and rounds it up to either ##.50 or ##.95.

The above formula requires 4 cells to accomplish the end result.
A1 - cost of 28.00
A2 - multiplier of 1.26
A3 - result of A1 x A2
A4 - above formula referencing the A3 result

 

[jgarland]

How about,

=INT(A1)+0.5+0.45*(MOD(A1,1)>0.5)

Hi, thanks for responding.

It works the same as MikeRickson's formula, please see my post just above this one for possibly a better eplanation of what I'm trying to do.

 

[shg]

How about providing an example of what the formula returns compared to what you want or expect?

 

[jgarland]

How about providing an example of what the formula returns compared to what you want or expect?

Hi.

I want to take the cost of an item then increase that cost by a set percent. The result would then by rounded up to either ###.50 or ###.95 - depending on the result.

Example A:
A1 - enter the cost of an item - say 28.00 (this is variable)
A2 - this is where the multiplier would be - say 1.26 (also variable)
A3 - cost in A1 multiplied by multiplier in A2 equals 35.28 - since the result in A3 is over 35.00 but less then 35.50 i want to round up to 35.50.

Example B:
A1 - cost of an item - 104.63
A2 - multiplier - 1.49
A3 - cost in A1 multiplied by multiplier in A2 equals 155.89 - since the result in A3 is over 155.50 but less then 156.00 I want to round up to 155.95.

I want the end cost of the item rounded up to ###.50 if it is higher then ###.00 but lower then ###.50, or ###.95 if it is higher then ###.50 but lower then ###.99

RESULTS EXAMPLES:

22.48 becomes 22.50
75.01 becomes 75.50
3.00 becomes 3.50
13.55 becomes 13.95
84.63 becomes 84.95
22.99 becomes 22.95

 

[shg]

=int(a1*a2)+0.5+0.45*(mod(a1*a2,1)>0.5)

 

[jgarland]

=int(a1*a2)+0.5+0.45*(mod(a1*a2,1)>0.5)

You Da Man !!! (or Woman, as the case may be )

That is exactly what I was after. Thank you so much. I've been working on this for days and couldn't figure it out.

Thanks also to all the other folks that pitched in. Every contribution is appreciated.

 

[jgarland]

=int(a1*a2)+0.5+0.45*(mod(a1*a2,1)>0.5)

shg, would it be possible for you to explain exactly how that formula calculates what it does? I'm trying to wrap my head around it but I'm having a major brain fart .

I can figure most of it but don't know what the >0.5 on the end does.