Moyenne pondérée avec critère

[elliotr]

Bonjour à tous,
Je rédige ce post sachant que j'ai déjà éffectué des recherches à propos de cette formule et que les solutions proposées ne fonctionnent pas .

Je vous explique ce que je cherche:

J'ai besoin de calculer une moyenne pondérée sur deux colonnes (celles avec les flèches rouges):




C'est la moyenne des taux d'intérêts pondérée par la maturité initiale qu'il faut, [G]MAIS[/G] sous critère(s) (1 ou 2 selon le cas)

POUR LA SUITE, NOUS PRENDRONS LA COLONNE H POUR LES TAUX ET K pour les maturité initiales.
Je m'explique:

Ce sont les taux d'intérêts de bonds, et il y a des bonds de durée d'émission de 1, 2 , 3 ou + de 3 mois.
La moyenne pour les bonds d'1 mois ce fait donc sous la condition que K9:K25<32 (car mois=31j max)
Conditions pour les bonds de 2 mois: K9:K25>31 ET K9:K25<63
Conditions pour les bonds de 3 mois: K9:K25>62 ET K9:K25<94
Condition pour les bonds > 3 mois : K9:K25>93

Il faut donc calculer les moyennes pondérées pour chaque catégorie mais je ne trouve pas la formule adéquate..


Si du monde ici pourrait m'aider, je vous en serais infiniement reconnaissant car là je stagne complet.


Bonne journée à vous.

 

[pijaku]

Re : Moyenne pondérée avec critère

Moyenne pondérée sans critère :
=SOMMEPROD(A2:A18;B2:B18)/SOMME(B2:B18)

Moyenne pondérée avec 1 critère (j'ai choisi 122 comme limite car y'avait pas trop plus petit...) :
=SOMMEPROD((A2:A18)*(B2:B18<122);(B2:B18)*(B2:B18<122))/SOMME.SI(B2:B18;"<122")

Moyenne pondérée avec 2 critères :
=SOMMEPROD((A2:A18)*(B2:B18>122)*(B2:B18<275);(B2:B18)*(B2:B18>122)*(B2:B18<275))/SOMMEPROD(B2:B18*(B2:B18>122)*(B2:B18<275))

 

[elliotr]

Re : Moyenne pondérée avec critère

Moyenne pondérée sans critère :
=SOMMEPROD(A2:A18;B2:B18)/SOMME(B2:B18)

Moyenne pondérée avec 1 critère (j'ai choisi 122 comme limite car y'avait pas trop plus petit...) :
=SOMMEPROD((A2:A18)*(B2:B18<122);(B2:B18)*(B2:B18<122))/SOMME.SI(B2:B18;"<122")

Moyenne pondérée avec 2 critères :
=SOMMEPROD((A2:A18)*(B2:B18>122)*(B2:B18<275);(B2:B18)*(B2:B18>122)*(B2:B18<275))/SOMMEPROD(B2:B18*(B2:B18>122)*(B2:B18<275))

Pour la moyenne pondérée sans critère c'est OK. J'ai vérifié numérairement pour être sûr et ça colle étant donné que la moyenne classique = 0,8% et la pondérée = 1,01% soi un écart de 0,21%.

Par contre pour les moyennes pondérées avec critère(s) je crois que c'est toujours pas ça.

En effet, les bonds avec 1 seul critère sont les bonds d'1 mois et >3mois, car pour les 1 mois on a uniquement la condition <32, et pour les 3 mois l'unique condition étant >93.

Les bonds avec 2 critères pour le calcul de la moyenne pondérée sont les bonds de 2 mois et 3 mois:
-2 mois avec une maturité 31<maturié bonds 2 mois<63 (donc >31 et <63)
-3 mois avec une maturité 62<maturité bonds 3 mois<94 (donc >62 et <94)

Si on prend ta 1ère formule qui va correspondre en théorie aux bonds de 1 mois ou >3 mois de maturité
Le problème c'est ton 122 ?? Car si tu mets <122 en critère cela va comptabiliser tous les bonds dont la maturité <122 , soit les 1, 2, 3 et >3mois donc tous !*


J'ai essayé de changer le 122 en 32 pour l'appliquer aux bonds d'1 mois, et j'obtiens l'eereur DIV/O!

 

[Spinzi]

