Excel Forumla help

[Bob1]

Hello all

really hope someone can help me as I'm useless when it comes to formulas that are not basic.

I am looking to round my prices to either a 5 or a 9 depending what's closer.

i have a long list of prices in say column A. In Column B I want to round the prices from Column A to end in either a 5 or 9 depending on whats closer.

so for example if number in column A is 623,624,625,626,627 it will convert/round number to 625 in column B

If in Column A number is 628,629,630,631,632, it will convert/round number to 629

Thanks in advance

 

[Chris.Solve]

I am not able to provide you with an answer to your question, but whenever I have Excel issues I use excelforum.com and get help very quickly from there. You can also try answers.microsoft.com but I find the responses are a little slower. Don't post on both forums without referencing the other post as they get a bit funny with posting in multiple places

 

[AndrewEardley]

I can almost get it to work, but not quite!

 

[AndrewEardley]

=SUBSTITUTE(A2;RIGHT(A2);IF(RIGHT(A2)="3";"5";IF(RIGHT(A2)="4";"5";IF(RIGHT(A2)="6";"5";IF(RIGHT(A2)="7";"5";IF(RIGHT(A2)="5";"5";"9"))))))

This is good as I can get it; however its not quite right as it will change 630,631,632 to 639 instead of 629. I cant figure out how to correct it; but excelforum might be able to use this a starting point; or may just scrap it and give you something "prettier" that works

 

[Scalloway]

=if(isodd(mround(+a1,5)),mround(+a1,5),mround(+a1,5)-1)

 

[AndrewEardley]

I knew there was a prettier way of doing it

I had seen the mround function but having never used it didn't know how to get it to work