Hoe laat ik excel bij het rekenen van cel tot cel naar beneden gaan?

[louisg52]

Hallo beste mensen,

Ik loop al een tijdje tegen een vervelend probleen aan.

Van een Excel werkblad worden 870 rijen gebruikt. De eerste 4 rijen dienen als titel en zijn vastgezet. Dat is handig bij het omlaag-scrollen.

Rij 5 t/m 870 bevat de data.

In de eerste kolom (standaard opmaak) staat een getal dat een weeknummer voorstelt.

Het begint met 9301 (rij 5) en eindigt met week 32 in 2009 oftewel 0932 (rij 870)

In de tweede kolom staat de inkoopprijs van een artikel en in de derde kolom de verkoopprijs

De eerste inkoop vond plaats in week 9310. De verkoop in week 9316. Dat was de eerste transactie

De laatste transactie vond plaats in week 0825 (inkoop) en 0840 (verkoop)

Voor de weken waarin geen handeling plaatsvindt, staat de kolom 2 en 3 niets ingevuld

In de vierde kolom zou ik graag de winst willen weergeven. Ik heb daarvoor het onderstaand VBA programmaatje geschreven dat als een Function (dus als formule) op elke regel van de 870 regels in het werkblad is ingevuld.

Daarvoor heb ik het eerste programmaatje (zie beneden) geschreven, maar dat werkt totaal niet.

Na lang geexperimenteer, kwam ik tot de volgende conclusie: Excel rekent andersom! D.w.z. Excel begint niet bij rij 5, maar bij 870 en rekent naar omhoog!

Daarop heb ik het tweede programmaatje (zie ook beneden). De functie werkt goed, maar…… de winst (of verlies) wordt consequent geplaatst op de regel van de inkoop i.p.v. de verkoop. Logisch, als de cellen van beneden naar boven worden doorlopen.

Hetzelfde probleem heb ik met het nummeren van de transacties. Excel plaatst nummer van de laatste transactie (122) op de rij van week 9310, en nummer 1 bij de laatste week (0825) in plaats van andersom.

Ik zou graag de getallen op de goede plaats willen hebben. Ook omdat elke week een regel wordt toegevoegd en ik graag zou willen dat alles meteen netjes klopt.

Hoe kan ik dit probleem oplossen? (ik heb al van alles geprobeerd, maar kom er niet uit)

Wie zou mij willen helpen?

Werkt niet:

Function Nwinst(ByVal BuyLevel, SellLevel) As Double

'

Static APrijs As Double

Dim VPrijs As Double



If BuyLevel > 0 Then

APrijs = BuyLevel

End If

'Exit Function

If SellLevel > 0 Then

VPrijs = SellLevel

Nwinst = VPrijs - APrijs

End If

End Function

Het volgende progr werkt wel, maar de winst staat op dezelde regel als de inkoop

i.p.v de verkoop

Function Nwinst(ByVal BuyLevel, SellLevel) As Double

'

Static VPrijs As Double

Dim APrijs As Double

If SellLevel > 0 Then

VPrijs = SellLevel

Exit Function

End If

If BuyLevel > 0 Then

APrijs = BuyLevel

Nwinst = VPrijs - APrijs

End If

End Function

[E.Desart]

:eusa_whistle: Ik heb het gevoel dat jij dingen graag gecompliceerd maakt, is het niet?

Als ik die ganse tekst juist begrijp:

Jij wil, op voorwaarde dat Aankoopprijs EN Verkoopprijs ingevuld zijn in de kolom rechts hiervan het verschil zetten (dus de winst), en in elk ander geval 0 of niets?

Wat hebben die weeknummers hiermee te maken?

En je vertelt dat die winst in een kolom staat naast aankoop en verkoop. Waar komt dan plots de zin vandaan:

Daarop heb ik het tweede programmaatje (zie ook beneden). De functie werkt goed, maar…… de winst (of verlies) wordt consequent geplaatst op de regel van de inkoop i.p.v. de verkoop. Logisch, als de cellen van beneden naar boven worden doorlopen.

Een custom funtie zal STEEDS het antwoord plaatsen in de cel waar deze geënterd is. Als blijkt (of de indruk geeft) dat hier een verschuiving gebeurde is dat omdat jij die functie niet juist maakte.

Een paar werkende dingen:

--------------------------------------------------------------------------------------------

Function Nwinst(BuyLevel, SellLevel) = jouw functie wat aangepast (die kon niet juist werken)

'

If SellLevel > 0 Then

VPrijs = SellLevel

End If

If BuyLevel > 0 Then

APrijs = BuyLevel

Nwinst = VPrijs - APrijs

End If

End Function

--------------------------------------------------------------------------------------------

