XL 2019 Arrondi bancaire

Kilgrave · 28 Février 2020

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.

Dranreb · 29 Février 2020

Si ça devait ne pas toujours donner le résultat escompté compte tenu de ce que les valeurs à 3 décimales autres que 125, 375, 625 et 875 ne peuvent pas exister avec exactitude, vous pourriez aussi essayer :

VB:

Function ArrondiB(ByVal V)
   If TypeOf V Is Range Then V = V.Value
   ArrondiB = Round(CCur(V), 2)
   End Function

Chris24 · 29 Février 2020

Re: oui

Dranreb · 29 Février 2020

Bonsoir.
À essayer aussi :

VB:

Function ArrBcr(ByVal V As Double) As Currency
   ArrBcr = Round(Int(V * 200 + 0.5) / 2) / 100
   End Function

	=ArrVv2(A2)	=ArrBcr(A2)
3,005	3,00	3,00	Function ArrVv2(ByVal V As Double) As Currency
3,015	3,02	3,02	ArrVv2 = Round(V, 2)
3,025	3,03	3,02	End Function
3,035	3,04	3,04
3,045	3,05	3,04	Function ArrBcr(ByVal V As Double) As Currency
3,055	3,06	3,06	ArrBcr = Round(Int(V * 200 + 0.5) / 2) / 100
3,065	3,07	3,06	End Function
3,075	3,08	3,08
3,085	3,09	3,08
3,095	3,10	3,10
3,105	3,11	3,10

Mais la norme IEEE 754 ne me semble avoir aucun rapport avec ça.

Édition: toutefois les erreurs de la fonction ArrVv2 étaient dues à la manière dont les valeurs en A étaient obtenues. Précédée d'une instruction de correction de sécurité, le Round(V, 2) de @Chris24 marche aussi :

Code:

Function ArrVv2(ByVal V As Double) As Currency
   V = Int(V * 200 + 0.5) / 200
   ArrVv2 = Round(V, 2)
   End Function

Dranreb · 29 Février 2020

Bon ben ce n'était pas bon. À force de ne tester que des nombre à 3 décimales, la dernière étant 5, j'en oubliai le reste.

VB:

Function ArrBcr(ByVal V As Double) As Currency
   ArrBcr = Round(Int(V * 1000 + 0.5) / 10) / 100
   End Function

À tester, mais sérieusement cette fois …

Kilgrave · 29 Février 2020

Bonjour,

J'ai testé la fonction de Chris24. Cela fonctionne si je mets arrondi(B7) par exemple. Mais si je mets arrondiB((B6+B7)/2), là j'obtiens une erreur Valeur!
Je testerai les autres suggestions ultérieurement.

Dranreb · 29 Février 2020

Bonjour.
Que souhaiteriez vous d'ailleurs si jamais l'argument n'était pas numérique ?
Et s'il était vide ? (Si la fonction renvoie une valeur vide, Excel assume 0)
Je ne garantirais le résultat de simplement Round(V, 2) que pour des valeur entières ou se terminant en décimal par ,125, ,375, ,625 ou ,875. D'autres nombres à 3 décimales dont la dernière est 5 ne peuvent exister en tant que valeurs de cellules qu'avec une infime différence, soit supérieurs soit inférieurs, ce qui devrait changer le résultat.

Kilgrave · 29 Février 2020

Dans ma feuille de calculs, ces situations ne sont pas possibles

Dranreb · 29 Février 2020

Alors pour que la fonction de @Chris24 accepte aussi les expressions numériques il suffit de la corriger comme suit :

VB:

Function ArrondiB(ByVal V)
   If TypeOf V Is Range Then V = V.Value ' Pour la bonne forme. Probablement pas indispensable.
   ArrondiB = Round(V, 2)
   End Function

Mais mes réserves tiennent toujours.

Kilgrave · 29 Février 2020

Merci mais je ne sais même pas comment modifier la fonction ArrondiB.
Je fait alt+F11, et ensuite?

Dranreb · 29 Février 2020

Dans l'explorateur de projets, faites un double clic sur le nom du module.

Kilgrave · 29 Février 2020

Merci beaucoup

Dranreb · 29 Février 2020

Si ça devait ne pas toujours donner le résultat escompté compte tenu de ce que les valeurs à 3 décimales autres que 125, 375, 625 et 875 ne peuvent pas exister avec exactitude, vous pourriez aussi essayer :

VB:

Function ArrondiB(ByVal V)
   If TypeOf V Is Range Then V = V.Value
   ArrondiB = Round(CCur(V), 2)
   End Function

Kilgrave · 29 Février 2020

Ok, c'est noté

Dranreb · 29 Février 2020

Je précise que ma réserve s'applique même si vous n'avez que cette moyenne en arrondi bancaire à faire partout, parce que les nombres à 2 décimales autres que 25, 50 et 75 ne sont pas non plus enregistrés avec exactitude.
Je propose :

VB:

Function MoyArrB(ByVal A, ByVal B)
   If TypeOf A Is Range Then A = A.Value
   If TypeOf B Is Range Then B = B.Value
   MoyArrB = Round((CCur(A) + CCur(B)) / 2, 2)
   End Function

La fonction CCur arrondi la valeur à 4 décimales toujours exactes, car représentée par un entier binaire signé 64 bits, valant 10000 × sa valeur conventionnelle.
Excel n'utilise hélas pas en interne ce type de donnée Currency pour la représentation des valeurs de cellules.
.

Dranreb · 29 Février 2020

Il semble d'ailleurs que la conversion en Currency fasse un arrondi bancaire sur la 4ème décimale. Pour 2 valeurs exactement représentables en Double ça semble être le cas :
312,46875 est arrondi à 312,4688@, supérieur et
312,53125 est arrondi à 312,5312@, inférieur.
=ARRONDI(312,46875;4) tente de valoir 312,4688 (2748504631337707 / 2^43 en fait) mais
=ARRONDI(312,53125;4) tente de valoir 312,5313 (2749054387151595 / 2^43 en fait).

Kilgrave · 29 Février 2020

Merci Dranreb pour toute votre aide.
Effectivement, la fonction arrondiB de Chris24 ne donne pas toujours le bon résultat. De toute façon, je ne comprenais pas comment elle fonctionnait (ni vos modifications ultérieures d’ailleurs). Au moins, je comprenais ce que j’avais suggéré dans mon premier post:
SI(MOD(B1*100;1)=0,5;ARRONDI.AU.MULTIPLE(B1;0,02);ARRONDI(B1;2))
N’y a-t-il pas moyen de créer une fonction ArrondiB à partir de de ce test (et qui fonctionnerait aussi avec un calcul?)

XL 2019 Arrondi bancaire

Kilgrave

XLDnaute Nouveau

Pièces jointes

Dranreb

Chris24

XLDnaute Impliqué

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Dranreb

XLDnaute Barbatruc

Dranreb

XLDnaute Barbatruc

Kilgrave

XLDnaute Nouveau

Discussions similaires