Rounding up/down to .95

  • Thread starter Thread starter MarcV
  • Start date Start date
M

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.
 
hi, Marc !

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

hth,
hector.

__ OP __
 
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
 
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
 
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
 
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.
Click to expand...

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
 
Back
Top