Ending In 5

J

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,
 
C

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
 
A

Alan

JR said:
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,
Click to expand...

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

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
 
J

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

Biff

My suggestion is intentional.

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

Biff
 
J

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