Bug in Excel of afrondingsprobleem?

[Black Tiger]

Ik heb de volgende formule in Excel staan op rij 2 en die trek ik dan door tot rij 80 ofzo. Het gaat om getallen, celeigenschappen staan ook op getal met maximaal 2 decimalen. Daardoor zou je denken dat de afronding automatisch gebeurt op 2 decimalen, heeft altijd zo gewerkt.

=ALS(B3=0;"";C2-B3)&ALS(D3=0;"";C2+D3)

Dat werkt verder prima, zolang je boven de 10 zit. Maar nu heb ik een getal 178,61.
Trek ik daar 168 van af, kom ik uit op 10,61 dus boven de 10, geen probleem.
Gaat het getal 169 worden krijg je dit als uitkomst:
9,61000000000001

Dit is in Exel 2003. Om een of andere reden wordt er dus onder de 10 opeens afgerond op een getal tot een x aantal plaatsen achter de komma. Terwijl er nog niet eens sprake is van een afronding. Want het zou gewoon 9,61 moeten worden.

Het lijkt mij eerder een bug. Is hier iemand iets van bekend of weet iemand hoe dit in orde te maken is zodat het gewoon 9,61 wordt?

 

[the matrix]

ik snap er geen fuck van wat je bedoelt

 

[eendenteam]

We hebben kunnen reproduceren wat je bedoelt.
Hier zelfde uitkomst in Excel 2010. Vreemd...

Je kunt e.e.a. oplossen door deze formule te gebruiken:

=AFRONDEN(ALS(B3=0;"";C2-B3)&ALS(D3=0;"";C2+D3);2)

of in het engels uit het hoofd: afronden vervangen door round Dat wordt dan dus iets als: =ROUND(ALS(B3=0;"";C2-B3)&ALS(D3=0;"";C2+D3);2)

syntaxis is: =afronden(getal;aantal decimalen)

Voordeel van deze formule is dat er echt wordt afgerond en niet een weergave van een cel (celeigenschap) wordt aangepast.

Suk6

 

[Black Tiger]

Bedankt Eendenteam, daar was ik naar op zoek.
Feitelijk zou hij het zelf moeten doen aangezien er een beperking is van 2 plaatsen achter de komma want dat is de instelling van de cel.

De code =AFRONDEN was me bekend, maar ik had geen flauw idee waar ik die dan moest plaatsen, met deze code.

Nu doet komt echter wel het probleem terug dat als ik in de B rij nog geen getal heb staan, dat er dan in de C rij de vermelding #WAARDE komt te staan.
Is er nog een manier om dat gewoon als leeg veld te laten zien net als met de originele code?

 

[Black Tiger]

Ik heb zojuist van Obelix71 van het CID forum de oplossing gekregen die ook het #waarde probleem omzeilt.
Code is bijna gelijk aan die van jullie Eendenteam, beetje opschuiven van het AFRONDEN statement.

=ALS(B3=0;"";AFRONDEN(C2-B3;2))&ALS(D3=0;"";C2+D3)

Daarbij wordt alelen dat c2-b3 afgerond maar da's verder prima, het zorgt er in elk geval voor dat de vakken eronder die #WAARDE niet meer laten zien.

Hartstikke bedankt voor het meedenken.

 

[eendenteam]

Nu doet komt echter wel het probleem terug dat als ik in de B rij nog geen getal heb staan, dat er dan in de C rij de vermelding #WAARDE komt te staan.

