cumulatief berekenen op basis van weeknummers

[Huigie]

In excel heb ik per week op dagniveau waarden staan.

nu is het de bedoeling dat als er een weeknummer in een cel wordt ingevoerd, volgende berekeningen worden gedaan:
- weektotaal 2011 -> gelukt
- weektotaal 2011 vorige week -> gelukt
- weektotaal 2010 -> gelukt
- maandcum. 2011 -> deels gelukt
- maandcum. 2010 -> deels gelukt
- cum 2011 -> deels gelukt
- cum 2010 -> deels gelukt.

Wat me niet lukt is dat als je een weeknummer geeft, die eerder dit jaar was, dus bv wk 3, dan moeten de cum cijfers een som zijn van de waarde van week 1 t/m 3. En bijv. wk 10 valt in maand 3, dan de cum. van 1 mrt t/m 13 mrt (laatste dag van wk 10). Ditzelfde met de gemiddelde.
Die formule krijg ik niet voor elkaar.
Kan iemand mij op weg helpen?

Uitgekleed voorbeeld:
Bekijk bijlage 133787

Dank alvast!

 

[robdgr]

Jaqueline,

Ik vermoed

=SOMMEN.ALS($J$6:$NJ$6;$J$3:$NJ$3;"<"&D26+1;$J$2:$NJ$2;MAAND(DATUM(JAAR(VANDAAG());1;1)+D26*7))/AANTALLEN.ALS($J$3:$NJ$3;"<"&D26+1;$J$2:$NJ$2;MAAND(DATUM(JAAR(VANDAAG());1;1)+D26*7))

met in D26 even het weeknummer. Of gemiddelden.als

 

[Huigie]

hier kom ik al heel wat verder mee!! joepie!

Nu nog volgende:
als ik in bv cel e1 een weeknummer invoer (28) en in cel f1 de formule:

=DATUM(JAAR(NU());1;($E1)*7+6)-WEEKDAG(DATUM(JAAR(NU());1;5))

dan krijg ik netjes de laatste dag van die week als datum (17-7-2011), waardoor ik heel mooi cum jaar kan maken.
Nu alleen cum van de lopende maand nog.
Stel week 28 is criterium, eind van de cum periode is 17-7-2011 maar begin moet dan eerste dag van de maand zijn (1-7-2011). Zodat ik met groter dan en kleiner dan criteria de waarden kan optellen.

Hoe bereken ik op basis van het weeknummer, de eerste dag van de maand die daarbij hoort?

 

[robdgr]

Jacqueline.

Wat dacht je van

=DATUM(JAAR(NU());MAAND(F1);1)

of

=DATUM(JAAR(NU());MAAND(DATUM(JAAR(NU());1;($E1)*7+6)-WEEKDAG(DATUM(JAAR(NU());1;5)));1)

 

[Huigie]

Geweldig!!!
Dat met die datum-dingen is voor mij nog een beetje hokus-pokus. Doe best heel veel met excel, maar de logica van formules met 'n datum zit nog niet in mijn grijze massa.

Dank voor de hulp, kom al weer een stap verder.

Weeknummers naar data rekenen in 2010 is inmiddels ook gelukt, dus stapje voor stapje kom ik er wel. En samen met jullie hulp, maak ik af en toe een flinke sprong!! :thumb:

Dank, dank, dank!!!

 

[robdgr]

Jacqueline,

Je formule in F1 gaat de mist in als 5 januari op een donderdag valt, je krijgt dan de zondag een week later. Gebruik

=DATUM(JAAR(NU());1;$E$1*7+ALS(EN(WEEKDAG(DATUM(JAAR(NU());1;1);2)<5;WEEKDAG(DATUM(JAAR(NU());1;1);2)>1);0;7)-WEEKDAG(DATUM(JAAR(NU());1;$E$1*7);2))

om dat te voorkomen.

 

[Huigie]

Super dank je wel!
Heb je voor mij ook op de oplossing hoe hij wordt als bv week 28 moet worden berekenen voor het jaar 2010?

 

[robdgr]

Jacqueline,

