Excel 365: controleer formule werkt niet consequent

[DePaul]

Mijn vraag

Ik heb een Excel 365 sheet met een aantal rijen en kolommen met data. Tussen de rijen staan her en der ook subtotalen die onderaan weer optellen naar het grote totaal per kolom. Aangezien er in de rijen nog wel eens wat wijzigt (het gaat hier om personeel met hun salarisgegevens) moeten er af en toe rijen worden verwijderd en nieuwe rijen worden toegevoegd.

Waar ik nu tegenaan loop is dat als je een rij toevoegt direct boven een subtotaal het subtotaal zelf niet wordt geupdate. Voeg je een rij toe boven de rij boven een subtotaal wordt het subtotaal wel geupdate.

Het komt dus helaas wel eens voor dat de subtotalen niet meer juist zijn omdat er dus een rij wordt toegevoegd direct boven een subtotaal.

Relevante software en hardware die ik gebruik

Excel 365

Wat ik al gevonden of geprobeerd heb

Ik ben zelf met een controle formule aan de slag gegaan. Die pakt het totaal van een kolom, trekt daar de som van de afzonderlijke rijen EN de subtotalen vanaf, en deelt deze door twee. Is de uitkomst 0, dan is het goed en blijft de controle cel leeg, anders verschijnt er CHECK als geheugensteun.

Dit is de formule: =IF(J72-(SUM(J8:J71)/2)=0,"","CHECK")

Waar ik nu echter tegenaan loop is dat de bewuste formule bij 18 kolommen staat. Bij 15 van de 18 kolommen "werkt" de formule, bij 3 van de 18 kolommen echter niet terwijl de subtotalen 100% zeker correct zijn. Het is ook geen afrondingsprobleem, als ik alle getallen 8 cijfers achter de komma weer laat geven zijn het netjes allemaal nullen dus dat is het ook niet.

Iemand enig idee wat er mis gaat?

[KabouterSuper]

Er kan van alles mis zijn. Probeer de formule eens door "Evaluate Formula" te gooien. Meestal zie je dan vrij snel wat er mis gaat.

[DePaul]

Heb ik gedaan maar levert niets op; gekke is ook dat ie bij andere kolommen dus wel gewoon werkt

[CappieL]

Wat voor uitkomst krijg je dan bij die 3 van de 18 die niet kloppen?
Krijg je daar foutmeldingen? Of een waarde die niet klopt?

[Teun_2]

Afhankelijk van waar je een rij toevoegd zal die wel of niet worden meegenomen in je formule. Als je bijvoorbeeld een sum(c2:c10) hebt en je voegt een rij middenin die range toe, zal die formule automatisch worden geupdate naar sum(c2:c11). Voeg een rij toe na rij 10, zal die formule geen update meer krijgen. En de waarde in die nieuwe rij dus niet meetellen.

Idealiter haal je die totalen en subtotalen uit je brondata en steek je die in een draaitabel op een apart tabblad.

[ Voor 5% gewijzigd door Teun_2 op 10-08-2022 17:12 ]

[dix-neuf]

@DePaul,
Welke formule staat er in J72 ?
En wáár staan de subtotalen?

[ Voor 28% gewijzigd door dix-neuf op 10-08-2022 17:21 ]

[F_J_K]

Ga met “echte” tabellen werken, dus niet met som J8:J71.
Een tabel maken in Excel

[F_J_K]

Aanvulling: ik vermoed namelijk dat de nieuwe regel niet wordt herkend als deel van de verzameling.

[DePaul]

CappieL schreef op woensdag 10 augustus 2022 @ 16:47:
Wat voor uitkomst krijg je dan bij die 3 van de 18 die niet kloppen?
Krijg je daar foutmeldingen? Of een waarde die niet klopt?

Daar wordt dus CHECK getoond alsof de subtotalen niet kloppen, maar dat doen ze 100% zeker

[DePaul]

Teun_2 schreef op woensdag 10 augustus 2022 @ 17:10:
Afhankelijk van waar je een rij toevoegd zal die wel of niet worden meegenomen in je formule. Als je bijvoorbeeld een sum(c2:c10) hebt en je voegt een rij middenin die range toe, zal die formule automatisch worden geupdate naar sum(c2:c11). Voeg een rij toe na rij 10, zal die formule geen update meer krijgen. En de waarde in die nieuwe rij dus niet meetellen.

