Calculer une moyenne suivant temps de présence

Bonjour

Vendredi soir : espoir !

Pourriez vous me dépanner sur une formule qui calculerait la moyenne de primes suivant le nombre de mois de présence dans l'année ?

J'ai mis mon tableau en PJ.

Merci par avance de votre temps.

21classeur1.xlsx (15.35 Ko)

Bonjour,

Je vois que tu sait déjà utiliser DateDif, ben c'est pas plus compliqué, sauf que pour compter le nombre de mois au lieu de mettre l'argument "ym" tu met juste "m" et il ne plafonnera pas a 12

Cf pièce jointe.

17classeur1.xlsx (15.88 Ko)

Hello !

Je me suis mal exprimée, en fait il faut que ça plafonne à 12 car il s'agit de la moyenne annuelle des primes à date donc si la personne est là depuis 2 ans, elle a un compteur complet de janvier 2016 à date et si elle est arrivée en avril 2016, il faut que ça compte d'avril à date

Tu vois ?

Bonjour,

à voir :

=H4/DATEDIF(MAX(D4;DATE(ANNEE($E$1);1;1));$E$1;"m")+1

tu auras peut-être un ajustement à faire, on ne sait pas comment tu veux compter les mois incomplets.

eric

Merci, ça m'a l'air de marcher, je testerai ça tranquillement lundi matin.

Bon weekend à tous

En la revoyant à l'écran je préfère ça finalement :

=H4/(DATEDIF(MAX(D4;DATE(ANNEE($E$1);1;1));$E$1;"m")+1)

eric

Merci

Je clôture lundi matin après avoir testé tous les cas !

Bonjour Astragale, toutes et tous,

Salut Watewmark, eriiic,

Je m'immisce dans ce fil car j'avais commencé les calcules hier soir et ce matin je n'obtiens pas tout a fait les mêmes résultats qu'eriiic.

En fait mes résultats colonne "G" son un mélange entre les résultats de la première formule d'eriiic colonne "L"et la deuxième colonne "M".

C'est troublant alors dans le doute, je publie ce que j'ai fais.

=SI(H4=0;"";H4/SI(D4="";"";SI(D4>DATE(ANNEE(AUJOURDHUI());MOIS(1);JOUR(1));MOIS(AUJOURDHUI())-MOIS(D4);DATEDIF(DATE(ANNEE(AUJOURDHUI());MOIS(1);JOUR(1));AUJOURDHUI();"m"))))

Cordialement.

9classeur1-v1.xlsx (21.73 Ko)

Bonjour

Merci d'avoir planché sur le sujet !

Je n'aurais pas été capable (pas encore !) de réaliser vos formules alors je vais les tester toutes lundi, ça me fait avancer

Bon samedi !

Bonjour à tous,

Je dois avouer que je ne m'y retrouve pas bien sur ce que l'on est censé calculer !

une formule qui calculerait la moyenne de primes suivant le nombre de mois de présence dans l'année ?

Il y a une ambiguïté dans la mesure où l'on ne sait si l'expression nombre de mois de présence a une valeur comptable ou n'est qu'une autre façon de dire moyenne suivant temps de présence tel que mentionné dans le titre du sujet.

Autrement dit l'évaluation doit se calquer sur le système comptable de détermination de la prime...

Autre aspect de la question, j'ai compris le sujet comme calcul de la prime annuelle moyenne en année pleine distribuée.

En prenant en compte le prorata du temps de présence dans l'année (lorsqu'il intervient), je procèderais avec la formule suivante :

=MOYENNE(H4:H33/FRACTION.ANNEE(SI(D4:D33>DATE(2016;1;1);D4:D33;DATE(2016;1;1));DATE(2016;12;31);1))

La formule est matricielle : à valider par Ctrl+Maj+Entrée.

Elle rétablit chaque prime à sa valeur annuelle et fait la moyenne.