Had je toch kunnen bedenken?:

=DATUM(JAAR(NU())-1;1;1)

 

[Huigie]

ik had er:

=DATUM(JAAR(NU())-1;1;($B1)*7+6)-WEEKDAG(DATUM(JAAR(NU())-1;1;5))

van gemaakt.... te omslachtig?

 

[robdgr]

Jacqeline,

Nee, maar dan pleit ik voor:

=DATUM(JAAR(NU())-1;1;$E$1*7+ALS(EN(WEEKDAG(DATUM(JAAR(NU())-1;1;1);2)<5;WEEKDAG(DATUM(JAAR(NU())-1;1;1);2)>1);0;7)-WEEKDAG(DATUM(JAAR(NU())-1;1;$E$1*7);2))

 

[Huigie]

Nou, ik ben al een heel eind!! Dank dank voor alle input!!

Wil nu graag het volgende bereiken: in een willekeurig cel wordt een weeknummer door de gebruiker in gevuld, en a.d.h. van dat weeknummer wordt het hele bestand herberekend.
Nu wil ik graag, dat in die cel waar het weeknummer ingevuld wordt, geen weeknummer van de huidige week of in de toekomst ingevuld kan worden. Bv we zitten nu in week 31. Er mag dan willekeurig een nr tussen 1 en 30 ingevuld worden, maar niet tussen 32 en 52. Volgende week mag er <31 ingevuld worden.
Naast de cel met het weeknummer staat een berekende cel die de zondag van dat weeknummer geeft, dat kan wellicht van pas komen?
Weet dat het met gegevensvalidatie moet kunnen, maar krijg het niet voor elkaar.

 

[robdgr]

Jacqueline,

Inderdaad via validatie met getal: minimum 1 en maximum weeknummer vandaag - 1. Het invoerbericht en de foutmelding heb ik maar even achterwege gelaten.

 

[Huigie]

Dank je wel, dat werkt super!! Alleen krijg ik geen foutmelding, vinkje staat wel aan, tekst is ingevoerd, maar verwacht een pop-up of zo.
Als ik nu een weeknummer in de toekomst ingeef, dan pakt hij de laatste week (dus zet hem in dit geval dan op 30).
Had er graag nog een waarschuwingsmelding bij....

 

[robdgr]

Jacqueline,

Als ik de bijlage invul, krijg ik hem te zien. Met nog een plaatje wordt het bestand te groot.

 

[Huigie]

Raar hoor, ik krijg echt niets te zien. Als ik in het invoerveld wat invul, laat hij wel een omschrijving bij de cel zien, maar niet een pop-up als ik een te hoog nummer invul.

Bekijk bijlage 134323

Bekijk bijlage 134321

 

[robdgr]

Vreemd: dit is mijn beeld. Je voegt wel een plaatje bij met het getal 30. Hij begint pas bij 31.

 

[Huigie]

Die melding krijg ik echt niet.
Als ik 31 invul, dan maakt excel daar zelf wel netjes 30 van, dus iets in de toekomst ingeven lukt niet, maar voor de uiteindelijke gebruiker zou het toch fijn zijn als er een melding komt.
Als jij in mijn voorbeeldbestand het getal 31 invul, komt dan de melding wel?
Dan zou het aan mijn excel kunnen liggen.
Werk thuis tussendoor aan het bestand met excel 2010, maar dat zou toch geen problemen mogen opleveren?

 

[robdgr]

En als je het vinkje weghaalt bij lege cellen negeren? Dat kan versiegebonden zijn.

 

[Huigie]

Helaas, dat haalt niets uit.
Het is verder niet zo heel erg, maar voor de "mooierigheid" van zo'n bestand waar heel veel tijd en energie in zit, en zo goed blijkt te gaan werken, is dat nou net dat puntje op de i, toch?

Ik zal straks thuis met excel 2010 eens kijken wat er dan gebeurd.....

 

[robdgr]

Ik zal straks thuis met excel 2010 eens kijken wat er dan gebeurt.....

Dat heb ik je gemeld, want ik werk met 2010.