Autres MOYENNE en supprimant les #N/A et les cases affichées vides sous ""

[riri38]

Bonjour,

je souhaite réaliser la moyenne de cases placées en horizontale.

En fonction d'une liste déroulante où je change le club (case ASM à gauche), les cases s'alimentent soit avec des scores (cases jaunes) soit avec des #N/A (en gris), soit vierges (en blanc) soit avec des cases vertes ou rouges (=toutes sous mise en forme conditionnelle et sous formules)

Je souhaite effectuer la moyenne des cases jaunes uniquement de J1 à J26 (il s'agit de noms de journée et non de cases excel) sans prendre en compte les cases #N/A grises, ni les cases blanches (sous condition de ""), ni les cases vertes ou rouges... de cette ligne.

Je n'arrive pas à avoir une moyenne sans renvoi d'erreur car la plage est discontinue

Merci pour votre aide

Fichier joint

Cdlt

Richard

 

[mapomme]

Bonsoir @riri38😀,

Utilisez cette formule matricielle :
=MOYENNE(SI(ESTNUM(F3:BE4)*(EST.PAIR(COLONNE(F3:BE4)+0*LIGNE(F3:BE4)));F3:BE4;""))

 

[Hecatonchire]

Bonjour,

Quelle est ta version ?

Je propose

=LET(p;F3:BE3;
m;SIERREUR(p/EST.PAIR(COLONNE(p));"");
SOMME(m)/NB(m))

 

[riri38]

Bonsoir @riri38😀,

Utilisez cette formule matricielle :
=MOYENNE(SI(ESTNUM(F3:BE4)*(EST.PAIR(COLONNE(F3:BE4)+0*LIGNE(F3:BE4)));F3:BE4;""))

Regarde la pièce jointe 1225266

Merci bcp. C'est nickel, ça fonctionne parfaitement.

 

[riri38]

Bonjour,

Quelle est ta version ?

Je propose

=LET(p;F3:BE3;
m;SIERREUR(p/EST.PAIR(COLONNE(p));"");
SOMME(m)/NB(m))

Merci pour ta réponse.
Version 2007

 

[mapomme]

Re @riri38 ,

En fait, j'avais fait une moyenne sur les deux lignes.
La version jointe à ce message a aussi les formules pour une seule ligne (voir cellules BF3 et BF4).
La formule pour la moyenne des deux lignes est dans la cellule BF6.

Rappel : ces trois formules sont matricielles.

Attention ! Il faut se rappeler que la moyenne des moyennes partielles n'est la moyenne du tout comme on pourrait le subodorer🙄.

 

[riri38]

Re @riri38 ,

En fait, j'avais fait une moyenne sur les deux lignes.
La version jointe à ce message a aussi les formules pour une seule ligne (voir cellules BF3 et BF4).
La formule pour la moyenne des deux lignes est dans la cellule BF6.

Rappel : ces trois formules sont matricielles.

Attention ! Il faut se rappeler que la moyenne des moyennes partielles n'est la moyenne du tout comme on pourrait le subodorer🙄.

Oui j'ai remarqué que ça prenait les 2 lignes en essayant de comprendre la formule.
Je l'ai adaptée à chaque ligne sur mon fichier.
C'est nickel. Encore merci pour la rapidité d'exécution.
Cdlt

 

[Hecatonchire]

Pour info : il faudra faire attention, en cas d'ajout de colonnes, de rester sur un nombre pair.

Je n'ai pas testé mais le +0*LIGNE(F3:BE4) ne me semble pas utile.

 

[mapomme]

Bonsoir @Hecatonchire😉,

Pour info : il faudra faire attention, en cas d'ajout de colonnes, de rester sur un nombre pair.

Effectivement je ne l'ai pas précisé, l'oubli est réparé👍. Soit la première colonne des données est paire et c'est OK soit la première colonne est impaire et alors on remplace EST.PAIRE par EST.IMPAIRE

Je n'ai pas testé mais le +0*LIGNE(F3:BE4) ne me semble pas utile. Oui

Dans la version v2, ce terme avait été supprimé. C'était une scorie d'un essai avorté🫤.

En tout cas, tu as l'œil🔎👏.

Bonne soirée (et nuit) 🥱.

 

[Hecatonchire]

Avant d'aller me coucher, je propose une version compatible 2007 (à valider par CTRL+MAJ+ENTRER) de ma proposition (version améliorée)

=MOYENNE(SIERREUR(;F3:BE3/EST.PAIR(COLONNE(;F3:BE3));""))

 

[riri38]

Merci les gars, trop cool !

 

[job75]

Bonjour riri38, le forum,

Vous avez dit :

En fonction d'une liste déroulante où je change le club (case ASM à gauche), les cases s'alimentent soit avec des scores (cases jaunes) soit avec des #N/A (en gris), soit vierges (en blanc) soit avec des cases vertes ou rouges (=toutes sous mise en forme conditionnelle et sous formules)

Je souhaite effectuer la moyenne des cases jaunes uniquement

Vous auriez pu fournir un fichier avec une liste déroulante et des mises en forme conditionnelles (MFC) non ?

A priori il faut s'appuyer sur la couleur jaune des MFC et donc utiliser le VBA et la fonction DisplayFormat mais cette fonction n'est disponible qu'à partir d'Excel 2010. Pour s'en sortir sur Excel 2007 il faut donc connaître la condition qui colore les cellules en jaune.

Merci de joindre votre fichier avec les MFC pour qu'on puisse vous donner la solution qui va bien.

A+

 

[job75]

En attendant pour ceux qui sont sur une version postérieure à Excel 2007 voyez le fichier joint et cette macro dans le code de la feuille :

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [B3]) Is Nothing Then Exit Sub
Dim coldeb%, dest As Range, i&, n%, j%, c As Range, s#
coldeb = 6 'colonne F
Set dest = Cells.Find("Moy*", , xlValues, xlWhole)
If dest Is Nothing Then MsgBox "Il faut une colonne avec 'Moy'", 48: Exit Sub
For i = dest.Row + 1 To Cells.SpecialCells(xlCellTypeLastCell).Row
    n = 0: s = 0
    For j = coldeb To dest.Column - 1
        Set c = Cells(i, j)
        If c.DisplayFormat.Interior.Color = vbYellow Then If IsNumeric(CStr(c)) Then n = n + 1: s = s + CDbl(c)
    Next j
    If n Then Cells(i, j) = s / n 'moyenne
Next i
End Sub

Elle se déclenche quand on modifie ou valide la cellule B3.