[Excel] Het grote Excel vragen topic #32

quote:

=INDEX(INDIRECT("$B$"&3+(AFRONDEN(($A399-$A$364)/30;0)*9)&":$AW$"&9+(AFRONDEN(($A399-$A$364)/30;0))*9);ALS(WEEKDAG($A399;3)+B$363>7;ALS(WEEKDAG($A399;3)+B$363-7>7;ALS(WEEKDAG($A399;3)+B$363-14>7;ALS(WEEKDAG($A399;3)+B$363-21>7;ALS(WEEKDAG($A399;3)+B$363-28>7;ALS(WEEKDAG($A399;3)+B$363-35>7;NB();WEEKDAG($A399;3)+B$363-35);WEEKDAG($A399;3)+B$363-28);WEEKDAG($A399;3)+B$363-21);WEEKDAG($A399;3)+B$363-14);WEEKDAG($A399;3)+B$363-7);WEEKDAG($A399;3)+B$363);ALS(WEEKDAG($A399;2)+B$363>8;ALS(WEEKDAG($A399;2)+B$363-8>8;ALS(WEEKDAG($A399;2)+B$363-16>8;ALS(WEEKDAG($A399;2)+B$363-24>8;ALS(WEEKDAG($A399;2)+B$363-32>8;NB();40);32);24);16);8))

quote:

Op zondag 10 juni 2012 18:19 schreef Janneke141 het volgende:
Wat is de fout, #VERW? Want dan zal de verwijzing van je INDEX-formule wel buiten de grenzen van de zoekmatrix vallen.

quote:

Op zondag 10 juni 2012 18:06 schreef qu63 het volgende:
Dat je je eigen formules niet meer snapt..

[..]

Grmbl

-edit-
Nou ja, ik snap em wel, alleen niet waarom bij de 35 maanden er voor wel goed gaat, maar nu ik de sheet met 5 maanden uit wil breiden hij ineens raar doet

quote:

Op dinsdag 12 juni 2012 00:43 schreef McGilles het volgende:

[..]

Waarom een formule met zovaak als( ) erin? Veel te omslachtig en nauwelijks te lezen voor iemand anders. Zou ook wel de fout zijn, aangezien ergens een maximum ligt bij die =als() formule. Doe dit via een zoekfunctie in een matrix, is de lengte ineens 4x zo kort

quote:

Op dinsdag 12 juni 2012 11:15 schreef Deetch het volgende:
Als je de functie helemaal uit splitst in delen kom je er sneller achter waar de fout zit. Dus alle ALS() functies even apart nemen en kijken waar dat mis gaat.

quote:

Op zondag 10 juni 2012 18:06 schreef qu63 het volgende:
Dat je je eigen formules niet meer snapt..

[..]

Grmbl

-edit-
Nou ja, ik snap em wel, alleen niet waarom bij de 35 maanden er voor wel goed gaat, maar nu ik de sheet met 5 maanden uit wil breiden hij ineens raar doet

quote:

Op dinsdag 12 juni 2012 10:35 schreef qu63 het volgende:
Eigenlijk wordt A1:AZ361 dynamisch gevuld, maar ik heb nu even kopiëren/plakken speciaal/waarden gedaan.

