Ending In 5

Hello,

I have a column of numbers that end in many different numbers such as 7.66,
3.43 ect. I would like to make all the numbers to end in 5. Such as 7.65 and
3.45.

Thanks JR,

Assuming your list is in column A, put the following formula in B1 and copy
down..........

=(INT(A1)&MID(A1,FIND(".",A1,1),2)&5)*1

Vaya con Dios,
Chuck, CABGx3


"JR" <gaspower@aol.com> wrote in message
news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
> Hello,
>
> I have a column of numbers that end in many different numbers such as
7.66,
> 3.43 ect. I would like to make all the numbers to end in 5. Such as 7.65
and
> 3.45.
>
> Thanks JR,
>
>

Hi!

Try this:

=ROUND(A1/0.05,0)*0.05

Biff

"JR" <gaspower@aol.com> wrote in message
news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
> Hello,
>
> I have a column of numbers that end in many different numbers such as
> 7.66, 3.43 ect. I would like to make all the numbers to end in 5. Such as
> 7.65 and 3.45.
>
> Thanks JR,
>

"JR" <gaspower@aol.com> wrote in message
news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
> Hello,
>
> I have a column of numbers that end in many different numbers such
> as 7.66, 3.43 ect. I would like to make all the numbers to end in 5.
> Such as 7.65 and 3.45.
>
> Thanks JR,
>
>

Hi JR,

Try this:

ROUND((A1+0.05)/0.1,0)*0.1-0.05


I am assuming you really did mean that you want each answer to end in
a 5 (rather than rounding to the nearest 0.05 which could result in,
say, 3.40.

If you just want to round to the nearest 0.05 then use the formula
posted by Biff.

HTH,

Alan.

>=ROUND(A1/0.05,0)*0.05

Slightly shorter
=mround(a1,0.05) for analysis tool pack

note not exactly what OP asked for as 0.38 gives 0.40 not ending in 5

cheers RES

Using this formula, half the numbers won't end in 5.

Given the examples, either

=ROUNDDOWN(A1,1)+0.05

or

=ROUNDUP(A1,1)+0.05

would be better, depending on whether a value like 3.40 should round to
3.35 or 3.45, respectively.



In article <eaoea4JbFHA.2416@TK2MSFTNGP09.phx.gbl>,
"Biff" <biffinpitt@comcast.net> wrote:

> Hi!
>
> Try this:
>
> =ROUND(A1/0.05,0)*0.05
>
> Biff
>
> "JR" <gaspower@aol.com> wrote in message
> news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
> > Hello,
> >
> > I have a column of numbers that end in many different numbers such as
> > 7.66, 3.43 ect. I would like to make all the numbers to end in 5. Such as
> > 7.65 and 3.45.
> >
> > Thanks JR,
> >

Thanks for all the help.


"JR" <gaspower@aol.com> wrote in message
news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
> Hello,
>
> I have a column of numbers that end in many different numbers such as
> 7.66, 3.43 ect. I would like to make all the numbers to end in 5. Such as
> 7.65 and 3.45.
>
> Thanks JR,
>

My suggestion is intentional.

I was, dare I say it, assuming that's what the OP wanted!

Biff

"JE McGimpsey" <jemcgimpsey@mvps.org> wrote in message
news:jemcgimpsey-DFFDE7.06204209062005@msnews.microsoft.com...
> Using this formula, half the numbers won't end in 5.
>
> Given the examples, either
>
> =ROUNDDOWN(A1,1)+0.05
>
> or
>
> =ROUNDUP(A1,1)+0.05
>
> would be better, depending on whether a value like 3.40 should round to
> 3.35 or 3.45, respectively.
>
>
>
> In article <eaoea4JbFHA.2416@TK2MSFTNGP09.phx.gbl>,
> "Biff" <biffinpitt@comcast.net> wrote:
>
>> Hi!
>>
>> Try this:
>>
>> =ROUND(A1/0.05,0)*0.05
>>
>> Biff
>>
>> "JR" <gaspower@aol.com> wrote in message
>> news:SgNpe.1436$bv7.732@newssvr21.news.prodigy.com...
>> > Hello,
>> >
>> > I have a column of numbers that end in many different numbers such as
>> > 7.66, 3.43 ect. I would like to make all the numbers to end in 5. Such
>> > as
>> > 7.65 and 3.45.
>> >
>> > Thanks JR,
>> >

Ah. I guess I just interpreted "I would like to make all the numbers to
end in 5" literally.

You may have been right, of course.

My second solution had an error - the + should have been a -.

In article <#49fPjWbFHA.464@TK2MSFTNGP15.phx.gbl>,
"Biff" <biffinpitt@comcast.net> wrote:

> My suggestion is intentional.
>
> I was, dare I say it, assuming that's what the OP wanted!