Idealiter haal je die totalen en subtotalen uit je brondata en steek je die in een draaitabel op een apart tabblad.

Dat weet ik, echter is dit een format bedacht en opgezet door ons uitermate flexibele hoofdkantoor in Hong Kong dus zo maar andere tabbladen toevoegen zijn uit den boze zeg maar

[DePaul]

F_J_K schreef op woensdag 10 augustus 2022 @ 17:45:
Ga met “echte” tabellen werken, dus niet met som J8:J71.
Een tabel maken in Excel

Helaas, dat is niet toegestaan door ons hoofdkantoor, ik moet het hiermee zien te fixen

[DePaul]

dix-neuf schreef op woensdag 10 augustus 2022 @ 17:18:
@DePaul,
Welke formule staat er in J72 ?
En wáár staan de subtotalen?

@dix-neuf In J72 staat het totaal. De subtotalen staan verspreid tussen J8 en J71

Dank voor het meedenken allemaal mensen

[KabouterSuper]

DePaul schreef op woensdag 10 augustus 2022 @ 16:30:
Heb ik gedaan maar levert niets op; gekke is ook dat ie bij andere kolommen dus wel gewoon werkt

Excel heeft wel fratsen, maar geen bugs. Als het geen afrondingsprobleem is, dan is het een logisch probleem waarvan je achteraf zegt: oja, stom.

Mis je bijvoorbeeld niet een subtotaal? Dan kloppen alle subtotalen die je ziet, maar ga je alsnog de mist in met je check. En kan je eens proberen om alle getallen nul te maken totdat je check niet meer uitslaat?

[Teun_2]

KabouterSuper schreef op donderdag 11 augustus 2022 @ 08:33:
[...]

Excel heeft wel fratsen, maar geen bugs. Als het geen afrondingsprobleem is, dan is het een logisch probleem waarvan je achteraf zegt: oja, stom.

Mis je bijvoorbeeld niet een subtotaal? Dan kloppen alle subtotalen die je ziet, maar ga je alsnog de mist in met je check. En kan je eens proberen om alle getallen nul te maken totdat je check niet meer uitslaat?

Geen bugs

I Wish. Gisteren nog een leuke tegengekomen. Als je een tabel aanmaakt met naam en een van je kolommen de naam heeft " Q". (dus spatie-letter) en je in een andere cel refereert naar "tabel[ Q]" maakt excel er bij opslaan en andere bewerkingen "tabel [ Q]" van, dus echt gewoon een spatie extra invoegen. Dit maakt de formule en daarbij heel je bestand corrupt als je het opslaat.

Maar inderdaad, mééstal is het user error of verkeerd gebruik van Excel.

[ Voor 5% gewijzigd door Teun_2 op 11-08-2022 09:58 ]

[dix-neuf]

DePaul schreef op woensdag 10 augustus 2022 @ 16:18:
Het is ook geen afrondingsprobleem, als ik alle getallen 8 cijfers achter de komma weer laat geven zijn het netjes allemaal nullen dus dat is het ook niet.

Dat mag je op grond daarvan zeker niet besluiten, daarvoor gebruik je met je controle met 8 een veel te klein aantal decimalen! De rekenfout heeft zeer waarschijnlijk te maken met het feit dat Excel getallen binair opslaat. Je probleem zou met gebruikmaking van de functie AFRONDEN eenvoudig op te lossen moeten zijn. Dus (bv.): in J72, als je de som wil afronden op 2 decimalen:
=AFRONDEN(SOM(J8:J71);2)

Afronden is niet hetzelfde als instelling van het aantal decimalen bij de celeigenschappen. Bij instelling bij de celeigenschappen wordt alleen de opmaak van het getal ingesteld, maar het onderliggende getal wordt daarmee niet gewijzigd !

[ Voor 22% gewijzigd door dix-neuf op 11-08-2022 10:48 ]

[DePaul]

@dix-neuf Held!

Dat was inderdaad toch de magie. Afronden erin, op nul zetten, en klaar.

Nogmaals dank allemaal voor het meedenken