La fonction FRACTION.ANNEE permet de rétablir la prime annuelle complète (non réduite au prorata du temps de présence). Elle permet éventuellement de moduler le calcul avec un paramètre prenant en compte les modalités de fractionnement de l'année pour le calcul de la prime. Ici 1 indique que le calcul est fait au prorata du temps réel (à remplacer par 4 si le calcul est fait sur des mois/année de 30j./360j. selon la norme européenne, ou par 3 si le calcul est fait des années de 365j. [ne différenciant pas les années bissextiles]).

Si le calcul est fait à partir d'un nombre de mois entier, cette fonction demeurera utilisable pour le calculer, dès lors que l'on sait comment il doit être calculé...

Cordialement.

Re à toutes et tous,

Si l'on considère que la moyenne doit se faire de la date du début du mois suivant la date d'entrée, ou, si l'ancienneté est supérieur au début de l'année en cours , jusqu'à la fin du mois inclus de l'année en cours.

Alors il faut déduire le mois en cours qui n'ai pas achevé.

D'où une autre façon de calculer de mon point de vue, sinon, il faudra faire de date à date au jour prés ce qui compliquerai l'affaire.

Aller, je me lance, car je crois qu'il y a que moi qui me comprends le fichier sera plus parlant.

A voir.

Cordialement.

PS: Bonjour MFerrand,

J'ai rien compris

Cordialement.

110classeur1-v2.xlsx (20.25 Ko)

bonjour

tout simplement et basta

=ENT((MAINTENANT()-D4)/30,5)-1

maintenant se met a jour lors de l'uoverture du fichier

-1 pasque le 1er mois ne compte pas because periode d'essai

cordialement

Il est vrai que j'ai peut-être mal présentée ma question au départ.

Ce tableau représente les gains annuels d'une équipe de commerciaux.

Si l'équipe ne bougeait pas sur un an ce serait évidement très simple : gain/12 et hop ! mais les salariés vont et viennent...

Je mets à jour ce tableau 3 fois par an car les primes sont calculées à ce rythme. Certains sont là depuis le début de l'année, d'autres pas et c'est là que la formule doit s'adapter et faire la moyenne depuis le 1er janvier si arrivée antérieure ou depuis le mois d'arrivée si postérieure.

Mais en tous cas merci


Ah ! Je répondais à MFerrand sans avoir vu les réponses suivantes

Tulipe ta formule est la plus courte !

Vivement lundi que je m'amuse avec vos formules

@mdo100

ne pas tenir compte de ma 1ère proposition j'avais oublié les () du diviseur.

La différence qu'on sur l'autre c'est que de janvier à mars par exemple je compte 3 mois (qu'on soit le 1er ou le 31 mars) et toi 2 mois puisque tu fais mois_fin - mois_début. Tu comptes les barrières et moi les poteaux.

Comme le demandeur à laissé un flou total sur ce qu'est un mois valide les 2 réponses sont bonnes

eric

re

ne te casse pas la tete ;fais au plus simple ;car une fois la regle etablie ;les nouveaux venus prendront le train en marche

Bonsoir Astragale,

Tu ne m'as pas vraiment répondu ! Mais ta réponse laisse supposer que les primes ne sont pas calculées sur une durée, mais sur un résultat. S'agissant de commerciaux, c'est relativement logique... En conséquence, le calcul d'une moyenne annuelle relève d'une évaluation et constitue éventuellement un indicateur.

