XL 2019 Arrondi bancaire

[Kilgrave]

Bonjour,

Je souhaite utiliser l'arrondi bancaire sur ma feuille de calculs Excel (ou Google Sheets).
Pour ceux, qui ne connaissent pas, je copie-colle Wikipedia:

Si quatre (ou un chiffre inférieur) est le chiffre qui suit la décimale à laquelle le nombre doit être arrondi, alors la décimale reste inchangée. Alors que si le chiffre suivant la décimale est six ou plus, la décimale est augmentée d'une unité. Enfin si le chiffre suivant est le chiffre cinq lui-même suivi par des chiffres différents de zéro, alors la décimale sera augmentée d'une unité, tandis que si cinq n'est suivi d'aucun chiffre (ou que par des zéros) alors la décimale est augmentée d'une unité lorsqu'elle est impaire et reste inchangée sinon. Cette méthode est parfois appelée « arrondi au chiffre pair » et est employée afin d'éliminer le biais qui surviendrait en arrondissant à chaque fois par excès les nombres dont le dernier chiffre est cinq.

Exemples :

3,046 arrondis aux centièmes devient 3,05 (parce que le chiffre suivant (6) est supérieur ou égal à 6) ;
3,043 arrondis aux centièmes devient 3,04 (parce que le chiffre suivant (3) est inférieur ou égal à 4) ;
3,045 arrondis aux centièmes devient 3,04 (parce que le dernier chiffre est 5, et le chiffre précédent (4) est pair) ;
3,015 arrondis aux centièmes devient 3,02 (parce que le dernier chiffre est 5, et le chiffre précédent (1) est impair).
Cette méthode est aussi nommée « arrondi bancaire » et est celle utilisée par défaut dans les micro-processeurs et le calcul numérique (norme IEEE 754).

Visiblement, cet arrondi n'est pas directement implémentée dans Excel donc j'essaie de l'implémenter moi-même (sans VBA car je n'y connais rien et j'aimerais que cela fonctionne aussi sur Google Sheets). J'ai donc tapé la formule suivante:
=SI(MOD(B1*100;1)=0,5;ARRONDI.AU.MULTIPLE(B1;0,02);ARRONDI(B1;2))
B1 contenant la valeur que je veux arrondir au centime avec cet arrondi bancaire.
Cela fonctionne bien puisque pour 346,045 cela me donne 346,04 et pour 346,055 cela me donne 346,06.
Donc j'étais plutôt satisfait. Sauf qu'en fait, B1 est le résultat d'un calcul, en l'occurence j'ai A1=2768,36/36*0,75 et B1=A1*6. Dans B1, j'obtiens bien 346,045(qui est la valeur exacte, il n'y a pas d'autre décimale) mais là, la formule me l'arrondit à 346,05, comme si le test ne fonctionnait plus. Du coup, je fait afficher dans la cellule D1=MOD(B1*100;1) et cela me donne bien 0,5.
C'est à n'y rien comprendre et je suis dessus depuis hier.
Je joins un petit fichier très simplifié pour illustrer mon propos.
A1 contient le calcul 2768,36/36*0,75, B1 contient la formule A1*6, C1 contient l'arrondi bancaire de B1, B2 contient la valeur 346,045 tapée directement et C2 l'arrondi bancaire de B2.
C1 me donne 346,05 au lieu de 346,04. C2 me donne bien 346,04. D1 affiche MOD(B1*100;1) , qui donne bien 0,5.

Toute aide sera un grand soulagement, donc je vous remercie par avance.

 

[JHA]

Bonjour à tous,

A essayer,

=ARRONDI(SI(ET(STXT(A1;5;1)*1=5;EST.PAIR(STXT(A1;4;1)*1));A1-0,01;A1);2)

JHA

 

[Jocelyn]

Bonjour le Forum,
Bonjour Kilgrave,

suivant ton exemple on ne voit a priori pas de décimal en B1 derrière le 5 et pour autant

en forçant l'arrondi de B1 à 3 décimales cela fonctionne

A tester

2))

=SI(MOD(ARRONDI(B1*100;3);1)=0,5;ARRONDI.AU.MULTIPLE(B1;0,02);ARRONDI(B1;2))

Cordialement

EDIT : Bonjour l'ami JHA je suis comme d'hab en retard

 

[Kilgrave]

