recherche une formule avec SI en ce basant sur 5 colonnes

[williamss]

Bonjour,

Voici un fichier excel :

Dans la colonne B nous avons les valeurs réelles pour chaque année. Puis pour les colonnes C, D, E et F on a des suppositions.

Dans la colonne J, pour chaque année, suivant les 4 colonnes C, D, E et F comment est il possible d'avoir comme résultat suivant une équation :

- "1" si le nombre maximum de cellules sur les 4 colonnes sont inférieur à -0.75 soit supérieur a 0.75 soit entre -0.75 et 0.75 (sauf si on a deux fois le nombre maximal de cellules dans deux rangs différents comme c'est le cas pour la ligne 19 ou 32... dans l'exemple) TOUT COMME LA valeur de la cellule B ;

- "impossible" si entre la colonne C et F on trouve deux fois le nombre maximum de cellule sup a +0.75 ou inf a -0.75 ou entre -0.75 et 0.75 de cellules. Par exemple pour la ligne 19 le nombre maximale de cellules avec une valeur incorporée du même coté est deux et on en trouve pour 2 rangs différents : 0.86 et 1.81 (sup à 0.75) puis -0.86 et -1.16 (inf a -0.75).

- "0" si les 2 cas de se qui est dit ci-dessus n'est pas cas ;

donc tout comme on le voit dans la colonne H mais ici fait après avoir regardé ??

merci

Williams

 

[Tibo]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Bonjour,

J'ai regardé ton fichier. J'ai du mal à comprendre, malgré tes explications (peut-être parce qu'on est dimanche et que le soleil tape un peu...).

L'idéal serait que tu commentes les résultats attendus en nous donnant dans le détail du pourquoi du comment le résultat.

@+

 

[KenDev]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Bonjour Williams, Tibo,

Je n'ai à peu près rien compris aux explications non plus Par contre j'espère avoir compris le tableau.

J'ai traduis le résultat à atteindre par :
Catégoriser les colonnes C à F par catégorie de valeur (<-0,75 ou > 0, 75 ou entre les deux bornes).
Si deux catégories arrivent première ex-aequo en nombre de représentant-> "impossible"
Si une catégorie est seule 'en tête' alors si la valeur de la colonne B appartient à cette catégorie -> '1' sinon '0'.

La formule suivante donne les même résultats que la colonne H :

=SI(MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))+MIN(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))=2;"impossible";SI(SI(B5>0,75;0,75;SI(B5<-0,75;-0,75;0))=0;SI(4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0);SI(SI(B5>0,75;0,75;SI(B5<-0,75;-0,75;0))=0,75;SI(NB.SI(C5:F5;">0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0);SI(NB.SI(C5:F5;"<-0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0))))

Il y a sans doute moins long..

Si le nombre de caractères dépassent la limite de la version d'Excel utilisée il faudra décomposer, ex :
Colonne G

=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))

Colonne H

=MIN(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))

Colonne I

=SI(B5>0,75;0,75;SI(B5<-0,75;-0,75;0))

Le résultat en colonne J :