De functie waar t om gaat staat in B364:CH403, maar alleen in B399:CH403 gaat ie fout
Hier te downloaden (in een .rar omdat m'm server .xlsx niet snapt )

quote:

Op dinsdag 12 juni 2012 22:39 schreef DaFan het volgende:
Goed bezig
Je weet met alle vragen mag je hier terecht

Tja Office 2010 is natuurlijk wel te verkrijgen via de minder legale manier. Ik vermoed dat niemand m zomaar heeft liggen, het is een duur pakket.
OpenOffice is vergelijkbaar, zeker in de basale functies, maar is toch geen Excel..

quote:

Op dinsdag 12 juni 2012 22:51 schreef Jesse_ het volgende:

[..]

Duur pakket?
60 euro heb je al drie licenties toch?

quote:

Op dinsdag 12 juni 2012 20:15 schreef McGilles het volgende:

[..]

Het gaat natuurlijk mis aangezien jij het verschil tussen 2 data deelt door 30 en dan gaat afronden naar 0 decimalen. Deel eens door 30,5 en trek dan de formule door, dan werkt het wel.

Btw: inderdaad een beetje een draakbestand. Het is dat ik lange dagen op werk maak, was ik werkloos dan had ik het bestand wel aangepast en retour gestuurd

quote:

Op dinsdag 12 juni 2012 23:15 schreef Meike26 het volgende:
Misschien dat ik dat dan idd wel ga aanschaffen, bedankt, ik zag al van die prijzen voorbij komen, maar dacht eigenlijk dat het te mooi was om waar te zijn.

quote:

Op dinsdag 12 juni 2012 14:27 schreef Deetch het volgende:

[..]

in welk deel van de formule gaat dit dan fout en waarom?

Is het zinvol om eens opnieuw te proberen?
• Wat wil je bereiken met dit monsterlijke gedrocht
• Wat heb je als input?
• Welke foutmelding krijg je?

quote:

Maand
Maandag
Dinsdag
Woensdag
Donderdag
Vrijdag
Zaterdag
Zondag
Totaal

quote:

=ALS(B4="";"";ALS(HORIZ.ZOEKEN($A$2;Projecten!$J$1:$AW$18;18;ONWAAR)/DAG(DATUM(JAAR($A$2);MAAND($A$2)+1;1)-1)=0;"";HORIZ.ZOEKEN($A$2;Projecten!$J$1:$AW$18;18;ONWAAR)/DAG(DATUM(JAAR($A$2);MAAND($A$2)+1;1)-1)))

quote:

=ALS(B3="";ALS(WEEKDAG($A$2;2)=2;1;"");ALS(B3+1<=DAG(DATUM(JAAR($A$2);MAAND($A$2)+1;1)-1);B3+1;""))

quote:

Op woensdag 13 juni 2012 04:02 schreef xaban06 het volgende:
Ik heb in de velden B2, C2, D2 t/m AF2 de waarde 00:00 staan, soms wordt het vervangen met bijvoorbeeld 00:10. Ik wil een opsomming hebben van hoeveel van deze velden de waarde 00:00 niet bevat.

Engelse Excel 2007.

quote:

Op woensdag 13 juni 2012 11:43 schreef draadstalig het volgende:
Geeft niet het gewenste resultaat. Ik heb nu een drop down gemaakt met de verschillende namen. Dus dat is opzich een stap in de goede richting, echter wil ik nu dat bij die naam de goede activiteiten laat zien en dus niet de totale planning. Ook google biedt tot nu toe geen hulp..

quote:

Op woensdag 13 juni 2012 11:45 schreef qu63 het volgende:

[..]

Dan kom je dus uit op als()-functies..
Bijvoorbeeld:
=ALS(A1="draadstalig";item 1 in de lijst voor draadstalig;ALS(A1="Deetch";item 1 in de lijst voor Deetch;ALS(A1="qu63";item 1 in de lijst voor qu63;"oeps, naam komt niet in de lijst voor!")))
Dit gaat alleen niet werken als je veel namen hebt..

quote:

Op woensdag 13 juni 2012 12:48 schreef Meike26 het volgende:
Eehm, even met mijn bijdehante beginners-kennis: Dat kan je toch simpel met filter doen ?

Heb hier gister een hele oefening over zit te maken met een rooster voor scheidsrechters, welke sportvelden en welke tijden ze waar/wat moesten fluiten.

quote:

Op donderdag 14 juni 2012 17:12 schreef Meike26 het volgende:
Hoe kan ik een Excel bestand online krijgen ? Photobucket ? Dan kan ik het jullie laten zien.

quote:

Op woensdag 13 juni 2012 11:43 schreef draadstalig het volgende:
Geeft niet het gewenste resultaat. Ik heb nu een drop down gemaakt met de verschillende namen. Dus dat is opzich een stap in de goede richting, echter wil ik nu dat bij die naam de goede activiteiten laat zien en dus niet de totale planning. Ook google biedt tot nu toe geen hulp..

quote:

Op zaterdag 16 juni 2012 01:08 schreef snabbi het volgende:
Onder het menu Gegevens heeft Excel de 'Wat-als'-analyse. Kies hier voor Doelzoeken.
Kies voor Cel Instellen L23
Kies voor Op Waarde 0
Kies voor Door wijzigen van Cel op C5

Mocht het niet helemaal werken, weet ik zeker dat je via de Help functie op Doelzoeker voldoende info kunt vinden om hem wel werkend te krijgen.

quote:

Op zaterdag 16 juni 2012 00:48 schreef coffeebiscuit1 het volgende:
Pfoe hier kom ik echt niet uit:

Ik zou heel graag iets willen bereiken in de onderstaande sheet... Het betreft hier een netto contante waarde methode, echter ik gebruik hem een beetje andersom.

Normaal is C5 een waarde waarmee je berekent hoe lang de terugverdien tijd is. Dus wanneer in kolom L de waarde positief wordt bij een bepaalde waarde van C5 (en de andere factoren in de linker kolommen). In dit jaar is zijn de onkosten dan terug verdiend.

Nu zou ik het graag anders willen hebben en namelijk willen weten bij welke waarde van C5, er na twintig jaar de waarde 0 is. Met andere woorden, bij welke waarde van C5 is L23 = 0

Nu is het lastige dat C5 zelf ook onderdeel is van de formule waaraan L23 wordt berekend. Handmatig kan ik wel deze "C5 geeft L23 = 0" waarde bepalen aan de hand van trial & error. Maar ik hoop dat hier ook een functie voor is in excel.

[ afbeelding ]

Alvast bedankt!!!

quote:

Op zaterdag 16 juni 2012 01:37 schreef coffeebiscuit1 het volgende:

[..]

Thanks snabbi, dit is inderdaad de automatische functie voor wat ik tot nu toe handmatig heb gedaan. Echter.. Middels dit rekent hij het maar 1-malig uit.

Graag zou ik voor C5 een formule ingeven, zodat deze cel altijd naar de waarde zoekt waarbij L23=0. Ook wanneer ik een van de andere factoren zou aanpassen. Ik heb dit nodig omdat ik in een andere tab naar deze cel wil verwijzen en het dus graag geautomatiseerd wil zien.

Ik heb het geprobeerd door middel van de =BET( formule, maar hier kreeg ik errors mee. Kringverwijzing ... Wat natuurlijk komt omdat de cel C5 zelf ook onderdeel is van de formule...

Via de helpfunctie ben ik er nu na een half uur helaas nog niet achter

Kortom, een in te voeren formule, voor wat middels jou manier direct wordt berekend.

quote:

Op zaterdag 16 juni 2012 11:08 schreef snabbi het volgende:
Je 'stoort' me niet hoor
Eigenlijk kan je de Macro zelf aanpassen om het zo te laten werken als je zelf wilt. Als je maar weet welke cel je parameter bevat voor een wijziging. Ik heb nu de hele C kolom gedaan behalve C5 (om te voorkomen dat ie in een loop gaat raken) maar je kan hem zo ver uitbreiden als je zelf wilt.

<> geeft hier aan ongelijk aan.

If (Target.Column = 3 And Target.Row <> 5) OR (Target.Column = 2 and Target.Row = 3) THEN
dat laatste gedeelte voegt nu ook B3 toe. Zo kan je in feite alle cellen op dit tabblad bereiken. Ook wanneer je naar andere tabbladen gaat kan het maar dan moet je de code van die betreffende sheet pakken en bijvoorbeeld zo aanpassen:
Sheets("Blad1").Range("L23").GoalSeek Goal:=0, ChangingCell:=Sheets("Blad1").Range("C5")

(let op dat er nu 2 keer de sheetnaam genoemd wordt van de plek waar deze cel zich bevindt. Je kan natuurlijk ook hebben dat je parameter op een andere sheet staat dan je resultaat, maar dat lukt je vast wel om aan te passen.)

(Uiteraard kan je ook dat hele IF er uit slopen zodat hij het bij elke cel doet )

quote:

Op zaterdag 16 juni 2012 00:48 schreef coffeebiscuit1 het volgende:
Pfoe hier kom ik echt niet uit:

Ik zou heel graag iets willen bereiken in de onderstaande sheet... Het betreft hier een netto contante waarde methode, echter ik gebruik hem een beetje andersom.

Normaal is C5 een waarde waarmee je berekent hoe lang de terugverdien tijd is. Dus wanneer in kolom L de waarde positief wordt bij een bepaalde waarde van C5 (en de andere factoren in de linker kolommen). In dit jaar is zijn de onkosten dan terug verdiend.

Nu zou ik het graag anders willen hebben en namelijk willen weten bij welke waarde van C5, er na twintig jaar de waarde 0 is. Met andere woorden, bij welke waarde van C5 is L23 = 0

Nu is het lastige dat C5 zelf ook onderdeel is van de formule waaraan L23 wordt berekend. Handmatig kan ik wel deze "C5 geeft L23 = 0" waarde bepalen aan de hand van trial & error. Maar ik hoop dat hier ook een functie voor is in excel.

[ afbeelding ]

Alvast bedankt!!!

quote:

Op zaterdag 16 juni 2012 12:50 schreef McGilles het volgende:

[..]

Ik zou snel stoppen met dat macro gedoe. Als je even de sheet opstuurt (upload) of vertelt hoe de formules zijn opgebouwd dan maak ik het dit weekend wel even in orde voor je met 1 simpele formule.

quote:

Op zaterdag 16 juni 2012 13:20 schreef snabbi het volgende:
Via een formule is het ook zeker op te lossen, zoals ik al zei het is maar één onbekende dus het moet best anders kunnen.

Voor wat betreft je macro. Hij doet het niet omdat Excel deze code niet over 2 verschillende tabbladen kan maken. Je krijgt dus in het tabblad (aan de linker kant aanklikken) genaamd financiele haalbaarheid de code:
[ code verwijderd ]

Daarnaast heb je dan nog de code in je oude tabblad zoals die eerder gemeld stond.

Macro's is wat mij betreft geen Excel maar gewoon programmeren, dus dat hoort er zeker niet bij. Alleen het doelzoeken en dergelijke is wel een mooie functie van Excel zelf.

quote:

Op woensdag 13 juni 2012 00:26 schreef qu63 het volgende:

[..]

• Wat ik wil bereiken:
Al die gekleurde blokjes halen hun data uit andere sheets en staan voor de 6 verschillende onderdelen van mijn studie. Alle onderdelen hebben een looptijd in maanden. In de praktijk komt het er op neer dat 1 dag werk = 1 maand, maar 5 weken werk is ook 1 maand.

Aangezien er per studiepunt 28 uur gewerkt moet worden kan ik terugrekenen hoe lang ik per dag, per onderdeel aan mijn studie zou moeten zitten. Op 1 september 2009 dus 2 uur en 12 minuten aan onderdeel oranje (projecten). Als ik A1 aanpas naar 2010 verandert de hele sheet, omdat de maandindeling dynamisch gedaan wordt.

Dit gebeurt dus allemaal in A2:AZ360.

Op regel 363 staan alle 31 dagen van de maand.
Vanaf 364 wordt alles weer dynamisch gevuld en wordt er in B364 (en verder) gekeken naar het totaal aantal uren en minuten dat ik die dag aan m'n studie zou moeten besteden volgens mijn planning.

De planning per week beslaat 9 rijen:

[..]

Alleen begint niet iedere maand op een maandag, vandaar de ingewikkelde zoekfunctie. Die stelt eerst vast in welke maand er gezocht moet worden, daarna gaat ie op zoek naar de totalen. En dat ziet er dus ingewikkeld uit..

• Input komt dus vanuit een andere planning, deze gegevens kloppen. Die formule ziet er zo uit (voor C4):

[..]

In B4 staat dit:

[..]

Die kijk dus welke dagnummer er bij de dag hoort.

Terug naar de vorige formule:
-Eerste is gewoon een simpele check of de dag + datum aanwezig zijn. Maandag 1 sept 2009 bestaat niet, dus die is leeg. Dinsdag 1 september wel, dus daar wordt de functie wel uitgevoerd.
-Daarna gaat ie op zoek naar de maand + jaar uit A2 in het tabblad projecten om in de 18e rij te kijken naar hoeveel punten ik die maand denk te halen voor het onderdeel projecten.
-Als ik dat getal deel door het aantal dagen in de maand en de uitkomst is 0 dan blijft de cel leeg, anders wordt dat de uitkomst van de formule.

• Welke foutmelding krijg ik:
Ik krijg dus geen foutmelding.. De formule doet wat ie moet doen, alleen krijg ik daarme dus niet de gewenste uitkomst

Ik deel nu, zoals een paar posts terug geopperd werd, door 30,5 ipv 30 en daarmee lijkt het probleem verholpen, alleen zijn er nu heel veel maanden met 31 dagen, dus daar klopt ook nog iets niet. Maar dat is voor morgen, nu eerst slapen

quote:

=INDEX(INDIRECT("$B$"&3+(AFRONDEN(($A399-$A$364)/30;0)*9)&":$AW$"&9+(AFRONDEN(($A399-$A$364)/30;0))*9);ALS(WEEKDAG($A399;3)+B$363>7;ALS(WEEKDAG($A399;3)+B$363-7>7;ALS(WEEKDAG($A399;3)+B$363-14>7;ALS(WEEKDAG($A399;3)+B$363-21>7;ALS(WEEKDAG($A399;3)+B$363-28>7;ALS(WEEKDAG($A399;3)+B$363-35>7;NB();WEEKDAG($A399;3)+B$363-35);WEEKDAG($A399;3)+B$363-28);WEEKDAG($A399;3)+B$363-21);WEEKDAG($A399;3)+B$363-14);WEEKDAG($A399;3)+B$363-7);WEEKDAG($A399;3)+B$363);ALS(WEEKDAG($A399;2)+B$363>8;ALS(WEEKDAG($A399;2)+B$363-8>8;ALS(WEEKDAG($A399;2)+B$363-16>8;ALS(WEEKDAG($A399;2)+B$363-24>8;ALS(WEEKDAG($A399;2)+B$363-32>8;NB();40);32);24);16);8))

quote:

=INDEX(INDIRECT("$B$"&3+(DATUMVERSCHIL($A$364;$A364;"m")*9)&":$AW$"&9+(DATUMVERSCHIL($A$364;$A364;"m")*9));ALS(WEEKDAG($A364;3)+B$363>7;ALS(WEEKDAG($A364;3)+B$363-7>7;ALS(WEEKDAG($A364;3)+B$363-14>7;ALS(WEEKDAG($A364;3)+B$363-21>7;ALS(WEEKDAG($A364;3)+B$363-28>7;ALS(WEEKDAG($A364;3)+B$363-35>7;NB();WEEKDAG($A364;3)+B$363-35);WEEKDAG($A364;3)+B$363-28);WEEKDAG($A364;3)+B$363-21);WEEKDAG($A364;3)+B$363-14);WEEKDAG($A364;3)+B$363-7);WEEKDAG($A364;3)+B$363);ALS(WEEKDAG($A364;2)+B$363>8;ALS(WEEKDAG($A364;2)+B$363-8>8;ALS(WEEKDAG($A364;2)+B$363-16>8;ALS(WEEKDAG($A364;2)+B$363-24>8;ALS(WEEKDAG($A364;2)+B$363-32>8;NB();40);32);24);16);8))

quote:

Op zaterdag 16 juni 2012 14:44 schreef qu63 het volgende:

_{En al die ALS()-functies die er na komen zijn om te bepalen hoeveel blokken de INDEX()-functie naar rechts moet, dat kan dus niet korter helaas..}

quote:

Op zaterdag 16 juni 2012 13:34 schreef coffeebiscuit1 het volgende:

[..]

Ik was net bezig met uploaden en begeleidend tekstje schrijven, maar gelukkig kwam snabbi net op tijd met de uitkomst. Toch bedankt voor je aanbod

[..]

Top, het werkt !

Hier zou ik zeker nóóit achter zijn gekomen zonder hulp. Dus super bedankt!

quote:

Op zaterdag 16 juni 2012 17:13 schreef McGilles het volgende:

[..]

De gemeenschappelijke factor is WEEKDAG($A364;3)+B$363
Dus als je nou via verticaal zoeken een tabel maakt met de bijbehorende waarden en als je hem daarin laat zoeken dan ben je klaar. 1 regel i.p.v. de weet ik veel hoeveel nu

quote:

Op maandag 18 juni 2012 09:29 schreef Deetch het volgende:
Mooie discussie, met macro's of met hendig sjieke excel formules. Ik heb ook wel eens tijden lopen prutsen in VBA tot een collega zei: "Maar dat kna deze functie toch?"

quote:

Datedif() was er ook zo een: http://www.cpearson.com/excel/datedif.aspx
Let op dat deze functie anders werkt dan datediff in VBA

quote:

Op maandag 18 juni 2012 12:42 schreef xyntarsus het volgende:
thx voor de hulp.

kan dit trouwens ook zonder dat ik het menu krijg (dat ie automatisch cel B2 pakt?)