Rounding up/down to .95
in [General]
|
Prev: How do I link cells between to sheets in MS Excel?
Next: How add a digit to front of number in each cell in excexl column
From: MarcV on 6 Feb 2010 16:00 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.
From: H�ctor Miguel on 6 Feb 2010 16:38 hi, Marc ! try with something like: [A3] =int(a1*a2)+1-0.05 hth, hector. __ OP __ > 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? ...
From: Bernd P on 6 Feb 2010 16:59 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
From: HagridC on 6 Feb 2010 17:07 =ROUNDUP(A1*A2,0)-0.05 -- Have a Great Day! HagridC "MarcV" wrote: > 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. > >
From: David-Melbourne-Australia on 6 Feb 2010 17:16
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 "MarcV" wrote: > 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. > > |