Erreur #valeur avec DATEDIF

[auregot]

Bonjour,

Voilà bien 24h que je bloque sur une formule. Je viens de trouver l'erreur qui cause l'affichage #valeur pour enfin pouvoir vous demander si vous avez une solution ! (c'est déjà pas mal ! )

Voici la formule me permettant de calculer le nombre de personnes ayant, aujourd'hui, entre 45 et 54 ans :

=SOMMEPROD(
(DATEDIF(Naissance;AUJOURDHUI();"y")>=45)
*(DATEDIF(Naissance;AUJOURDHUI();"y")<=54)
)

Naissance correspond donc simplement à une colonne que j'ai nommée "Naissance". Le problème, je suppose, est que ma première ligne contient les titres des colonnes... ici "Date de naissance". Si je remplace "Date de naissance par "01/01/1960", par exemple, la formule fonctionne. S'il y a du texte, cela ne fonctionne pas.

Ce que je ne souhaite pas faire :
- Identifier la plage de données avec les cellules de début et de fin, exemple : F2:F3456 (dans ce cas, le jour où quelqu'un rajoutera une colonne avant, ma formule ne sera plus valide)

Je ne sais pas si ça peut se gérer avec des références relatives/absolues, je ne maîtrise pas assez ce fonctionnement.

Que faire ?

Merci d'avance à vous !!!

 

[ERIC S]

Re : Erreur #valeur avec DATEDIF

Bonjour

j'ai mis des dates de naissance en colonne A et essayé cette formule

=NB.SI(A:A;"<=" & DATE(ANNEE(AUJOURDHUI())-45;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))-NB.SI(A:A;"<" & DATE(ANNEE(AUJOURDHUI())-54;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))

pour sommeprod, il faut toujours borner les cellules

 

[Modeste geedee]

Re : Erreur #valeur avec DATEDIF

Bonsour®

Naissance correspond donc simplement à une colonne que j'ai nommée "Naissance". Le problème, je suppose, est que ma première ligne contient les titres des colonnes... ici "Date de naissance".


Ce que je ne souhaite pas faire :
- Identifier la plage de données avec les cellules de début et de fin, exemple : F2:F3456 (dans ce cas, le jour où quelqu'un rajoutera une colonne avant, ma formule ne sera plus valide)

Je ne sais pas si ça peut se gérer avec des références relatives/absolues, je ne maîtrise pas assez ce fonctionnement.

Que faire ?

Merci d'avance à vous !!!

il faut utiliser le gestionnaire de noms et créer une plage dynamique

• Definir Nom : Naissance
• fait référence à :
• =DECALER(Feuil2!$F$1;1;0;NB(Feuil2!$F:$F);1)

veillez à ce qu'il n'y ait pas de vide dans la plage de définition,
ni date erronée (considérée alors comme texte : 31 Février 2013)

lors de l'insertion de colonne ou de ligne, le nom et l'adresse est alors gérée dynamiquement par Excel

nb :
méfiance : l'utilisation de la fonction DATEDIF est entachées de bugs

 

[auregot]

Re : Erreur #valeur avec DATEDIF

Rebonjour !

Merci pour vos retours rapides. Alors, aucune des deux propositions ne fonctionne.
Voilà ce qui fonctionne chez moi et que je voudrais adapter pour que cela fonctionne durant des années à venir (je suis la seule à gérer excel et je compte quitter mon emploi) :

=SOMMEPROD(
(H=1)
*(Naissance>=DATE(1957;1;1))
*(Naissance<=DATE(1966;12;31))
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1))
)

J'avais simplifié la formule pour mieux pouvoir communiquer dessus.
Voilà pourquoi il me faut utiliser SOMMEPROD(). Qui, par ailleurs, fonctionne très bien sans borner des cellules et en utilisant les noms prédéfinis.

Lorsque j'utilise NB.SI, suivant vos indications, Excel m'empêche de mettre rajouter des critères en utilisant la fonction ET(), il me dit qu'il y a trop de propositions.

Lorsque je renomme ma plage de cellules en utilisant DECALER(), la formule me renvoie l'erreur #N/A. Pourtant ça me semblait être une bonne idée !

WTF ?

 

[ERIC S]

Re : Erreur #valeur avec DATEDIF

Re

le rajout de critères n'était pas dans la question initiale il me semble

ou tout au moins je ne l'avais pas compris ainsi

 

[auregot]

Re : Erreur #valeur avec DATEDIF

PS, on dirait que le problème vient du fait que DATEDIF() n'arrive pas à gérer les cellules contenant du texte...

J'ai bel et bien ma ligne d'en-tête sur tout mon document, aucune formule ne m'a jamais posé problème avec cela depuis le départ... et là, impossible avec DATEDIF.

Ce que je souhaite, c'est, outre les autres fonctions de ma formule, les personnes ne soient prises en compte que lorsqu'elles ont entre 45 et 54 ans. Chaque année, le calcul devra se refaire, il faut que les formules puissent fonctionner toutes les années à venir sans avoir à les retoucher.

Trop dur pour Excel ? Je ne veux pas le croire !

 

[auregot]

Re : Erreur #valeur avec DATEDIF

PS, on dirait que le problème vient du fait que DATEDIF() n'arrive pas à gérer les cellules contenant du texte...

J'ai bel et bien ma ligne d'en-tête sur tout mon document, aucune formule ne m'a jamais posé problème avec cela depuis le départ... et là, impossible avec DATEDIF.

Ce que je souhaite, c'est, outre les autres fonctions de ma formule, les personnes ne soient prises en compte que lorsqu'elles ont entre 45 et 54 ans. Chaque année, le calcul devra se refaire, il faut que les formules puissent fonctionner toutes les années à venir sans avoir à les retoucher.

