Excel: Hoe tekstvelden in formules negeren?

[Fantomaz]

Mijn vraag

Hoi mensen,
Ik heb een tabel waarin eea moet worden berekend.
Een veld daarin heeft een bedrag.
Echter, als in plaats van het bedrag daar nvt (niet van toepassing staat), moet hij dat veld negeren.
Hij neemt het nu altijd mee, waardoor er geen formule uitgevoerd kan worden.

Relevante software en hardware die ik gebruik
Ik gebruik Excel. Niet de 365 maar het laatste model vóórdat ze 365 werden.

Wat ik al gevonden of geprobeerd heb.
Ik wilde wat proberen met If statements en zo, maar heb denk ik genoeg aan een korte oplossing die ik in de formule kan plakken oid.

Fantomaz
...

[Martinez-]

De manier waarop je de vraag stelt, geeft te weinig concrete aanwijzigingen om tot een goede oplossing te komen. Kan je misschien een voorbeeld uit je Excel hier plaatsen?

Je geeft namelijk aan "het eea te berekenen", maar de oplossing is hier sterk van afhankelijk.

[MisterMailman]

Iets met ISGETAL() en eventueelveen hulpkolom?

[Reinier]

Dit echt een extreem basic geval wat je met één enkele ALS() of IF() kan oplossen. Wat je zelf al gevonden had.
Dus. Check in de ALS() of er Nvt of wat dan ook in de cel staat. Zo ja: niks. Zo nee: de rest van je formule.

[Martinez-]

Reinier schreef op zaterdag 15 maart 2025 @ 20:31:
Dit echt een extreem basic geval wat je met één enkele ALS() of IF() kan oplossen. Wat je zelf al gevonden had.
Dus. Check in de ALS() of er Nvt of wat dan ook in de cel staat. Zo ja: niks. Zo nee: de rest van je formule.

Sterk afhankelijk van wat je wilt berekenen. Als je elk getal individueel evalueert (en dus ook de tekst), dan is dit eenvoudig. Ga je echter kijken naar gemiddelde etc, dan is het toch echt minder eenvoudig.

[Reinier]

Martinez- schreef op zaterdag 15 maart 2025 @ 20:43:
[...]


Sterk afhankelijk van wat je wilt berekenen. Als je elk getal individueel evalueert (en dus ook de tekst), dan is dit eenvoudig. Ga je echter kijken naar gemiddelde etc, dan is het toch echt minder eenvoudig.

Mja, dat is waar. Als TS geen duidelijke voorbeelden geeft doe ik (te) eenvoudige aannames

[dix-neuf]

Fantomaz schreef op zaterdag 15 maart 2025 @ 20:19:
...als in plaats van het bedrag daar nvt (niet van toepassing staat), moet hij dat veld negeren.
Hij neemt het nu altijd mee, waardoor er geen formule uitgevoerd kan worden.

Zoals hierboven ook al door anderen is opgemerkt, is het belangrijk om te weten wát voor formule je voor de betrokken cel(len) wil gebruiken. Wil je bv. een rij of kolom optellen of een andere bewerking op die cel(len) uitvoeren? Als je dat even vermeldt. zal een oplossing snel gevonden zijn (denk ik).

[F_J_K]

Of gewoon 0 invullen.

Hoe dan ook: @Fantomaz geef ajb zowel de concrete situatie als concrete eigen probeersels.

[Fantomaz]

OK, ik ben onvolledig geweest.

Ik hoop dat dit plaatje het wat beter omschrijft.
Als ik in Kolom D, E, F waardes heb die ik handmatig heb ingevoerd, en ik wil die optellen, gaat dat met =D1+E1+F1.
Hetzelfde met rij 2, 3, etc...
Maar als in Kolom D er een text staat ipv een waarde, wordt de formule niet uitgevoerd en krijg ik geen uitkomst.
Mijn idee is het om wanneer er geen waarde in een veld staat, dit veld dan niet mee te nemen in de formule.
En hoewel ik begrijp dat dat middels een IF statement kan, weet ik niet hoe ik dat in Excel kan doen.




Ik stel me voor dat dit gaat met ALS D1=[getal] THEN D1+E1+F1, ELSE E1+F1 of zoiets...

[ Voor 82% gewijzigd door Fantomaz op 16-03-2025 21:24 . Reden: abusievelijk te vroeg gepost ]

[Martinez-]

Fantomaz schreef op zondag 16 maart 2025 @ 21:17:
OK, ik ben onvolledig geweest.

