Ending In 5

[JR]

Thanks for all the help.

 

[JR]

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,

 

[CLR]

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

 

[Biff]

Hi!

Try this:

=ROUND(A1/0.05,0)*0.05

Biff

 

[Alan]

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.

 

[Robert_Steel]

=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

 

[JE McGimpsey]

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.

 

[Biff]

My suggestion is intentional.

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

Biff

 

[JE McGimpsey]

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 -.