=SI(G5+H5=2;"impossible";SI(I5=0;SI(4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75")=G5;1;0);SI(I5=0,75;SI(NB.SI(C5:F5;">0,75")=G5;1;0);SI(NB.SI(C5:F5;"<-0,75")=G5;1;0))))

Voir la feuille 1 pour la formule unique, la feuille 2 pour le résultat décomposé.

Cordialement

KD

 

[williamss]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Ok,

Par exemple :

- suivant l'équation que je cherche à réaliser, dans la cellule J5 je voudrais que ça me mets IMPOSSIBLE car sur les 4 colonnes il y en a 2 (donc la moitié du nombre de colonne dont on se base C, D, E, F) qui ont un résultat supérieur a 0.75 et les 2 autres qui ont un résultat inférieur à -0.75. Donc impossible a voir ce qu'il faut prendre puisque le maximum de colone se trouve dans 2 camps (ici 2 sup a 0.75 et 2 inf a -0.75) ;

- suivant l'équation que je cherche à réaliser, dans la cellule J6 je voudrais que ça me mets 1 car sur les 4 colonnes il y a plus de la moitié (ici les ¾) qui donne une valeur comprise entre -0.75 et 0.75 tout comme la valeur de la cellule B6 (donc même résultat) ;

- suivant l'équation que je cherche à réaliser, dans la cellule J7 je voudrais que ça me mets 0 car le maximum de valeur sur les 4 colonnes (C, D, E, F) sont supérieur à 0.75 alors que la cellule b7 est inférieur a -0.75 (donc résultat différent) ;

- suivant l'équation que je cherche à réaliser, dans la cellule J31 je voudrais que ça me mets 1 car le maximum de valeur sur les 4 colonnes (C, D, E, F) sont inférieur à -0.75 et même si il y en a 2 sur 4 donc la moitié du nombre de colonne, les 2 autres cellules ne sont pas sup. 0.75 ou entre 0.75 et -0.75 mais chacune sont dans les deux catégories différentes.

J'espère qu'avec ces exemples que vous comprenez se dont je cherche a faire.

Merci

Williams

 

[KenDev]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Re,

Merci pour ces nouvelles explications qui sont, il me semble, exactement ce que j'avais compris (et que je pense avoir traduis plus succinctement). Et est ce que regarder la solution donnée au post précédent vous tente ??
Cordialement

KD

 

[williamss]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

En même temps que tu m'as répondu j'ai répondu à Tibo. Puis après j'ai vu ta réponce et c'est exactement ce dont je cherchais à faire.

merci

Williams

 

[williamss]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Dans l'équation =SI(MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))+MIN(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"))=2;"impossible";SI(SI(B5>0,75;0,75;SI(B5<-0,75;-0,75;0))=0;SI(4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0);SI(SI(B5>0,75;0,75;SI(B5<-0,75;-0,75;0))=0,75;SI(NB.SI(C5:F5;">0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0);SI(NB.SI(C5:F5;"<-0,75")=MAX(NB.SI(C5:F5;">0,75");NB.SI(C5:F5;"<-0,75");4-NB.SI(C5:F5;">0,75")-NB.SI(C5:F5;"<-0,75"));1;0)))) est il possible de mettre a la place de la valeur 0.75 une cellule comme référence (EXEMPLE B2) cela éviterait, si l’on veut raisonner avec d’autres bornes de changer la valeur dans les formules ?

Merci

Williams

 

[KenDev]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Bonsoir Williams,

=SI(MAX(NB.SI(C5:F5;">" & $B$2);NB.SI(C5:F5;"<-" & $B$2);4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2))+MIN(NB.SI(C5:F5;">" & $B$2);NB.SI(C5:F5;"<-" & $B$2);4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2))=2;"impossible";SI(SI(B5>$B$2;$B$2;SI(B5<-$B$2;-$B$2;0))=0;SI(4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2)=MAX(NB.SI(C5:F5;">" & $B$2);NB.SI(C5:F5;"<-" & $B$2);4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2));1;0);SI(SI(B5>$B$2;$B$2;SI(B5<-$B$2;-$B$2;0))=$B$2;SI(NB.SI(C5:F5;">" & $B$2)=MAX(NB.SI(C5:F5;">" & $B$2);NB.SI(C5:F5;"<-" & $B$2);4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2));1;0);SI(NB.SI(C5:F5;"<-" & $B$2)=MAX(NB.SI(C5:F5;">" & $B$2);NB.SI(C5:F5;"<-" & $B$2);4-NB.SI(C5:F5;">" & $B$2)-NB.SI(C5:F5;"<-" & $B$2));1;0))))

Cordialement

KD

 

[williamss]

Re : recherche une formule avec SI en ce basant sur 5 colonnes

Merci bp,

En jouant avec les "" je n'y etais pas arrivé car je ne savais pas qu'ici qu'il fallait le &.

Williams