• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

help needed in getting values to end in 0,5 or 9

S

sliderxb

New Member
have a table of prices which are formatted to 2 decimal places and want the end digit after the decimal to end in either 0, 5 or 9.


eg.

1.87 = to change to 1.89

1.32 = to change to 1.35

1.00 = stay the same

1.16 = to change to 1.19


anything ending 0,5 or 9 stays as is, anything ending in 1 - 4 changes to end in 5. anything ending in 6 - 8 changes to end in 9.


have over 10000 items so not possible manually.


Can anyone help?
 
Try


=IF(OR(RIGHT(MOD($A:$A,1),1)*1=0,RIGHT(MOD($A:$A,1),1)*1=5,RIGHT(MOD(A:A,1),1)*1=9),A1,IF(OR(RIGHT(MOD($A:$A,1),1)*1=6,RIGHT(MOD($A:$A,1),1)*1=7,RIGHT(MOD(A:A,1),1)*1=8),(LEFT($A:$A,3)&9)*1,(LEFT($A:$A,3)&5)*1))
 
I got one more formula.. :)


=IF(OR((ROUND(G8,1)-G8)=0,(ROUND(G8,1)-G8)=0.05,(ROUND(G8,1)-G8)=0.01),G8,IF((ROUND(G8,1)-G8)<0,G8+(ROUND(G8,1)-G8)+0.05,G8+((ROUND(G8,1)-G8)-0.01)))


The question was pretty interesting to solve..


Pls Note: I have the rates in column G and in particular G8 Cell.


Regards,

Prasad
 
sliderxb, sorry, even in my formula, if rate is ending with n.35 or n.85 it is rounding it to n.39 or n.89, rest all the cases it works fine. :(


I will try to get your workaround for the same as well..
 
Here is the corrected formula :)


=IF(OR((ROUND(G8,1)-G8)=0,(ROUND(G8,1)-G8)>0.04999,(ROUND(G8,1)-G8)=0.01),G8,IF((ROUND(G8,1)-G8)<0,G8+(ROUND(G8,1)-G8)+0.05,G8+((ROUND(G8,1)-G8)-0.01)))


I have changed =0.05 part of formula to >0.04999 that did the trick for me.


Regards,

Prasad DN


Ps: The error was due to fraction 0.0499999972
 
Thanks prasaddn for pointing our my error. I didn't think of that.


modified

IF(OR(VALUE(RIGHT($A:$A*100,1))=0,VALUE(RIGHT($A:$A*100,1))=5,VALUE(RIGHT($A:$A*100,1))=9),A1,IF(OR(VALUE(RIGHT($A:$A*100,1))=6,VALUE(RIGHT($A:$A*100,1))=7,VALUE(RIGHT($A:$A*100,1))=8),(LEFT($A:$A,3)&9)*1,(LEFT($A:$A,3)&5)*1))
 
I've wanted to find a way to use the CHOOSE function for a while -- thank you for providing me an example!


=VALUE(REPLACE(A1,LEN(A1),1,CHOOSE(RIGHT(A1),5,5,5,5,5,9,9,9,9,0)))


Is this what you need?
 
Nice idea, Daffy!

However, one small catch. Trailing 0's won't be picked up by the RIGHT function (assuming it's not entered as text). A quick IF check should help:

=IF(ROUND(A1,1)=A1,A1,VALUE(REPLACE(A1,LEN(A1),1,CHOOSE(RIGHT(A1),5,5,5,5,5,9,9,9,9,0))))
 
Daffy333, I really liked your formulae and very nice approach, just one catch.. whenever the value is like n.n0 it changes to n.5
 
Humph. Well, this version gets rid of the IF in favour of a double unary solution:


=VALUE(REPLACE(A1,LEN(A1),1,CHOOSE((--(RIGHT(TEXT(A1,"0.00"),1)="0")*10+RIGHT(TEXT(A1,"0.00"),1)),5,5,5,5,5,9,9,9,9,0)))


My mistake for being too enthusiastic about CHOOSE! :)
 
My test pool was quite small. How is this one?


=VALUE(REPLACE(TEXT(A12,"0.00"),LEN(TEXT(A12,"0.00")),1,CHOOSE(RIGHT(A12*100)+1,0,5,5,5,5,5,9,9,9,9)))
 
well.... 1.00 turns into 2.00 and the rest of the data are zeros.


i created a list of data to test.


1.00

1.01

1.12

1.23

1.34

1.45

1.56

1.67

1.78

1.89

1.90

2.00
 
don't think so. your formula


VALUE(REPLACE(A1,LEN(A1),1,CHOOSE((--(RIGHT(TEXT(A1,"0.00"),1)="0")*10+RIGHT(TEXT(A1,"0.00"),1)),5,5,5,5,5,9,9,9,9,0)))


change 1 to zero because excel only see 1 but can't change 1 to text with 1.00. it remains 1


sorry my bad. your last formula does work. I mis read A12 as A1 so overwriting your formula shown didn't work in the first place. Thanks for the tips!!
 
=VALUE(REPLACE(TEXT(A1,"0.00"),LEN(TEXT(A1,"0.00")),1,CHOOSE(RIGHT(A1*100)+1,0,5,5,5,5,5,9,9,9,9)))


You're right, I was copy pasting from B12 instead of B1. Phew! Nothing like having an active pool of formula testers on hand! I'm glad I was able to (sort of) help!
 
Interesting,


My try (less text processing, more maths):


Code: 
=RC1-(MOD(RC1*100,10)-CHOOSE(MOD(RC1*100,10)+1,0,5,5,5,5,5,9,9,9,9))/100


Jeanbar
 
You must log in or register to reply here.
Back
Top