View original (French)

Round to 0, 5, and 9

Solved
Valo -  
 Valo -
Bonjour,

I would like to know how to make Excel (2007) give a value that always ends in 0, 5, or 9 at the second decimal.

Example:
3.46 --> 3.45
7.81 --> 7.80
15.17 --> 15.19

Here is what I always need to get for the second decimal based on its value:

0 --> 0
1 --> 0
2 --> 0
3 --> 5
4 --> 5
5 --> 5
6 --> 5
7 --> 9
8 --> 9
9 --> 9

I need to incorporate this into simple calculations (+ - / *)
Example: 3.21*4=12.84 --> 12.85

Thank you for your help.

8 answers

  1.
    Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello
    try this formula (for a value in A1)
    =INT(A1)+IF(A1-INT(A1)<=0;0;IF(A1-INT(A1)<=0.6;0.5;0.9))

    and if you have a formula in A1, replace A1 here with that formula to get a direct result
    best regards
    --
    The quality of the answer mainly depends on the clarity of the question, thank you!
    1
  2. ccm81 Posted messages 11033 Status Member 2 434
     
    Hello to both of you

    1. A small correction to the Vaucluse formula, but I don't think it fixes everything
    =INT(A1)+IF(A1-INT(A1)<=0.2,0,IF(A1-INT(A1)<=0.6,0.5,0.9))
    2. What I had tried
    Your value to be processed in G4
    In a cell formatted with 2 decimal places
    =INT(ROUND(G4,2)*10)/10+CHOOSE(1+MOD(100*ROUND(G4,2),10),0,0,0,5,5,5,5,9,9,9)/100

    Best regards
    1
  3. Valo
     
    It works perfectly well!!!

    Thank you very much.

    Thank you all for your contribution!!!

    Best regards,
    1
  4. ccm81 Posted messages 11033 Status Member 2 434
     
    Try this one
    =INT(ROUND(G5;2)*10)/10+INDEX({0;0;0;5;5;5;5;9;9;9};1+MOD(100*ROUND(G5;2);10))/100

    Regards
    1
    1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      According to your idea, on the same principle:
      =INT(ROUND(A2;2)*10)/10+INDEX({0;5;9};MOD(INT(A2*30+0,11);3)+1)/100
      0
  6. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello

    just for the record

    indeed, I made a mistake with the floor, I worked on the 1st decimal instead of the second.

    for the second decimal, it's here, but it doesn't compare to Patrice's solution (hi my friend), so I'm just saying that I didn't mess everything up! :-)))

    =INT(A1*10)/10+(IF((A1*10-INT(A1*100/10))*10<3,0,IF((A1*10-INT(A1*100/10))*10<7,5,9)))/100
    that's what it gives
    https://mon-partage.fr/f/JUfg29bY/
    cheers

    The quality of the response mainly depends on the clarity of the question, thank you!
    1
  7. Valo
     
    Hello,

    Thank you for this response.
    However, it only works on the first decimal and not on the second, and we have an error when it goes to 0:

    2.00 --> 2.00
    2.10 --> 2.50 (instead of 2.00)
    2.20 --> 2.50 (instead of 2.00)
    .
    .
    .
    .
    0
  8. Valo
     
    Great, it works!!! (the second formula)
    Thank you very much!

    A simple effect probably due to rounding:
    1.03 --> 1.05 (ok)
    2.03 --> 2.00 (instead of 2.05)
    But it doesn't seem serious for what I have to do.

    Thanks again!!!
    0
    1. Patrice33740 Posted messages 8400 Registration date   Status Member Last intervention   1 784
       
      Hello,

      It is indeed a rounding issue!
      Try:
      =INT(ROUND(A2,2)*10)/10+CHOOSE(1+MOD(INT(100*ROUND(A2,2)),10),0,0,0,5,5,5,5,9,9,9)/100
      0