Function Nwinst1(BuyLevel, SellLevel)

Nwinst1 = ""

If BuyLevel > 0 Then

If SellLevel > 0 Then Nwinst1 = SellLevel - BuyLevel

End If

End Function

--------------------------------------------------------------------------------------------

Function Nwinst2(BuyLevel, SellLevel)

Nwinst2 = ""

If BuyLevel * SellLevel <> 0 Then Nwinst2 = SellLevel - BuyLevel

End Function

--------------------------------------------------------------------------------------------

Als jij liever 0 (nul) ziet verschijnen dan een ogenschijnlijk lege cel verander Nwinst = "" door Nwinst = 0 of wat ook.

Maar je kan dit alles ook vervangen door een gewone standaard formule op het werkblad (vertaal zelf naar Nederlandse versie indien nodig):

=IF(AND($C8>0,$D8>0),$D8-$C8,"")

of

=IF($C8*$D8<>0,$D8-$C8,"")

Waarbij $C8 = aankoop en $D8 = verkoop; ook hier kan je "" vervangen door het getal 0 of wat ook.

Mis ik eigenlijk iets in jouw uitgebreide tekst waardoor ik het probleem fout zie?

PS: vraag je niet af hoe Excel rekent: dat is ECHT specialistenwerk. En Microsoft optimaliseert dit regelmatig bij nieuwe versies (Excel analyseert elk model en maakt hier een interne rekenstructuur van)

[E.Desart]

PS: vraag je niet af hoe Excel rekent: dat is ECHT specialistenwerk. En Microsoft optimaliseert dit regelmatig bij nieuwe versies (Excel analyseert elk model en maakt hier een interne rekenstructuur van)

Deze kerel is top specialist ter zake:

http://www.decisionmodels.com/index.htm

Die verkoopt ook software om Excel spreadsheets te analyseren in functie van efficiëntie en rekensnelheid. Dit heeft echter alleen of hoofdzakelijk zin voor mensen die zware rekenintensieve spreadsheets maken

Tussen haakjes: je kan een functie dwingen op bij ELKE recalculatie van wat ook op je werkblad opnieuw te berekenen.

Zoek hiervoor "VOLATILE" in je Helpfile.

Dit moet je echter trachten te mijden als de pest. Normaal herrekent een formule alleen als een van zijn argumenten wijzigt (direct of indirect).

[jhnbk]

Tussen haakjes: je kan een functie dwingen op bij ELKE recalculatie van wat ook op je werkblad opnieuw te berekenen.

Zoek hiervoor "VOLATILE" in je Helpfile.

Dit moet je echter trachten te mijden als de pest. Normaal herrekent een formule alleen als een van zijn argumenten wijzigt (direct of indirect).

Zie hier: http://msdn.microsoft.com/en-us/library/aa...fice.11%29.aspx

Uiteraard beter niet doen zoals E.Desart al aangeeft.

Ik denk dat je zelfs geen vba nodig hebt voor jouw probleem. Wat "als" structuren en deze doorvoeren over heel je tabel zou moeten werken. (Je omschrijft het probleem wel zeer vaag)

[louisg52]

Allereerst dank voor de genomen moeite. Dat de beschrijving wat uitgebreid lijkt, is omdat ik zo volledig mogelijk wilde zijn. Dat heeft het er niet duidelijker op gemaakt.

De weeknummers van de eerste kolom hebben er inderdaad niets mee te maken. Met die medeling wilde ik alleen maar uitleggen waarom de inkoop en verkoopprijs niet in dezelfde rij staan en dat wekelijks een nieuwe regel wordt toegevoegd.

De genoemde oplossingen werken helaas niet. Laat ik daarom het probleem opnieuw beschrijven en mij beperken tot de kern:

Bij het bepalen van het verschil van twee getallen die niet in dezelfde rij staan, plaatst Excel consequent het verschil bij het verkeerde getal

Het aantal lege rijen tussen de getallen varieert.

In principe ben ik niet geinteresseerd hoe Excel rekent. Maar omdat de resulterende winst (of verlies) telkens op dezelfde rij als de inkoopsprijs wordt geplaatst, kreeg ik de indruk dat Excel de cellen van de kolom doorloopt van beneden naar boven.

Hoe kan ik het resultaat krijgen daar waar het hoort, namelijk naast de verkoopprijs?

mvg.

[E.Desart]

Bij het bepalen van het verschil van twee getallen die niet in dezelfde rij staan, plaatst Excel consequent het verschil bij het verkeerde getal

Het aantal lege rijen tussen de getallen varieert.

Hoe kan ik het resultaat krijgen daar waar het hoort, namelijk naast de verkoopprijs?