Trop dur pour Excel ? Je ne veux pas le croire !

 

[auregot]

Re : Erreur #valeur avec DATEDIF

Effectivement ERIC, je ne l'avais pas précisé, j'avoue faire des formules à rallonge et tente de les simplifier pour qu'on en discute tranquillement... ^^

 

[auregot]

Re : Erreur #valeur avec DATEDIF

Raté, je sais pas d'où ça vient... si je remplace Naissance par $F2:$F3456, ça me renvoie un #N/A également.

Voilà la formule complète qui foire :

=SOMMEPROD(
(H=1)
*(DATEDIF(Naissance;AUJOURDHUI();"y")>=45)
*(DATEDIF(Naissance;AUJOURDHUI();"y")<=54)
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1))
)

 

[ERIC S]

Re : Erreur #valeur avec DATEDIF

Re

dans ma proposition, j'avais remplacé datedif à base de Date (plus long à écrire) mais qui passe sur tous les Excel

DATE(ANNEE(AUJOURDHUI())-45;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI()))

 

[ERIC S]

Re : Erreur #valeur avec DATEDIF

re

et tes accompagnement_janvier... c'est des colonnes entières?

 

[auregot]

Re : Erreur #valeur avec DATEDIF

RE aussi...

BANCO pour ta proposition :

=SOMMEPROD(
(H=1)
*(Naissance<=DATE(ANNEE(AUJOURDHUI())-45;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))
*(Naissance>=DATE(ANNEE(AUJOURDHUI())-54;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1))
)

RESTE un ennui majeur... lorsque je comptabilise la totalité de mon public (âge par âge, sexe par sexe), les résultats des formules sont différents du comptage que je peux faire manuellement.

Je reviendrai dessus après mon weekend prolongé, si vous le voulez bien

BON WEEKEND !!!!

 

[ERIC S]

Re : Erreur #valeur avec DATEDIF

Bonjour

je te laisse en week-end, mais pour la suite : essaie d'envoyer un bour de fichier (quelques lignes avec les formules et aussi attendus)

Cela t'aidera à voir la vie en rose. Depuis hier soir, les problèmes n'existent plus

 

[auregot]

Re : Erreur #valeur avec DATEDIF et utilisation avancée de SOMMEPROD()

Salut Eric !

Bon, je viens de retravailler mon fichier pour t'envoyer un exemple et j'ai solutionner une partie de mon problème.

Je ne comprenais pas pourquoi les chiffres calculés par excel étaient supérieurs à ceux que je calculais moi, manuellement.
En fait, c'était une erreur d'utilisation de SOMMEPROD().

Lorsque je voulais savoir le nombre de personnes en fonction de leur venue en janvier à l'un OU l'autre des ateliers/accompagnements, je faisais par exemple :

=SOMMEPROD(
(H=1)
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1))
)

En fait, je n'avais pas compris l'utilisation des opérateurs logiques "+" ou "*" avec SOMMEPROD(). Il fallait que ma "somme", mes "+", soient calculés pour être ">0" !!!!! Donc :

=SOMMEPROD(
(H=1)
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1)>0)
)

Pour bien utiliser la formule, j'ai voulu la tester aussi sans distinction de genre... j'ai donc enlevé "(H=1)*"
Or, sans que je comprenne pourquoi, la formule ne fonctionne que si je mets : "((H=1)+(F=1)>0)" (avec ou sans le ">0" d'ailleurs...) si tu as une explication à l'obligation de cette partie de formule, je veux bien... (je vois pas l'intérêt de lui dire que ça doit être un homme ou une femme puisque tout le monde est un homme ou une femme !)

=SOMMEPROD(
((H=1)+(F=1)>0)
*((Accompagnement_Janvier_2011>=1)+(Job_Cafe_Janvier_2011>=1)+(CV_Janvier_2011>=1)+(Informatique_Janvier_2011>=1)+(Valorisation_Janvier_2011>=1)+(Linguistique_Janvier_2011>=1)+(Acces_Libre_Janvier_2011>=1)+(Manifestation_Janvier_2011>=1)>0)
)

Il me reste encore un problème que je n'arrive pas à résoudre pour l'instant... peut-être que tu pourras m'aider ?
Il me compte 6 hommes ayant entre 25 et 44 alors qu'il y en a 8... pourtant, tout le reste fonctionne... il doit encore y avoir une erreur dans ma formule !!!! MAIS EUHHHHHHHHHHHHHHH

Je te joins un fichier minimal pour que tu observes mes calculs, en espérant que tu trouves le fameux bug... ^^

Je te remercie énormément pour ton aide précieuse... sincèrement, seule, j'aurais laissé tomber !

Et la vie est rose même avec des soucis d'excel... perso, chercher des solutions, surtout via l'informatique, CA ME REGALE !!!

++

 

[auregot]

Re : Erreur #valeur avec DATEDIF

La seule chose que je vois, c'est que j'ai 2 personnes qui ont 44 ans cette année et qui les ont déjà fêtés...

Ils ne serait ni pris en compte pour les 25/44 :

*(Naissance<=DATE(ANNEE(AUJOURDHUI())-25;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))
*(Naissance>=DATE(ANNEE(AUJOURDHUI())-44;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))

Ni pour les 45/54 :

*(Naissance<=DATE(ANNEE(AUJOURDHUI())-45;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))
*(Naissance>=DATE(ANNEE(AUJOURDHUI())-54;MOIS(AUJOURDHUI());JOUR(AUJOURDHUI())))

????

Bon, l'erreur est là, mais il est clair que j'ai tout faux !!... Pffffff, vais-je m'en sortir un jour ?