=ALS(b3="";"'';ALS(B3=0;"";AFRONDEN(C2-B3;2))&ALS(D3=0;"";C2+D3))

M.a.w. een extra ALS-statement er voor gezet: als cel B3 leeg is dan leeg laten en anders (dan is B3 dus niet leeg) de formule die er eerst stond.

 

[eendenteam]

=ALS(B3=0;"";AFRONDEN(C2-B3;2))&ALS(D3=0;"";C2+D3)

Daarbij wordt alelen dat c2-b3 afgerond maar da's verder prima

Tja dan is het te hopen dat de formule C2+D3 geen problemen oplevert. Daarom hadden wij het AFRONDEN ook er helemaal voor gezet want dan neemt die C2+D3 ook mee met goed afronden. Wij hadden verder geen informatie van jou want als C2+D3 nooit problemen oplevert dan hadden wij het AFRONDEN ook gezet waar die nu staat.

 

[Black Tiger]

Wij hadden verder geen informatie van jou want als C2+D3 nooit problemen oplevert dan hadden wij het AFRONDEN ook gezet waar die nu staat.

Daar hebben jullie gelijk in, die informatie had ik inderdaad niet verstrekt en dan zou ik zelf in eerste instantie ook uitgegaan zijn van het feit dat het voor de hele formule zou moeten. Foutje mijnerzijds.

Evt. is de afronden truuk ook nog gebruiken voor die C2+D3 zoals nu, dan blijft toch dat #Waarde weg.
Vermoedelijk krijg je dat als je het ALS B=0 statement in het afronden meeneemt, dus de hele formule in de afronding zet.

Om die waardevermelding te voorkomen zou het dan ook nog zo kunnen lijkt me:

=ALS(B3=0;"";AFRONDEN(C2-B3;2))&ALS(D3=0;"";AFRONDEN(C2+D3;2))

 

[eendenteam]

dat kan maar dan wordt de formule met AFRONDEN twee keer gebruikt.
Je kunt dan net zo goed het vooraan zetten:

=AFRONDEN(ALS(B3=0;"";C2-B3)&ALS(D3=0;"";C2+D3))

Overigens klopt het dat indien de waarde in cel D3 g??n 0 (nul) is, dat er dan altijd de uitkomst van C2+D3 wordt ingevuld dus ongeacht wat er in cel B3 staat?

 

[Black Tiger]

Je kunt dan net zo goed het vooraan zetten:

Kan ook, maar zoals in m'n eerdere reply stond aangegeven, zit je dan weer met het probleem dat je in lege velden overal de tekst #WAARDE krijgt te staan en da's niet mooi.

Overigens klopt het dat indien de waarde in cel D3 0 (nul) is, dat er dan altijd de uitkomst van C2+D3 wordt ingevuld dus ongeacht wat er in cel B3 staat?

Wat er met C2 en D3 gebeurt heeft niets te maken met wat er in B3 staat.
C2 is de cel die beide formules met elkaar combineert, niet B3.

Wat er in deze formule gebeurt is het volgende:

ALS(B3=0;"";C2-B3

Als B3=0 dan gebeurt er niets (dubbele aanhalingstekens), anders doe C2-B3.
Zelfde geldt voor de tweede code.

ALS(D3=0;"";C2+D3)

Als D3=0 dan gebeurt niets, anders doe C2+D3

Oftewel.... als D3=0 wordt er zelfs nooit de uitkomst van C2+D3 ingevuld.

Het is wel zo dat niet tegelijkertijd iets ingevuld kan worden in Cel B3 en Cel D3, maar dat is aldus ook de bedoeling. Kan ook in een enkele formule en met kleurtjes, dat weet ik.
Maar wat af gaat en bij komt in aparte kolommen ziet er overzichtelijker uit, daarnaast hoef ik dan geen + of - ervoor te zetten om aan te geven of het erbij komt of eraf.

 

[eendenteam]

Oops foutje van mij. Zal de posting hiervoor aanpassen. Mijn laatste zin moet dus zijn:

Overigens klopt het dat indien de waarde in cel D3 g??n 0 (nul) is, dat er dan altijd de uitkomst van C2+D3 wordt ingevuld dus ongeacht wat er in cel B3 staat?

Het is wel zo dat niet tegelijkertijd iets ingevuld kan worden in Cel B3 en Cel D3, maar dat is aldus ook de bedoeling. Kan ook in een enkele formule en met kleurtjes, dat weet ik.

Ja dat kan wel maar heeft geen zin in de formule die jij gebruikt, want daarom ook onze vraag dat indien D3 geen nul is er altijd C2+D3 wordt ingevuld en dan heeft wat er in B3 staat, geen enkele zin/invloed.

 

[Black Tiger]

want daarom ook onze vraag dat indien D3 geen nul is er altijd C2+D3 wordt ingevuld en dan heeft wat er in B3 staat, geen enkele zin/invloed.

Correct. Als je D3 invult (dus geen nul) wordt er altijd C2+D3 gedaan, echter dan mag er zelfs op B3 zelfs niks ingevuld worden, zag ik net toen ik dat even ging testen.
Als ik op zowel D3 alsook B3 iets invul, dan krijg ik een vaag resultaat wat zelfs voorzien is van 2 komma's. Dus da's ook niet goed.

Het is een soort van inkomsten/uitgaven dingens. Op de B rij komen de uitgaven, op de D rij komen de inkomsten.
Heb je zowel inkomsten alsook uitgaven, moet er dus voor elk apart een regel ingevuld worden.

 

[eendenteam]

Klopt, maar wat wil je nu met D3 als er in B3 ook iets is ingevuld (beide geen nul uiteraard)?

M.a.w. we snappen niet wat je nu met het & (EN) teken wilt. Wellicht moet je in de formule het "EN"-statement gebruiken of wil je de uitkomsten van de twee (C2-B3 en C2+D3) echt samenvoegen in ??n cel....? Dus als bijvoorbeeld C2-B3 als uitkomst 8 heeft en C2+D3 heeft als uitkomst 12 dan wordt als gevolg van jouw formule in die cel ingevuld: 812 (samenvoegen van 8 en 12 als gevolg van het &-teken in de formule)

 

[Black Tiger]

M.a.w. we snappen niet wat je nu met het & (EN) teken wilt.

Ahzo. Volgens mij is het "EN" statement hetzelfde als het gebruiken van het & teken of AND.
Net zoals ik voor ALS ook IF had kunnen gebruiken. Deze formule wordt over meerdere regels doorgetrokken, dus op een gegeven moment werk je dan ook met B31 en D31 etc.

De bedoeling hiervan is dat ik op 1 en dezelfde regel gewoon ofwel in B3 ofwel in D3 een bedrag wil kunnen intypen, waardoor dit van het totaalbedrag in C2 ofwel wordt afgetrokken, ofwel erbij wordt opgeteld.
Dus stel in C2 staat 50
Nu geef ik 5 euro uit. Dan moet ik in B3 het getal 5 intypen en dan komt in C3 automatisch 45 te staan.
Zou het echter zo zijn dat ik 5 euro gekregen heb, dan geef ik dat getal 5 in D3 in en komt dus in C3 automatisch 55 te staan.

Dat is de bedoeling. Omdat het een combinatie is die een bewerking teweeg moet brengen in dezelfde rij (C rij) heb ik het & statement gebruikt.
Het wekte misschien wat verwarring omdat ik zowel Engelse alsook Nederlandstalige statements door elkaar gebruikt heb, in Excel kan dat. En bij gebruik van formules ben ik wat minder kieskeuring v.w.b. het gebruik van 1 taal.

 

[eendenteam]

Jij gebruikt in de formule het & teken en dan worden dingen samengevoegd
dat is iets anders dan in de formule het woord EN te gebruiken.
EN in een formule is een voorwaarde.

Ik zal later (of morgenochtend) even kijken wat je wilt en in een formule proppen (ben nu TV aan het kijken)

 

[eendenteam]

Dan heb je dat gedoe met "=als(b3=0)" en zo helemaal niet nodig.
Overigens je kunt geld uitgeven en ook tegelijk inkomsten hebben toch... M.a.w. in theorie zou er waarden kunnen staan in B3 en dus tegelijk ook in D3.

Wat dacht je hiervan:

=ALS(EN(B3>03>0);AFRONDEN(C2+D3-B3;2);ALS(B3>0;AFRONDEN(C2-B3;2);ALS(D3>0;AFRONDEN(C2+D3;2);"")))

3 mogelijkheden:

- B3 ?n D3 zijn beide groter dan 0 in de formule is dat: "EN(B3>03>0);". Als dat niet zo is, dan is ?f B3 groter dan 0 ?f D3 is groter dan 0 en dus is:
- B3 is groter dan 0 (maar D3 dus niet) in de formule is dat: "ALS(B3>0;". of:
- D3 is groter dan 0 (maar B3 dus niet) in de formule is dat: "ALS(D3>0;".

als B3 en ook D3 groter zijn dan 0 (dus er is een waarde ingevuld in B3 maar ook in D3) dan komt in C3 de uitkomst van: C2+D3-B3 (inclusief afronden),
als dat niet zo is, dus B3 ?n D3 zijn niet beide nul (0) dan (is ?f B3 groter dan 0, ?f D3 is groter dan 0 en dus):

- als B3 groter is dan 0 dan komt in C3 de uitkomst van: C2-B3 (inclusief afronden)

- en als B3 niet groter is dan 0 (maar dan is dus D3 groter dan 0) dan komt in C3 de uitkomst van: C2+D3 (inclusief afronden)

Aan het einde van de formule zie je dubbele aanhalingstekens staan. Dat komt omdat we in de formule aan het einde wederom een ALS-statement hebben gebruikt namelijk: "ALS(D3>0;"
Dat is feitelijk niet nodig omdat er helemaal geen andere mogelijkheid is dan dat D3 groter is dan 0 en dus zou je ook onderstaande formule kunnen gebruiken welke, - wetenschappelijk gezien - mathematisch beter is:

=ALS(EN(B3>03>0);AFRONDEN(C2+D3-B3;2);ALS(B3>0;AFRONDEN(C2-B3;2);AFRONDEN(C2+D3;2)))

 

[Black Tiger]

Ik begrijp nu wat je bedoeld, dat & is eigenlijk niet op zijn plaats, maar ik kreeg het op andere wijze niet werkend.

De formule die jij nu gegeven hebt werkt in elk geval ook perfekt en nu klopt het wel als ik op dezelfde regel op B3 en D3 iets ga invullen, hetgeen voorheen niet kon. Dat kwam dus door mijn foute & statement.
Alleen kreeg ik het op andere manier met de formule zoals ik hem had samengesteld niet aan de praat.

Thanks!!

 

[eendenteam]

Het & teken in een formule wordt gebruikt om inhouden e.d. samen te voegen, dat kan met getallen maar ook met cellen die tekst bevatten.

Stel in B3 staat het getal 5 en in D3 het getal 12
Als je die twee cellen gaat samenvoegen door het & teken te gebruiken dan krijg je in die cel de waarde 512 (zijnde letterlijk 5 en 12 samengevoegd).
Dus (in bijvoorbeeld C2) de formule +B3&D3 levert dan op: 512

Zo kun je dat ook doen met tekst.
Stel in B3 staat: BlackTiger
en in D3 staat: snapt het

Dan kun je in C2 opgeven: +B3&D3
en zul je zien dat in C2 als waarde staat: BlackTigersnapt het
Dan wil je natuurlijk nog tussen de woorden "BlackTiger" en "snapt" een spatie hebben.
Dan moet je de formule in C2 wijzigen in: +B3&" "&D3
Twee keer het & teken dus om twee keer samen te voegen en wel 1 keer een spatie (" ")

 

[Black Tiger]

Yep, heb hem weer helemaal door, bedankt voor de uitleg.

V.w.b. het afrondingsprobleem, schijnt inderdaad in alle versies voor te komen, althans in elk geval 2003, 2007 en 2010, vaagheid dus. Maarja met deze truuk weer goed werkend.
Thanks!

 

[bert50]

Nu wil ik mij hier eigenlijk niet mee bemoeien, maar hadden we vroeger ook niet zoiets met rekenmachines. Het was 10/3*3-10 sommige gaven 0 als antwoordt en andere gaven 0,00000000001 aan, welke is nu het beste????