Louis, Ik denk dat je beter eens een voorbeeld file (met slechts enkele typische lijnen, dus verkort gestileerd model) post.

Het is zéér simpel en dit geldt zowel voor standaard werkblad functies (formules) als 'custom made' functies.

Dergelijke functies (beide types) zijn gewoon niet in staat om iets in een andere cel te zetten. Dus die zetten dit berekend verschil consequent in de cel waar de formule staat. Dit betekent dat jij je argumenten niet juist selecteert.

Dus duidelijk: Ook een custom made functie kan NIET zoals een andere macro (Command Macro) ergens in andere cellen wat schrijven maar werkt gewoon alsof je standaard Excel werkblad formules gebruikt. En ik heb nog nooit een formule zijn resultaat in een andere cel weten geven (=magie) dan degene in dewelke de formule staat.

Of ik begrijp er totaal niets van.

[E.Desart]

Ik begin hier zowat de indruk te krijgen dat jij een veel uitgebreider probleem hebt.

Met name dat die verkoopprijs ergens in een willekeurige week kan staan (of nergens) en dat die functie dan moet gaan zoeken waar die aankoopprijs staat.

Als dat zo is heb je wel criteria nodig om te weten wat, wat is.

1e conditie: verkoopprijs > 0

2e actie: welke aankoopprijs hoort hier dan bij?

3e actie: code voor zoeken op basis van bepaalde criteria en berekening.

Als ik hier in de juiste richting denk zou ik zeggen: zonder model met duidelijke uitleg blijft dit gokken op afstand.

En wat jij met jouw zelfgemaakte functie wou bereiken is me dan helemaal niet duidelijk.

Het blijft nog steeds mogelijk dat dit zonder VBA kan ook.

[louisg52]

Ik zal proberen de voorbeeldfile te maken. Ik hoop dat het goed overkomt.

De kolommen zijn weeknummer, tradenummer, aankoop, verkoop, opbrengst(+/-)

0929 12 17,31 1,01

0930

0931

0932 18,32

0933

0934 13 18,01 -0,50

0935 17,51

0936 14 17,63 0,97

0937

0938 18,60

0939

In de vijfde kolom staat de functie Nwinst(RK[-2],RK[-1]), die ik met mijn eerste bericht heb meegestuurd

De blanco vlakken tussen de getallen zijn nulwaarden, die zijn onderdrukt.

Het geheel is een onderdeel van een aandelen programma, waarin de aan- en verkoop van één bepaald fonds wordt bijgehouden.

Ik heb veel uitgeprobeerd, maar dan werd het verschil tussen vorige trade en de aankoop van de volgende berekend.

mvg.

Louis

Ik zie dat de getallen even in kolommen gesorteerd moeten worden. Het mailprogramma schuift alles tegenelkaar. Het voorbeeld gaat om tradenummer 12, 13 en 14.

[E.Desart]

Ik zie dat de getallen even in kolommen gesorteerd moeten worden. Het mailprogramma schuift alles tegenelkaar. Het voorbeeld gaat om tradenummer 12, 13 en 14.

Staat verkoop steeds in de volgende GEBRUIKTE rij (dus direct na aankoop) of is hier overlap mogelijk?

Tabel 1: Dit is wat jij krijgt

Tabel 2: Dit is wat jij wil

Tabel 3: Kan deze situatie zich ook voordoen? Transactie 13 wordt verkocht in de 37ste week i.p.v. 35ste week.

Louis01 1704 keer bekeken

Wat bedoel jij met "onderdrukt" in functie van de lege cellen en rijen? Staan hier niet-zichtbare formules of gegevens in?

PS: als jij nog dergelijke dingen entert gelieve ofwel een figuur te gebruiken ofwel de tabellen macro die hier ergens te downloaden is. Zo laat je anderen puzzelen.

[E.Desart]

Ik wacht op antwoord voor Tabel 3.

Als deze situatie zich zou kunnen voordoen bestaat er geen oplossing noch via werkblad formules noch VBA, omdat je gewoon geen parameters of criteria hebt om verkoopprijs aan aankoopprijs te koppelen.

Dwz dat je hier op de verkoopslijn je tradenummer ergens moet zetten (in zelfde kolom of eigen toe te voegen kolom) zodat ergens een link kan gelegd worden tussen aankoop en verkoop.

Indien de situatie werkt als in tabel 1 (jouw huidige situatie en tabel) en 2 (jouw doelstelling) kan je volgende werkblad formule gebruiken. Ik heb hier een speciale toepassing van de MATCH functie voor gebruikt:

Je moet steeds starten van rij 1 in de formule (ongeacht wat er boven je tabel staat)

Voer deze formule in in cel F2 (volgens mijn figuur) en kopieer die gewoon naar beneden.

