Formule complexe excel ! Résolu/Fermé

Question

Bonjour,

Attention voilà une formule qui me casse la tête bien comme il le faut.

Voilà ce que je dois faire.   

J'ai deux statuts celui de départ 1100 et celui d'arrivée 1650.
J'ai une date pour le statut 1100 et une pour le statut 1650.
J'ai un nombre de données qui varie selon les mois (en janvier je peux avoir 20 dates puis février 100 je dois donc prendre en compte toute la feuille excel pour permettre l'automatisme)
Je dois calculer le délai moyen pour toute ma feuille de passage entre le statut 1100 et le statut 1650.
Ici vous me direz limite c'est simple.... mais il s'avère que dans ma colonne 1650 j'ai des trous étant donnée que mon ordre n'est pas arrivé encore à 1650... Je peux donc avoir des lignes avec une date en 1100 et pas en 1650 il faut donc exclure ces lignes.
En plus de cela j'ai une colonne statut actuel dans lequel on me dit l'ordre est actuellement en 1100 ou entre 1100 et 1650 (1250, 1300 , 1400...) mais aussi j'ai des statuts 1990 qui eux sont particuliers et ne doivent pas être pris en compte à savoir que si j'ai un statuts 1990 j'ai une date de 1100 et une de 1650 il faut donc aussi exclure cela du calcul final.

En simple la formule doit être comme ceci : je calcule le délai moyen de passage entre tous mes 1100 et mes 1650 en excluant les ordres qui n'ont pas de dates en 1650 et ceux qui sont en 1990...

Que du bonheur non ?

michel_m · Answer

Bonjour,

Montre un bout de ton classeur, car tes explications sont pour le moins "brouillon"
pour mettre un classeur format XL97-2003
https://www.cjoint.com/
et tu colles le lien proposé dans ton message

Antoine · Answer

Voilà le fameux fichier... 
Ici c'est une simulation. 
A terme il  devrait y avoir au minimum 500 lignes il faut donc prendre en compte le document en entier.


https://www.cjoint.com/?bCl2wSTemz

tompols · Answer

Salut, essaie avec cette formule (plage à adapter) : =SOMMEPROD((J4:J20<>1990)*(M4:M20<>"")*(M4:M20-L4:L20))/SOMMEPROD((J4:J20<>1990)*(M4:M20<>""))

michel_m · Answer

OK merci, Donc tu voudrais avoir le temps moyen de passage 1100 à 1650 colonnes L & M ? c'est bien çà ? SI c'est OK essaies en test sur ligne 4 à 9 (délai moyen 7) =MOYENNE(SI((L4:L9<>"")*(M4:M9<>"");(M4:M9)-(L4:L9))) formule matricielle à valider par Ctrl+maj+entree, le curseur étant dans la barre de formules et non par entree comme d'hab XL encadre alors automatiquzment la formule par des accolades si ca te convient remplace ensuite les 9 par es 500 lignes Edit: en lisant la réponse de Tompols (bonjour),je vois que j'avais pas vu la colonne J =MOYENNE(SI((J4:J500=1650)*(L4:L500<>"")*(M4:M500<>"");(M4:M500)-(L4:L500)))

Antoine · Answer

ça marche parfaitement lorsque l'on dépasse pas 20 par contre vu que je vais inclure ça dans une macro, il faut que dès le départ je fasse ma formule pour qu'elle prenne en compte toute la feuille même si il y a des lignes totalement vides. Dans le cas de ta formule si je garde mon fichier comme cela et que je remplace 20 par l'infini j'ai un résultat de 0

michel_m · Answer

Pourquoi ne pas avoir précisé que tu voulais faire ca par macro au départ? la résolution est complètement différente !!! d'autre part, que cela soit par sommeprod ou par matricielle, le calcul est refusé si on prend des colonnes entières...

on a que ce que tu as écrit pour t'aider, donc...

tompols · Answer

Re,
effctivement c'est différent...que veux-tu exactement : 
- on peut faire le calcul en vba et n'afficher que le résultat
- on peut créer dynamiquement la formule en vba en determinant la derniere ligne de ta liste
- on peut aussi imaginer juste une formule(sans vba), soit on fixe la plage jusqu'à la ligne 65536, soit on determine la derniere ligne ds la formule....
- autres....

Antoine · Answer

Désolé d'avoir oublié ce gros détail...

Oui il faut que je mette tout ça dans une macro avec vous une solution ?

Merci pour votre aide à tous !

tompols · Answer

Re Bon tu nous dit toujours pas si le calcul doit s'effectuer ds le code et renvoyer la valeur ou si tu veux une formule... J'ai pris l'option 2, pour mettre la formule en X1 : derlig = Sheets(1).Range("A65536").End(xlUp).Row Range("X1").Formula = "=Sumproduct((J4:J" & derlig & "<>1990)*(M4:M" & derlig & "<>"""")*(M4:M" & derlig & "-L4:L" & derlig & "))/Sumproduct((J4:J" & derlig & "<>1990)*(M4:M" & derlig & "<>""""))"

tompols · Answer

re,
Pour la 2eme formule : 
"=SUMPRODUCT((O4:O" & derlig & "-L4:L" & derlig & ">2)*1)"

Antoine · Answer

J'ai tout testé c'est génial pour les deux !!!! Merci beaucoup pour ces réponses et surtout la rapidité !!!!

tompols · Answer

de rien :)

Formule complexe excel !

12 réponses

Discussions similaires