Ik hoop dat dit plaatje het wat beter omschrijft.
Als ik in Kolom D, E, F waardes heb die ik handmatig heb ingevoerd, en ik wil die optellen, gaat dat met =D1+E1+F1.
Hetzelfde met rij 2, 3, etc...
Maar als in Kolom D er een text staat ipv een waarde, wordt de formule niet uitgevoerd en krijg ik geen uitkomst.
Mijn idee is het om wanneer er geen waarde in een veld staat, dit veld dan niet mee te nemen in de formule.
En hoewel ik begrijp dat dat middels een IF statement kan, weet ik niet hoe ik dat in Excel kan doen.


[Afbeelding]

Ik stel me voor dat dit gaat met ALS D1=[getal] THEN D1+E1+F1, ELSE E1+F1 of zoiets...

Kan alleen de eerste kolom nvt bevatten?

Indien ja: nvt = 0 en je hebt geeneens een formule nodig die lastiger is dan =som.

[Fantomaz]

Ik zat al even te spelen met deze formule: =ALS(C5=1;C5;0)
Dat lijkt te werken, wanneer er "1" staat. Dan neemt dat veld de waarde van C5 1 aan. Anders 0

Als ik =ALS(C5>=1;C5;0) doe (dus als de waarde in C5 groter of gelijk is als 1) dan ziet hij "nvt" als meer dan 1 en krijg ik ook 1 te zien in het uitkomstveld.

Ik weet niet hoe ik kan aangeven dat hij alleen wat moet doen wanneer het niet iets numeriek is.

[Fantomaz]

Martinez- schreef op zondag 16 maart 2025 @ 21:29:
[...]


Kan alleen de eerste kolom nvt bevatten?

Indien ja: nvt = 0 en je hebt geeneens een formule nodig die lastiger is dan =som.

Ik heb het even simplistisch gebracht, maar er komen meer velden in waar tekst kan komen te staan ipv waardes. Als ik daar het onderscheid in kan maken, kan ik verder.
De rest heb ik inmiddels redelijk door. Denk ik...

[Miersel]

Zoek eens op de als.fout functie. Je kunt bv een formule maken zoals =als.fout(d1*1;0}+als.fout(e1*1;0)+als.fout(f1*1;0)

[appel001]

Zou eens kijken naar som.als

SOM.ALS, functie - Microsoft Ondersteuning

=SOM.ALS(G2:G25;">5")

Isgetal geeft een waar of onwaar aan. Dus daar zou je ook iets mee kunnen in combinatie met een als functie. Maar die functie is hierboven ook al benoemd.

[Lustucru]

Som.als doet hier idd de truc. Maar dan wel met als criterium <>0. DAn negeert hij weliswaar ook cellen met de waarde 0, maar dat maakt voor een optelling niet zoveel uit.
=SOM.ALS(A1:E1;"<>0")

[MisterMailman]

Miersel schreef op zondag 16 maart 2025 @ 23:47:
Zoek eens op de als.fout functie. Je kunt bv een formule maken zoals =als.fout(d1*1;0}+als.fout(e1*1;0)+als.fout(f1*1;0)

Dat kan toch veel korter?
=als.fout(d1+e1+f1;0)

Som.als lijkt me overkill en is ook qua performance niet ideaal.

[ Voor 10% gewijzigd door MisterMailman op 17-03-2025 06:40 ]

[Croga]

MisterMailman schreef op maandag 17 maart 2025 @ 06:39:
Dat kan toch veel korter?
=als.fout(d1+e1+f1;0)

Die geeft dus 0 als 1 van de kolommen geen getal is. Dat was niet de bedoeling.

Som.als lijkt me overkill en is ook qua performance niet ideaal.

Maar is wel de enige zinvolle optie voor de toekomst. Als er meer kolommen bij komen zal SUMIF blijven werken terwijl iedere optie die kolom voor kolom af gaat zal falen.

In de engineering wereld waar "Build once, run forever" het devies zou moeten zijn is SUMIF de enige zinnige optie. som.als is zowieso nooit een optie in het kader van onderhoudbaarheid.

[Lustucru]

MisterMailman schreef op maandag 17 maart 2025 @ 06:39:
[...]
Som.als lijkt me overkill en is ook qua performance niet ideaal.

Ik zie de overkill niet. Lekker korten helder. Performance is een absoluut onzin-argument. 1000 iteraties van een som.als() berekening over 5 kolommen bij 1000 regels kost op een 5 jaar oude pc nog geen 68 ms. Bovendien, wat zou sneller zijn? Een geoptimaliseerde routine of 5 keer in een interpreter een berekening uitvoeren, foutcode testen en dan optellen? Inderdaad, de losse als.fout() is 15% langzamer. Niet dat je dat merkt...