Merci pour ces 2 réponses. Je vais tester ça.
Vous sauriez me dire pourquoi ce que j'ai tapé ne fonctionne pas?

 

[sylvanu]

Bonjour Kilgrave, Jocelyn, JHA

Kilgrave,
Je pense qu'il s'agit d'un problème d'arrondi dans les calculs d' XL.
En effet, si dans B1 vous mettez : =A1*6-0,00000000001 alors vous retrouvez bien 346.04.

 

[Kilgrave]

Vos solutions fonctionnent.
Mille mercis!!

 

[JHA]

Bonjour à tous,

Gestion des entiers à plusieurs chiffres

=ARRONDI(SI(ET(STXT(A1;CHERCHE(",";A1)+3;1)*1=5;EST.PAIR(STXT(A1;CHERCHE(",";A1)+2;1)*1));A1-0,01;A1);2)

JHA

 

[Chris24]

Bonjour le fil

Et pourquoi pas une toute petite fonction

Function ArrondiB(Plage As Range)

    ArrondiB = Round(Plage, 2)

End Function

Cdt

 

[Kilgrave]

Merci à tous pour vos suggestions.
Pour Chris24, je ne sais pas utiliser les fonctions. Et j’aimerais que ça fonctionne également en ligne dans Google Sheets.
Ce problème d’arrondi dans Excel m’étonne. Car mon calcul revient en fait à faire 2768,36/8, ce qui donne exactement 346,045 et il n’y a vraiment aucune autre décimale. De plus, quand je fais mod(100*b1;1), cela m’affiche bien 0,5. Mais le test si(mod(100*b1)=0,5) me renvoie la valeur fausse. Je trouve ça déroutant.

Et surtout, je m’étonne qu’Excel n’ait pas cette fonction directement implémentée. Visiblement, c’est l’arrondi utilisé par les banques et il y a même une norme pour ça (IEEE 754). Donc quand on fait une feuille de calculs financiers, cet arrondi est indispensable. Là, je vais être obligé de mettre partout une formule avec un si, ce qui alourdit considérablement ma feuille de calculs (d’autant que j’ai des cellules qui comportent déjà un si). La fonction de Chris24 me serait sans doute bien utile mais je ne sais pas comment faire.

Merci encore

 

[sylvanu]

Re le fil,
Comme j'ai tenté de vous l'expliquer tout à l'heure, en vain semble t-il, c'est un problème de précision XL.
En effet le =MOD(B4*100;1) ne donne pas 0.5 mais : 0,499999999992724.
Testez votre valeur avec 15 décimales.
D'où je pense le problème de limites : MOD(B4*100;1) < 0.5

 

[Kilgrave]

Désolé Sylvanu, j’avais zappé votre message.
En remplaçant 2768,36/36*0,75 par 2768,36/48, cette fois cela donne bien 0,5 et ma formule initiale fonctionne

 

[sylvanu]

Dans XL ( comme dans tout système informatique ) certains calculs sont faussés à cause de la précision de calcul.
Dans votre exemple, il est de 7.2 10^(-12). Vous êtes juste à la limite.
Dans votre formule =SI(MOD(B1*100;1)=0,5;ARRONDI.AU.MULTIPLE(B1;0,02);ARRONDI(B1;2))
comme le MOD est < 0.5 il passe à l'expression si FAUX.

 

[Kilgrave]

Oui, mais je viens de vérifier avec une simple calculatrice.
Je fais 2768,36/36*0,75 que je stocke dans une mémoire A. Si ensuite je fais A*6, cela me donne exactement 346,045. Excel, quant à lui, me donne 346,049999999992724. Pas terrible, je trouve

 

[Chris24]

Bonsoir Kilgrave

Pour utilisé la fonction :

Dans votre fichier ouvrir l'éditeur VBA, touches alt F11

Dans l'éditeur VBA faire insertion Module

Dans le module copier la fonction

Function ArrondiB(Plage As Range)

    ArrondiB = Round(Plage, 2)

End Function

Voili Voilou la fonction est disponible comme dans le fichier du poste #8

en quittant enregistrer votre fichier au format excel XLSM

Cdt

 

[Kilgrave]

Merci Chris24. Je testerai ça demain.
Une fois la fonction créée, je tape =ArrondiB(cellule), c’est bien ça?