Ending In 5

  • Thread starter Thread starter JR
  • Start date Start date
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 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.
 
=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.
 
My suggestion is intentional.

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

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