Re : Moyenne pondérée avec critère

Et as tu essayé de changer les intervalles ?

 

[elliotr]

Re : Moyenne pondérée avec critère

Et as tu essayé de changer les intervalles ?

J'ai changé pour la première le 122 en 32 pour l'appliquer aux bonds de maturité 1 mois, et j'obtiens l'erreur DIV/0!

 

[pijaku]

Re : Moyenne pondérée avec critère

tu obtiens l'erreur DIV/0 parce que tu n'as aucune cellule en colonne B dont le contenu réponds à ta condition! Tous tes nombres sont supérieurs à 32...

Edition : Et pourtant je te l'avais indiqué dans mon précédent message :
Moyenne pondérée avec 1 critère (j'ai choisi 122 comme limite car y'avait pas trop plus petit...)

 

[elliotr]

Re : Moyenne pondérée avec critère

J'ai résolu le problème.
Moyenne pondérée à 2 conditions:

=SOMMEPROD(A2:A18*B2:B18*(B2:B18>31)*(B2:B18<63))/SOMMEPROD(B2:B18*(B2:B18>31)*(B2:B18<63))

 

[pijaku]

Re : [Résolu] Moyenne pondérée avec critère

Formule qui n'est en fait qu'une simplification de ce que je te donnais ICI

Tant mieux, le principal étant que cela fonctionne...

Bonne suite

 

[elliotr]

Re : [Résolu] Moyenne pondérée avec critère

Formule qui n'est en fait qu'une simplification de ce que je te donnais ICI

Tant mieux, le principal étant que cela fonctionne...

Bonne suite

Oui.
Merci bien.

 

[elliotr]

Re : [Résolu] Moyenne pondérée avec critère

En fait ce n'est pas tout à fait résolu...

J'ai bien le bon résultat pour les bonds à 2 conditions (2 et 3 mois)
Par contre pour ceux à une condition, j'ai toujours l'erreur DIV/0!

J'ai appliqué ceci :

=SOMMEPROD(A2:A18*B2:B18*(B2:B18<32))/SOMMEPROD(B2:B18*(B2:B18<32)) pour les 1 mois

 

[Spinzi]

Re : Moyenne pondérée avec critère

Ce qui est logique si dans ta matrice tu n'as rien en dessous de 32...

 

[elliotr]

Re : Moyenne pondérée avec critère

Pour les >3 mois , j'obtiens un résultat, mais il n'est pas bon:

=SOMMEPROD(H9:H25*K9:K25*(K9:K25>93))/SOMMEPROD(K9:K25*(K9:K25>93))

 

[elliotr]

Re : Moyenne pondérée avec critère

Ce qui est logique si dans ta matrice tu n'as rien en dessous de 32...

C'est vrai... A force de me prendre la tête avec ça j'avais omis ce détail.

Et pour les +3 mois, t'as une idée du problème ?

 

[Spinzi]

Re : Moyenne pondérée avec critère

Pour les >3 mois , j'obtiens un résultat, mais il n'est pas bon:

=SOMMEPROD(H9:H25*K9:K25*(K9:K25>93))/SOMMEPROD(K9:K25*(K9:K25>93))

=SOMMEPROD(A2:A18*B2:B18*(B2:B18<32))/SOMMEPROD(B2:B18*(B2:B18<32)) pour les 1 mois

Pas le même intervalle de données ?

 

[elliotr]

Re : Moyenne pondérée avec critère

Pas le même intervalle de données ?

J'ai adapté les formules à mon fichier c'est normal:
=SOMMEPROD(H9:H25*K9:K25*(K9:K25>93))/SOMMEPROD(K9:K25*(K9:K25>93))

En fait je pensais que ce n'était pas bon car il y a une différence avec la moyenne que j'ai calculé manuellement pour vérifier les >3mois (MOYENNE=(...)).
En faisant cette vérification je tombe sur le même résultat entre la pondérée et la classique pour les 2 et 3 mois.
Par contre je n'ai pas le même résultat avec les >3 mois.

-Avec la classique je suis à 0.96%
-Avec la pondérée je suis à 1.16%

Est-ce normal étant donné que je sais que par définition a pondérée n'est pas la même chose que la classique bien qu'apparement la pondérée peut avoir dans certains cas la même valeur ??