Autres Ecart-Type sur les valeurs supèrieure à 0

[otarc]

Bonjour,

J'aimerai appliquer l'écart-type sur les valeurs supèrieure à 0 dans les cases C7;E7;G7;I7;K7;M7;O7;Q7 ( et non pas C7:Q7)

Est-ce possible sans fonction matricielle ?
Ci-joint le fichier correspondant. La formule est à rentrer dans la case C14. Si cela fonctionne, l'écart type obtenu doit être 1,51185789 car l'écart type va prendre 7 valeurs sur 8.

Version Excel2007

Je vous remercie de votre retour,

Bien cordialement

 

[job75]

Bonjour otarc, bienvenue sur XLD,

Si vous ne voulez pas de formule matricielle le plus simple est d'utiliser une ligne 8 auxiliaire.

Votre fichier en retour avec en C8 =SI(C7>0;C7;"") et en F14 =ECARTYPE(C8:Q8)

A+

 

[job75]

S'il peut y avoir des valeurs d'erreur utiliser en C8 =SI(ESTNUM(LN(C7));C7;"")

 

[sylvanu]

Bonjour Otarc, job,
En reprenant la définition de base de l'écartype adaptée aux cellules >0 :

=RACINE(SOMME.CARRES.ECARTS(C7:O7)/(NB.SI(C7:O7;">0")-1))

Ce qui donne bien 1.51185789 sans formules matricielles.

 

[otarc]

Bonjour,

Merci à vous deux. La ligne auxiliaire me dérange dans mon fichier, je vais m'orienter vers la solution de la racine. Cependant, je suis obligé de selectionner mes cases une par une, je ne peux pas utiliser C7:Q7

J'ai utilisé cette formule sur la case C13 : =RACINE(SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7;Q7)/(NB.SI(C7;">0")+NB.SI(E7;">0")+NB.SI(G7;">0")+NB.SI(I7;">0")+NB.SI(K7;">0")+NB.SI(M7;">0")+NB.SI(O7;">0")-1+NB.SI(Q7;">0")-1))

Mais je n'arrive pas à obtenir le résultat 1.51185789
Ci-joint le fichier avec la formule ci-dessus

Merci pour votre retour,

Bien cordialement

 

[sylvanu]

Re,
Pourquoi ne pouvez vous pas sélectionner la plage C7:Q7 ?
C'était une info importante.

 

[otarc]

Dans mon fichier d'origine, je peux avoir des données entre les cases qui m'interessent (ceux dans le fichier fourni sur le forum). Je ne dois pas prendre en compte ces données.

Edit : La formule est bonne à moitié.

=RACINE(SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7;Q7)/(NB.SI(C7;">0")+NB.SI(E7;">0")+NB.SI(G7;">0")+NB.SI(I7;">0")+NB.SI(K7;">0")+NB.SI(M7;">0")+NB.SI(O7;">0")-1+NB.SI(Q7;">0")-1))

La partie du bas est correct et tient bien en compte valeur sup à 0. Par contre la partie du haut SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7;Q7) prendre en compte le Q7 alors que Q7=0 et cela fausse le résultat

 

[sylvanu]

Alors essayez ça :

=RACINE(SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7)/(SOMMEPROD((C7>0)+(E7>0)+(G7>0)+(I7>0)+(K7>0)+(M7>0)+(O7>0))-1))

Mais ça ne marche que si le nombre de valeurs n'est pas trop élevé, car sinon la formule va être à rallonge.

 

[otarc]

La formule que j'avais écrit un peu plus haut était bonne à moitié.

=RACINE(SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7;Q7)/(NB.SI(C7;">0")+NB.SI(E7;">0")+NB.SI(G7;">0")+NB.SI(I7;">0")+NB.SI(K7;">0")+NB.SI(M7;">0")+NB.SI(O7;">0")-1+NB.SI(Q7;">0")-1))

La partie du bas est correct et tient bien en compte valeur sup à 0. Par contre la partie du haut SOMME.CARRES.ECARTS(C7;E7;G7;I7;K7;M7;O7;Q7) prend en compte le Q7 alors que Q7=0 et cela fausse le résultat. Si j'enlève Q7, j'ai le bon résultat.

Est-il possible d'appliquer une condition sur la fonction somme.carres.ecarts ? Actuellement, elle prend toute les valeurs en compte, 0 inclus, je voudrai que ça ne soit que les valeurs superieur à 0.

 

[job75]

Une ligne auxiliaire vous perturbe, une fonction VBA vous perturbera aussi :

Function EcarTyp(r As Range)
Dim a, ub%, i&, j%, x As Variant
a = r 'matrice, plus rapide
If Not IsArray(a) Then a = r.Resize(2) 'au moins 2 éléments
ub = UBound(a, 2)
For i = 1 To UBound(a)
    For j = 1 To ub
        x = a(i, j)
        a(i, j) = ""
        If IsNumeric(CStr(x)) Then If x > 0 Then a(i, j) = x
Next j, i
EcarTyp = Application.StDev(a)
End Function

Le code doit être placé impérativement dans un module standard, fichier joint.

 

[job75]

Si comme sur l'exemple il faut étudier une cellule sur 2 :

Function EcarTyp(r As Range, ColStep%)
Dim a, ub%, i&, j%, x As Variant
a = r 'matrice, plus rapide
If Not IsArray(a) Then a = r.Resize(2) 'au moins 2 éléments
ub = UBound(a, 2)
For i = 1 To UBound(a)
    For j = 1 To ub
        x = a(i, j)
        a(i, j) = ""
        If (j - 1) Mod ColStep = 0 Then If IsNumeric(CStr(x)) Then If x > 0 Then a(i, j) = x
Next j, i
EcarTyp = Application.StDev(a)
End Function

L'argument ColStep donne le pas des colonnes à étudier, fichier (2).

 

[otarc]

Merci pour la solution VBA,
J'abandonne la recherche d'alternative excel.
Avez-vous une solution à proposer en matricielle ?

 

[job75]

Avez-vous une solution à proposer en matricielle ?

Oui bien sûr :

=ECARTYPE(SI(ESTNUM(LN(C7:Q7))*(MOD(COLONNE(C7:Q7)-COLONNE(C7);2)=0);C7:Q7))

A valider par Ctrl+Maj+Entrée.

 

[otarc]

Parfait, merci beaucoup pour votre aide !

Bonne soirée