Rounding up/down to .95

[MarcV]

This is a Mircosoft Office 2007 Excel spread sheet.

I need to figure out a formula that can round up or down to $xx.95 using the
following situation

cost: 18.23
mark up: 1.8
Retail-based on this alone would be $32.81

A1= Cost
A3= Retail
A2= Mark up

Formula used in A3 is- =A1*A2

My problem is that the owner wants all cents to be rounded up or down to .95
and costs are all different throught the cost columns.

Is there a formula that can be entered to do such a funtion?

Thanks for any assistance you can offer.

 

[Héctor Miguel]

hi, Marc !

try with something like: [A3] =int(a1*a2)+1-0.05

hth,
hector.

__ OP __

 

[Bernd P]

Hello,

I suggest to use
=ROUND(A1*A2+0.05,0)-0.05

Please check against Hectors suggestions with values like 0.44 and
0,45 which version you really need...

Regards,
Bernd

 

[HagridC]

=ROUNDUP(A1*A2,0)-0.05

 

[David-Melbourne-Australia]

Hi MarcV

The formulae to go in A3 is:

= INT(A1*A2) + IF( ROUND( MOD(A1*A2 , 1) ,2) < 0.45 , -1 , 0) + 0.95

If instead you want to leave the formula you currently have in A3 and enter
the above in A4, it would read:

= INT(A3) + IF( ROUND( MOD(A3 , 1) ,2) < 0.45 , -1 , 0) + 0.95

This would give you the means to check each result, though I have tested the
above and it seemed to work fine for me.

Good luck. Hope this helps.

David

 

[David-Melbourne-Australia]

Hi again

Just noticed HagridC's response. Just to clarify:

If you have $31.01 and you wanted it to be rounded up to $31.95, then
HagridC's formula will do that. His formula basically works on even if there
is just one cent, round up to the next 95 cents.

I assumed you wanted rounding based on the 45c mark, so if is $31.44, it
will round down to $30.95, but $31.45 will round up to $31.95. If this is
the case, use my formula.

Hope this clarifies.

David

 

[HagridC]

My mistake. I neglected to consider the round down.

 

[Ron Rosenfeld]

This is a Mircosoft Office 2007 Excel spread sheet.

I need to figure out a formula that can round up or down to $xx.95 using the
following situation

cost: 18.23
mark up: 1.8
Retail-based on this alone would be $32.81

A1= Cost
A3= Retail
A2= Mark up

Formula used in A3 is- =A1*A2

My problem is that the owner wants all cents to be rounded up or down to .95
and costs are all different throught the cost columns.

Is there a formula that can be entered to do such a funtion?

Thanks for any assistance you can offer.

If you are rounding "up or down", where is the "dividing line". In other
words, at what value to you change from rounding down to rounding up?
--ron