Logo GNT
Actualités Tests & Guides Bons Plans
Twitter iPhone pliant OnePlus 11 PS5 Disney+ Orange Livebox Windows 11
Entraide Applications Microsoft Office Excel How to make average function ignore MIN and MAX

How to make average function ignore MIN and MAX

3 réponses
Avatar
Larry4500
Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to
ignore the two highest and lowest values? The second question is less
important but I would really appreciate it if somebody has the answer
to my first question- it would be really useful for what I'm trying to
do. Thank you!
Signaler un problème avec ce contenu

3 réponses

Supprimer
Cette action est irreversible, confirmez la suppression du commentaire ?
Signaler le commentaire

Veuillez sélectionner un problème

Nudité
Violence
Harcèlement
Fraude
Vente illégale
Discours haineux
Terrorisme
Autre
Avatar
Pierre Fauconnier
Hello...

sorry, but this newsgroup is mpFe where F means French...

So i try to answer you in english, but with the functions names in french...

For your first question, you can use
=(SOMME(A1:A10)-MAX(A1:A10)-MIN(A1:A10))/(NBVAL(A1:A10)-2)

SOMME = SUM, and i think max and min are in english as in french

For your second question, you can use
=(SOMME(A1:A10)-GRANDE.VALEUR(A1:A10;1)-GRANDE.VALEUR(A1:A10;2)-PETITE.VALEUR(A1:A10;1)-PETITE.VALEUR(A1:A10;2))/(NBVAL(A1:A10)-4)
where GRANDE.VALEUR could be LARGE and PETITE.VALEUR could be SMALL

It exists perhaps a simplier solution.

Ok?


--
Pierre Fauconnier () (Skype:
pierre_fauconnier)
"Le bonheur n'est pas au bout du chemin. Le bonheur EST le chemin ( proverbe
zen )
Remplacez nospam.nospam par pfi.be pour répondre. Merci


"Larry4500" a écrit dans le message de news:

Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to
ignore the two highest and lowest values? The second question is less
important but I would really appreciate it if somebody has the answer
to my first question- it would be really useful for what I'm trying to
do. Thank you!



Avatar
Pierre Fauconnier
Hello

I think the solutions are in english

For your first question
=(SUM(A1:A10)-MAX(A1:A10)-MIN(A1:A10))/(COUNTA(A1:A10)-2)
For your second question
=(SUM(A1:A10)-LARGE(A1:A10,1)-LARGE(A1:A10,2)-SMALL(A1:A10,1)-SMALL(A1:A10,2))/(COUNTA(A1:A10)-4)

Ok?


--
Pierre Fauconnier () (Skype:
pierre_fauconnier)
"Le bonheur n'est pas au bout du chemin. Le bonheur EST le chemin ( proverbe
zen )
Remplacez nospam.nospam par pfi.be pour répondre. Merci

"Larry4500" a écrit dans le message de news:

Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to
ignore the two highest and lowest values? The second question is less
important but I would really appreciate it if somebody has the answer
to my first question- it would be really useful for what I'm trying to
do. Thank you!



Avatar
Jacquouille
Allo Jeanne ?

--
Bien amicalmement,
Vivement conseillés:
http://www.excelabo.net
http://jacxl.free.fr/mpfe/trombino.html
http://dj.joss.free.fr/netiquet.htm
http://frederic.sigonneau.free.fr/

Jacquouille.

"Larry4500" a écrit dans le message de news:

Is there a way to get Excel to ignore the max and min of a data range
when calculating the average? If so, is there any way to get it to
ignore the two highest and lowest values? The second question is less
important but I would really appreciate it if somebody has the answer
to my first question- it would be really useful for what I'm trying to
do. Thank you!