# Help with a formula please.

=?Utf-8?B?Um9yeQ==?=
Guest
Posts: n/a

 24th Apr 2006
Can anyone write a formula to do the following,
Add 53 % to a cell with a figure in it then round of the pence to 95 ie
101.95 also if possible if i drag down a coloum of figures to apply the
formula to, ignore any cells in the coloum with have a nil value.
--
Rory Moore

=?Utf-8?B?VG9wcGVycw==?=
Guest
Posts: n/a

 24th Apr 2006
Do you always round up or down?

127*1.53=194.31 ==> 193.95 or 194.95?
137*1.53=209.61 ==> 208.95 or 209.95?

If it up, take integer value and add 0.95

A1=Value
B1=%

=IF(A1<>0,INT(A1*(1+B1))+0.95,0)

Round down

=IF(A1<>0,INT(A1*(1+B1)-1)+0.95,0)

HTH

"Rory" wrote:

> Can anyone write a formula to do the following,
> Add 53 % to a cell with a figure in it then round of the pence to 95 ie
> 101.95 also if possible if i drag down a coloum of figures to apply the
> formula to, ignore any cells in the coloum with have a nil value.
> --
> Rory Moore

=?Utf-8?B?VG9wcGVycw==?=
Guest
Posts: n/a

 24th Apr 2006
.... change to

=IF(A1<>0,INT(A1*(1+B1))+0.95,"") if you nil values to return blank

"Toppers" wrote:

> Do you always round up or down?
>
> 127*1.53=194.31 ==> 193.95 or 194.95?
> 137*1.53=209.61 ==> 208.95 or 209.95?
>
> If it up, take integer value and add 0.95
>
> A1=Value
> B1=%
>
> =IF(A1<>0,INT(A1*(1+B1))+0.95,0)
>
> Round down
>
> =IF(A1<>0,INT(A1*(1+B1)-1)+0.95,0)
>
> HTH
>
> "Rory" wrote:
>
> > Can anyone write a formula to do the following,
> > Add 53 % to a cell with a figure in it then round of the pence to 95 ie
> > 101.95 also if possible if i drag down a coloum of figures to apply the
> > formula to, ignore any cells in the coloum with have a nil value.
> > --
> > Rory Moore

 Thread Tools Rate This Thread Rate This Thread: 5 : Excellent 4 : Good 3 : Average 2 : Bad 1 : Terrible

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts BB code is On Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are Off Forum Rules

 Similar Threads Thread Thread Starter Forum Replies Last Post =?Utf-8?B?bGVjYWJhbGxlcm8=?= Microsoft Excel Programming 1 2nd Nov 2005 12:46 PM Jonathan Windows XP General 10 13th Jan 2005 03:22 AM =?Utf-8?B?U2FuYXRhcml1bV83?= Windows XP General 2 25th Sep 2004 04:00 PM -\$- Windows XP Internet Explorer 2 21st Dec 2003 11:45 PM Muxer Microsoft Excel Programming 2 24th Jul 2003 01:02 AM

Features