Croga schreef op maandag 17 maart 2025 @ 06:48:
[[...] is SUMIF de enige zinnige optie. som.als is zowieso nooit een optie in het kader van onderhoudbaarheid.

Spreekje nu jezelf tegen of val je alleen over het gebruik van de Nederlandse functienaam?

[ Voor 20% gewijzigd door Lustucru op 17-03-2025 08:17 ]

[MisterMailman]

Croga schreef op maandag 17 maart 2025 @ 06:48:
[...]

Die geeft dus 0 als 1 van de kolommen geen getal is. Dat was niet de bedoeling.

Nou, dan als.fout(1+1+1;"")

Maar goed, ik vind het allemaal maar moeilijk gedoe voor zoiets simpels

[MisterMailman]

Croga schreef op maandag 17 maart 2025 @ 06:48:
In de engineering wereld waar "Build once, run forever" het devies zou moeten zijn is SUMIF de enige zinnige optie.

Als het over engineering gaat, zou Excel nooit de oplossing voor zoiets moeten zijn

[Croga]

MisterMailman schreef op maandag 17 maart 2025 @ 08:32:
Nou, dan als.fout(1+1+1;"")

Maar goed, ik vind het allemaal maar moeilijk gedoe voor zoiets simpels

Ik ben bang dat je de vraag niet goed begrepen hebt.

MisterMailman schreef op maandag 17 maart 2025 @ 08:33:
Als het over engineering gaat, zou Excel nooit de oplossing voor zoiets moeten zijn

Waarom niet? Excel heeft zeker zijn nuttig gebruik. Er is geen enkele reden om een tool af te serveren omdat jij denkt dat het een slechte tool is. Er is ook geen enkele reden om de principes van engineering excellence te laten varen omdat je denkt dat de tool te simpel is.

[MisterMailman]

Croga schreef op maandag 17 maart 2025 @ 09:00:
[...]

Ik ben bang dat je de vraag niet goed begrepen hebt.

Ik heb het een paar keer gelezen, maar blijkbaar valt het kwartje hier niet idd

Waarom niet? Excel heeft zeker zijn nuttig gebruik. Er is geen enkele reden om een tool af te serveren omdat jij denkt dat het een slechte tool is. Er is ook geen enkele reden om de principes van engineering excellence te laten varen omdat je denkt dat de tool te simpel is.

Nou nou nou, je doet nog al wat aannames

Maar aangezien het kwartje hier niet valt, en het geen discussie-topic over Excel is, hou ik me er verder buiten Hopelijk kunnen jullie TS verder helpen.

[Reinier]

MisterMailman schreef op maandag 17 maart 2025 @ 09:44:
[...]

Ik heb het een paar keer gelezen, maar blijkbaar valt het kwartje hier niet idd

Nvt + 80 + 40 moet 120 opleveren. Jouw formule geeft "" terug en dat is niet conform specs.

[Fantomaz]

Ik ben de draad wat kwijt in jullie (overigens zeer welkome uitleg).
Ik heb al eens een stukje gedaan waarbij ik kijk of de waarde "nvt" meer is dan 0, dan...
Nu is nvt natuurlijk ook een waarde, zij het niet numeriek. Dus slikt die vergelijking het niet als "minder dan 0".
Want nvt is meer dan 0. Net als dat alle ingevulde waardes meer dan 0 zijn.

Dus dat werkt niet.
Maar misschien heb ik een stukje gemist in de uitleg.
Als er 1 of 0 uit kan komen, ben ik al een heel end. Dan kan ik het ergens mee vermenigvuldigen om tot het resultaat te komen.

Het steekt alleen in het "herkennen" van een waarde ipv een stukje tekst.

[Lustucru]

Fantomaz schreef op dinsdag 18 maart 2025 @ 22:14:
[...]

Dus dat werkt niet.

Het voorbeeld met som.als (ook genoemd door @appel001 ) werkt perfect.

Lustucru in "Excel: Hoe tekstvelden in formules negeren?"

[Lustucru]

Fantomaz schreef op dinsdag 18 maart 2025 @ 22:14:
[...]

Het steekt alleen in het "herkennen" van een waarde ipv een stukje tekst.

Daar is IsGetal() voor.

[appel001]

Hmmm gaan we nu excel afserveren en een migratie doen, in het kader van optimalisatie?


Ik denk dat soms toch gewoon even de gebruiker geholpen moet worden. Juist in het kader van efficientie. De suggesties lijken de gebruiker op weg te helpen. Om dit vraagstuk nu bij een heel DevOps team neer te leggen lijkt mij wat kostbaar.

Sterker nog, handmatig 10000 regels nalopen en eventueel verwijderen ga je waarschijnlijk elke week 10 jaar volhouden, zonder dat opschalen en trainen naar een andere tool zinvol is.

Topicstarter is gewoon gebaat bij dit stukje kennis..voor dit project en waarschijnlijk alles wat hij/zij het komende jaar gaat doen.

[Miersel]

Fantomaz schreef op dinsdag 18 maart 2025 @ 22:14:
Ik ben de draad wat kwijt in jullie (overigens zeer welkome uitleg).

Heb je mijn eerdere suggestie al geprobeerd?

=als.fout(d1*1;0}+als.fout(e1*1;0)+als.fout(f1*1;0)

Op het moment dat een cel geen getal bevat levert dat voor de betreffende cel de waarde 0 op. En dat was wat je bedoelde, toch?

Een andere oplossing is dat je wat hulpcellen (kolommen) gaat gebruiken waarin je per op te tellen cel checkt of er een tekst in staat (dan vullen met waarde 0) en anders de waarde uit de cel overneemt.
En dan tel je de hulpcellen bij elkaar op.
Of een cel een tekst bevat kun je zoals hierboven genoemd checken met =isgetal(celverwijzing).

[Rensjuh]

Ik weet niet of er een verschil in de functie =SUM zit omdat ik wél Office365 heb, maar bij mij gaat het goed:


Ik heb dezelfde waardes in kolommen gezet als TS, vervolgens met =SUM de range opgegeven en de waardes worden netjes opgeteld en weergeven zoals je verwacht.

Is ook de verwachte werking volgens de Microsoft sift:
Getting the "Sum" function to ignore text in the cell

edit;
Bij TS gaat het fout omdat hij de kolommen optelt door ze op te tellen met +.
Dan gaat het bij mij ook mis:

Met gebruik van de =SUM() formule gaat het zoals TS verwacht.

[ Voor 42% gewijzigd door Rensjuh op 19-03-2025 09:07 ]

[Lustucru]

offtopic:
Verrek... in min herinnering was dat vroegah niet zo en gaf SOM() ook een fout op tekst, , maar je hebt volkomen gelijk. SOM() negeert van zichzelf al cellen die geen getal bevatten

[ Voor 10% gewijzigd door Lustucru op 19-03-2025 09:10 ]

[Fantomaz]

OK, maar wat gebeurt hier nu dan?

=SOM.ALS(A1:E1;"<>0")

Als de optelling(?) van alle waardes tussen A1 en E1 ongelijk zijn aan "0" toch?

Dan is de uitkomst true of false, 1 of 0 neem ik aan?
Maar ik wil dat van slechts 1 veld weten.

Dus =SOM.ALS(A1;"<>0") want dan kan ik daarmee rekenen.
Toch lijkt Excel alles wat ongelijk is aan 0 (dus ook tekst) een 0 te geven.

Mis ik nog iets?

[update]
Ik zie nu pas dat er meer reacties waren. Ik moet de anderen nog even doornemen.

[ Voor 10% gewijzigd door Fantomaz op 19-03-2025 23:05 ]

[Martinez-]

Fantomaz schreef op woensdag 19 maart 2025 @ 23:04:
OK, maar wat gebeurt hier nu dan?

=SOM.ALS(A1:E1;"<>0")

Als de optelling(?) van alle waardes tussen A1 en E1 ongelijk zijn aan "0" toch?

Dan is de uitkomst true of false, 1 of 0 neem ik aan?
Maar ik wil dat van slechts 1 veld weten.

Dus =SOM.ALS(A1;"<>0") want dan kan ik daarmee rekenen.
Toch lijkt Excel alles wat ongelijk is aan 0 (dus ook tekst) een 0 te geven.

Mis ik nog iets?

[update]
Ik zie nu pas dat er meer reacties waren. Ik moet de anderen nog even doornemen.

Lees gerust de andere reacties. Ik wil je wel even wat toelichting geven op je functie.

De =som.als-functie somt de velden indien deze voldoen aan een aantal condities (bij som.als is dat overigens 1 conditie, sommen.als is die voor meervoudig).

Die geeft dus geen 0 of 1 waarde, maar geeft de waarde van alle optellingen die voldoen aan de conditie die je gesteld hebt.

[Fantomaz]

Rensjuh schreef op woensdag 19 maart 2025 @ 09:05:

edit;
Bij TS gaat het fout omdat hij de kolommen optelt door ze op te tellen met +.
Dan gaat het bij mij ook mis:
[Afbeelding]
Met gebruik van de =SUM() formule gaat het zoals TS verwacht.

Dit was de spijker op z'n kop! Behalve dat ik het met SOM moet doen omdat ik een Nederlandse versie heb.

Bedankt iedereen voor het geduld en voor jullie inzet!!