XL 2019 Formule Max sans prendre en compte les N/A

pacman

XLDnaute Nouveau
Bonjour tout le monde,

J’aimerai incorporer un « si erreur » dans ma formule (ci-dessous) car lorsque qu’il y a une variable en « N/A » ma formule Max() renvoie un « N/A » or je voudrais que ma formule prenne la valeur la plus haute en dehors du « N/A ». Exemple :
A1 = 7
A2 = 2
A3 = N/A
A4 = 4
avec ma formule Max() je retombe sur le « N/A » or je souhaiterais avoir le « 7 ».

Formule : =MAX(SI(AA5=3;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));1;-1);SI(AA5=0;M5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));1;-1);SI(AA5=0;T5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));1;-1);SI(AA5=0;W5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));1;-1);SI(AA5=0;Q5;« N/A »)))))

Merci d’avance pour votre aide.
PS: désolé pour la formule kilométrique, elle tient en compte plusieurs conditions et étant naze en VBA je fais avec les formules
:)
 

pacman

XLDnaute Nouveau
Bonjour Pacman,
Essayez :
VB:
=MAX(SIERREUR(A1:A4;""))
En validant avec ALT+CTRL+Enter car formule matricielle.
PS : La solution était donnée dans :
Bonjour Sylvanu,
Merci beaucoup pour ce rapide retour.
En fait je sais que la formule qu'il faut c'est "MAX(SIERREUR" mais je n'arrive pas à l'incorporer dans ma longue formule 😅 pourrais-tu m'aider à ce niveau ?
 

sylvanu

XLDnaute Barbatruc
Supporter XLD
Re,
C'est vous qui intégrer les N/A dans votre formule ?
Pourquoi ne pas remplacer ce #N/A par "". De ce fait il ne sera pas utilisé pour le max, et on évite le Sierreur.
Voir PJ. Il n'y a pas d'erreur. Même si le fichier est bidon.
PS : Dans de longues formules on peut faire un retour à la ligne avec Alt+Enter, ça rend les formules plus lisibles.
VB:
=MAX(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;T5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))))
)
 

Pièces jointes

  • Pacman.xlsx
    8.4 KB · Affichages: 7

Chris401

XLDnaute Accro
Bonjour à tous
La formule peut être raccourcie

Code:
=MAX(
SI(AA5=0;M5;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));AA5;-AA5));
SI(AA5=0;T5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));AA5;-AA5));
SI(AA5=0;W5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));AA5;-AA5));
SI(AA5=0;Q5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));AA5;-AA5)))
 

pacman

XLDnaute Nouveau
Re,
C'est vous qui intégrer les N/A dans votre formule ?
Pourquoi ne pas remplacer ce #N/A par "". De ce fait il ne sera pas utilisé pour le max, et on évite le Sierreur.
Voir PJ. Il n'y a pas d'erreur. Même si le fichier est bidon.
PS : Dans de longues formules on peut faire un retour à la ligne avec Alt+Enter, ça rend les formules plus lisibles.
VB:
=MAX(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;T5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))))
)
Re,
En fait le "N/A" dont je parle correspond à la cellule T5 (fichier ci-joint). Comment dire à excel (dans ma longue formule) de prendre le Max() sans tenir compte s'il y a une cellule contenant N/A.
Merci pour l'astuce Alt+Enter, j'avoue que c'est plus lisible comme ça :)
 

Pièces jointes

  • Pacman2.xlsx
    11.6 KB · Affichages: 4

sylvanu

XLDnaute Barbatruc
Supporter XLD
Pas tout compris à votre formule, avec cette formule ça ne plante pas mais est ce que ça fait ce que vous attendez ???
Code:
=MAX(
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;0;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))));0)
)
Plus deux Sierreur en T5 U5.
 

Pièces jointes

  • Pacman4.xlsx
    11.4 KB · Affichages: 2

Chris401

XLDnaute Accro
Re
Essaye :
Code:
=MAX(
SI(AA5=0;SIERREUR(M5;0);SIERREUR(DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(T5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(W5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(Q5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));AA5;-AA5);0)))
 

pacman

XLDnaute Nouveau
Pas tout compris à votre formule, avec cette formule ça ne plante pas mais est ce que ça fait ce que vous attendez ???
Code:
=MAX(
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;0;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))));0)
)
Plus deux Sierreur en T5 U5.
Merci beaucoup, ça marche maintenant ! un grand ouf de soulagement :)
 

pacman

XLDnaute Nouveau
Re
Essaye :
Code:
=MAX(
SI(AA5=0;SIERREUR(M5;0);SIERREUR(DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(T5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(W5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(Q5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));AA5;-AA5);0)))
Merci beaucoup Chris pour votre aide (et l'astuce de la formule simplifiée) ;)
 

Statistiques des forums

Discussions
311 720
Messages
2 081 912
Membres
101 837
dernier inscrit
Ugo