Dans ces conditions, je considère que le calcul au prorata du temps réel (en nombre de jours sur le nombre de jours de l'année), qui correspond à la formule que j'ai proposée est le plus approprié.

Cette formule donne comme résultat sur ton tableau : 5 068,89.

Cordialement.

Bonjour à tous,

Alors...

Tulipe : ça ne marche que si la date d'arrivée est postérieure à janvier, sinon par exemple pour une ancienneté de 2 ans ça me compte 24 mois au lieu des 10 (janvier à octobre 2016), par contre je n'avais pas gardé le -1 car la période d'essai ne compte pas.

Eric : bizarrement j'ai des moyennes correctes quand la date d'arrivée est antérieure à janvier mais pas après, à moins que la formule fasse un prorata d'octobre ? Si c'est le cas alors c'est bon.

MFerrand : Je ne m'y retrouve pas. Par exemple 1ère ligne : 7 mois d'ancienneté et prime de 881€ donc moyenne sur 7 mois = 126€ et tu trouves 147€, à quoi est-ce que ça correspond ?

Mdo : pareil, ça marche pas sur toutes les durées.

Ils sont chiants ces commerciaux, une seule prime annuelle et ce serait plus simple

Bonjour,

J'ajouterai que tant que tu ne répondras pas à la question déjà posée Qu'est-ce qu'un mois valide ? tu auras plusieurs réponses toutes aussi correctes.

Dans ma formule un jour travaillé dans le mois suffit, du 14/03 au 24/10 ça fait donc 8 mois, 881/8=110.125.

Maintenant si c'est 15j travaillés ou tout mois complet ça change forcément...

eric

Je ne vois pas bien ce que tu as fait avec ma formule !

Celle-ci ne calcule pas une moyenne mensuelle mais une moyenne annuelle !

Elle ne fait pas une un calcul individuel mais global : elle calcule la prime moyenne annuelle par commercial, en la pondérant au prorata du temps de présence.

Exemple : prime perçue en H4 =881,00 pour une durée de présence du 14/03 au 31/12/2016.

En année pleine la prime aurait été de : 1104,27 (pondération selon durée de présence) retenue pour calculer la moyenne des 30 commerciaux.

Pour trouver ce résultat de la prime en année pleine, la formule à utiliser :

=H4/FRACTION.ANNEE(SI(D4>DATE(2016;1;1);D4;DATE(2016;1;1));DATE(2016;12;31);1)

Si on applique cette formule à chaque commercial et que l'on fait la moyenne (des résultats obtenus pour chacun des 30 commerciaux), tu obtiendras le même résultat que la formule matricielle que j'ai donnée calcule en une seule fois.

Si c'est une moyenne mensuelle par individu que tu veux obtenir, il suffit d'ajouter /12 à la formule ci-dessus, puisqu'elle donne un résultat annuel. Soit pour l'exemple cité : 92,02, différent de tes 126 car il n'y a pas 7 mois de présence mais précisément 293 jours du 14/03 au 31/12/2016.

Si la prime n'est pas complète pour l'année (il reste une fraction à payer...), il faut prendre comme référence la période de paiement, période couverte par la prime, date invariable une fois définie (la date en E1, date du jour ne peut être prise en considération, elle varie tous les jours et ne peut correspondre à une date borne de paiement de prime...)

On peut donc avoir 3 moyennes mensuelles dans l'année si paiement de primes en 3 fois comme il apparaît, à condition de connaître les dates de début et fin de chaque période.

Cordialement.


Contrairement à Eric (faut bien qu'on ne soit pas d'accord de temps en temps ! ), je maintiens que la bonne option est un calcul sur le temps réel (et non des mois théoriques. Il s'agit en effet de primes (si l'on suit les indications ajoutées au fur et à mesure) générées par une activité commerciale (et non réglées sur des périodes forfaitairement définies). Ce calcul correspond de fait à un indicateur d'activité, laquelle ne peut être appréciée que sur la période où elle s'exerce réellement...

Mais ce différent n'est pas un problème Excel... Mais cela confirme que l'on aboutira nécessairement à des résultats différents selon l'analyse faite du problème et les options retenues.

J'avais en effet pensé d'abord que l'on travaillait sur l'année, et me suis maintenant avisé que la colonne C3 étant vide, elle était destinée à accueillir une 3e prime de l'année. D'où la question sur les dates effectives : finC1/débutC2 et finC2/débutC3 (si confirmé que : débutC1 = 01/01/2016 et finC3 = 31/12/2016).

Cordialement.

Eric, un mois valide est un mois commencé.

Dès lors que l'on était entré dans le mois d'octobre je calculais la moyenne sur 10 mois.

Mferrand, je comprends mieux, c'est en fait plus sophistiqué que ce que je pensais faire au départ car c'est au jours près

Merci encore Messieurs de votre aide !

Edit : oui effectivement il y a une troisième prime calculée fin décembre. Les 3 périodes sont :

1/ janvier à mars

2/ avril à juillet

3/août à décembre

Rechercher des sujets similaires à "calculer moyenne suivant temps presence"