Wellicht klinkt deze titel wat vaag, maar ik wist niets beters op het moment, haha.
Ik zal proberen uit te leggen wat ik graag wil. Via mijn verzekering (Ditzo) heb ik een Apple Watch kunnen aanschaffen. Deze kan ik in 2 jaar tijd terug verdienen door sportieve doelen te behalen.
Elke maand kun je 1/24e deel cashback verdienen door 3500 punten te verzamelen. Nu wil ik graag een sheet maken zodat ik in 1 oog opslag kan zien hoeveel punten ik nog moet per dag, zie afbeelding:
Nu krijg je voor diverse activiteiten verschillende punten.
Nu zou ik graag onderin mijn sheet willen weergeven hoevaak ik in deze maand nog 200, 100, en 50 punten moet halen om mijn doel te behalen deze maand. In de huidige maand zitten nog 21 dagen en ik moet nog 2300 punten halen. In de sheet zou dan onderin moeten staan:
Aantal dagen nog 200 punten halen: 2
Aantal dagen nog 100 punten halen: 19
Aantal dagen nog 50 punten halen: 0
Ik heb echter geen idee hoe ik hieraan moet beginnen… Heeft iemand een idee?
Je zou me er erg mee helpen, nu zit ik steeds handmatig te rekenen of ik op schema lig om mn doel te halen
Deel het aantal resterende punten door 100: dat is het aantal dagen dat je nodig hebt om het met 100 punten per dag te doen.
Is dit groter dan het aantal resterende dagen in de maand, neem dan het verschil: dit is het aantal dagen waarop je 200 punten moet halen. Op alle overige dagen moet je 100 punten halen.
Verder nog: is (punten ÷ 200) meer dan het aantal resterende dagen in de maand, dan kun je het van de maand niet meer halen.
Voorbeeld 1: je moet nog 1400 punten halen en er zijn nog 10 dagen. 1400 ÷ 100 = 14, en 14 > 10. Dus doe je 14 − 10 = 4 om te berekenen dat je vier dagen 200 punten moet halen. Resteert 10 − 4 = 6 dagen van 100 punten.
4 × 200 + 6 × 100 = 800 + 600 = 1400.
Voorbeeld 2: je moet nog 1400 punten halen en er zijn nog 6 dagen. 1400 ÷ 200 = 7, en dat is meer dan 6, dus deze maand kom je er niet.
Dat is de échte kunst van het programmeren (en dus ook van het gebruiken van een spreadsheet): bedenken hoe de berekening ook echt uitgevoerd moet worden
Aangenomen dat de cel „Dagen nog te gaan na vandaag” B5 is en „Punten die nog behaald moeten worden” B11, dan …
Zet in de cel achter „Aantal dagen nog 200 punten halen” (d.w.z. in cel B13): ALS(B11÷100>B5;B11÷100−B5;0)
Zet in de cel achter „Aantal dagen nog 100 punten halen” (cel B14): ALS(B13>0;B5−B13;B11÷100)
Slim is ook om cel B5 voorwaardelijke markering te geven: als hij kleiner is dan B13 maak je hem bijvoorbeeld rood, omdat het dan niet meer mogelijk is om de benodigde punten te halen.
Ja, dat is zeer zeker een kunst! Ik ben redelijk op weg met het programma maar er valt nog veel te leren. Misschien toch maar eens een cursus volgen voor de wat uitgebreidere formules.
Misschien heb je daarvoor tips?
Heb je formules overgenomen (ik probeer ze ook te begrijpen, dat lukt deels), maar helaas geeft hij nu een fout, zie afbeelding:
De fout bij 200 punten weet ik hoe dat komt, omdat “dagen nog te gaan” in dagen weergegeven wordt. Wanneer ik daar handmatig het getal 19 invoer dan gaat die wel goed. Ik krijg het echter niet voor elkaar dat de formule hem begrijpt.
Als ik B5 op gegevensnotatie “getal” zet verandert het niet helaas.
Ook B19 geeft een fout aan, weet niet wat daar fout gaat.
Kan ik je de sheet eventueel mailen dat je mee kunt kijken?
Wat staat er precies in cel B5? Als dat iets is als DATUMVERSCHIL(B2;B3;"D") dan zou het gewoon moeten werken — bij mij in elk geval wel.
(Heel rij 4 is niet nodig in je tabel, trouwens: je kunt al aan de einddatum van de maand zien hoeveel dagen er in de maand zitten )
ALS(B11÷100>B5;B11÷100−B5;0)
De formule ALS heeft twee of drie delen. Het eerste deel is een vergelijking die waar of niet waar is: in dit geval B11÷100>B5 — d.w.z. „is de waarde in B11, gedeeld door 100, groter dan (>) de waarde in B5?”
Als de uitkomst van de vergelijking waar is, gebruikt Numbers het tweede deel van de formule; anders (bij niet waar) het derde deel, als dat er is. Wat hier dus zal gebeuren, is dat Numbers de waarde van B11 ophaalt en deelt door 100; daarna kijkt hij of de uitkomst van de deling groter is dan de waarde in B5. Is dat zo, dan voert hij het tweede deel van de formule uit: B11÷100−B5 — d.w.z. „Deel de waarde in B11 door 100 en trek er dan de waarde in B5 vanaf”, en zet die uitkomst in de cel. Is daarentegen de uitkomst van de deling níét groter dan de waarde in B5, dan zet hij eenvoudigweg 0 in de cel.
Daar zou in dit voorbeeld 3 moeten komen te staan. Immers zijn er nog 13 dagen en moeten er 2300 punten behaald worden. 10x200 punten = 2000. Blijven er 300 punten en 3 dagen over = 100 punten per dag.
Ik ben zelf even aan het prutsen geweest en heb B15 met de volgende som aan het werk gekregen: (B11−(B14×200))÷100. Nu weet ik niet of dit de juiste som is en of het netter kan maar het werkt redelijk:
Het enige wat nu nog eigenlijk fout is, is dat er 11,5 en 1,5 als resultaat wordt gegeven. Ik snap waardoor dit komt omdat het behaald aantal punten nu 2050 is, maar weet niet hoe dit op te lossen.
Eigenlijk moeten deze waarden bij 200 punten naar onderen worden afgerond en bij 100 dagen naar boven. Een halve dag punten halen kan immers niet.
Som B14 (200 punten): AFRONDEN.BOVEN(ALS(B11÷100>B5;(B11÷100−B5);0);1)
Som B15 (100 punten): AFRONDEN.BOVEN(ALS((B11−(B14×200))÷100<0;0;(B11−(B14×200))÷100);1)
Mocht je nog aanvullende tips hebben dan hoor ik het graag maar volgens mij is het gelukt !!!
Volgens mij ben je er aardig uitgekomen. Inderdaad moet je afronden als het aantal punten geen veelvoud van 100 is, maar omdat je zo te zien niet van plan was om 50 punten op een dag te halen, had ik dat deel weggelaten. Wil je die mogelijkheid er ook bij hebben dan wordt het best wat ingewikkelder, namelijk. De eenvoudigste oplossing lijkt mij ook gewoon afronden zoals je gedaan hebt.
Dank voor je reactie. Wat je zegt klopt! Ik was in 1e instantie niet van plan om de 50 punten mee te nemen in de sheet, omdat ik doorgaans die niet haal. Maar het kan wel gehaald worden (50 punten) en dat was gisteren het geval toen ik aan het testen was (behaald 2050). Toen kwam ik erachter dat de sheet een verkeerde uitslag gaf en zocht daar een oplossing voor.
Je kunt de volgende puntenaantallen verdienen: 50, 100, 150, 200 & 250 maar had alleen 100 en 200 in de sheet meegenomen omdat ik eigenlijk alleen die scores haal. Maar de sheet moet er wel mee kunnen werken als het op 50 is afgerond, dat is nu met het afronden bereikt.
Er zijn natuurlijk heel veel combinaties te maken aan het begin van een nieuwe maand om de benodigde 3500 punten te halen maar het gaat er maar om dat ik makkelijk kan zien of het nog te halen is in de huidige maand en hoeveel dagen ik daar 100 of 200 punten voor moet binnenslepen, en dat is nu precies gelukt!
Als je die er allemaal in wilt verwerken, dan is dat natuurlijk te doen, maar ingewikkeld zal het dan zeker worden Dat is natuurlijk niet nodig als je ze toch niet verwacht te gebruiken — en als je dat later toch wel gaat doen, kun je altijd je spreadsheet verder uitbreiden om ze ook mee te nemen in de berekening.
