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: David-Melbourne-Australia on 6 Feb 2010 17:20 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 "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: HagridC on 6 Feb 2010 18:33 My mistake. I neglected to consider the round down. -- Have a Great Day! HagridC "David-Melbourne-Australia" wrote: > 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 > > > > "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: Ron Rosenfeld on 6 Feb 2010 22:53
On Sat, 6 Feb 2010 13:00:03 -0800, MarcV <MarcV(a)discussions.microsoft.com> 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. > 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 |