=IF(ISNUMBER(D2),IF(D2>0,D2-INDEX(C$1:C2,MATCH(1E+30,C$1:C2)),""),"")

Deze formule zoekt automatisch de laatste aankoopprijs die bij de verkoopprijs hoort (en wordt niet gehinderd door cel inhoud die geen getal is, zelfs tekst of leeg of .....)

Voorbeeld (noteer dat die formule op elke lijn staat en alleen visueel iets laat zien waar nodig)

Louis02 1701 keer bekeken

[louisg52]

Tabel 1 is inderdaad wat ik krijg en tabel 2 is precies wat ik wil...!

Overlap komt niet voor, dus tabel 3 vervalt.

De tradenummers heb ik er bij gezet voor de duidelijkheid, maar in de echte file komen ze niet (meer) voor.

Er is kennelijk wél een link tussen aan- en verkoop, want de verschillen worden perfect berekend. Ze staan alleen consequent op de verkeerde plaats, waardoor ik ben gaan denken dat Excel van beneden naar boven rekent, want anders zou ik het niet kunnen verklaren.

Wat ik bedoel met onderdrukken van nulwaarden: via het menu Extra/ Opties heb ik op het tabblad 'weergave' het vinkje weggehaald bij nulwaarden. Het maakt de tabel wat overzichtelijker en heeft geen enkele invloed op het VBA.

In de cellen zit geen verborgen informatie.

mvg.

[E.Desart]

Louis,

Ik ga niet meer discussiëren over wat er gebeurt met je zelf gemaakte functie. Het resultaat zag je zelf en is de reden dat je hulp vroeg.

Je zag dat ik je de oplossing gaf in vorig bericht (voorwaarde dat Tabel 3 niet van toepassing kan zijn, wat je nu bevestigd.)?

=IF(ISNUMBER(D2),IF(D2>0,D2-INDEX(C$1:C2,MATCH(1E+30,C$1:C2)),""),"")

De Nederlandse versie van dezelfde formule:

=ALS(ISGETAL(D2);ALS(D2>0;D2-INDEX(C$1:C2;VERGELIJKEN(1E+30;C$1:C2));"");"")

Als jij hier 0 wil behouden i.p.v. "" (dit is een ogenschijnlijk lege tekst cel) worden de formules:

=IF(ISNUMBER(D2),IF(D2>0,D2-INDEX(C$1:C2,MATCH(1E+30,C$1:C2)),0),0)

De Nederlandse versie van dezelfde formule:

=ALS(ISGETAL(D2);ALS(D2>0;D2-INDEX(C$1:C2;VERGELIJKEN(1E+30;C$1:C2));0);0)

Is het nu opgelost?

[louisg52]

Eric,

De formules werken in het werkblad dat ik even als test heb gemaakt.

Het werkblad waarin ik ze gebruik echter, is van het verwijzingstype R1K1. Nu stuit ik bij het omzetten op C$1. Hoe vertaal ik dat? (ik weet de betekenis van het zo belangrijke $-teken niet goed)

Zou de formule nog willen omzetten naar 'R1K1' of aangeven hoe ik C$1 moet omzetten?

(ik gebruik Excel 2003NL)

Dan zou ik je willen danken voor alle tijd en moeite die je mijn probleem hebt besteed.

Met vriendelijke groet,

Louis

[E.Desart]

De formules werken in het werkblad dat ik even als test heb gemaakt.

:eusa_whistle: Eens dat ik weet wat ik moet doen of exact verwacht wordt geef ik alleen maar werkende dingen.

Het is gemakkelijker dat je de instelling A1 gebruikt dan R1K1 (aanpassen via Menu >Extra > Opties > Algemeen)

Ik werk met een Engelstalige: dus sommige woorden van mij zijn gewoon uit geheugen.

Maar hoe dan ook:

Engels

=IF(ISNUMBER(RC[-2]),IF(RC[-2]>0,RC[-2]-INDEX(R1C[-3]:RC[-3],MATCH(1E+30,R1C[-3]:RC[-3])),""),"")

Nederlands

=ALS(ISGETAL(RK[-2]);ALS(RK[-2]>0;RK[-2]-INDEX(R1K[-3]:RK[-3];VERGELIJKEN(1E+30;R1K[-3]:RK[-3]));"");"")

Dit geld dus als je hem in cel F2 zet (mijn figuur). Sorry ik ben niet gewend aan dit verwijzingstype voor normale werkbladen (je verliest er alle gevoel bij).

Je kan je pagina ook eerst op A1 verwijzingstype zetten. Originele formule kopiëren en dan terug zetten (maar A1 is véééél meer common voor gewone spreadsheets).

[louisg52]

Bedankt, Eric