WAARDE

Waarom het werkt

SOM slaat tekst in een bereik bewust over, dus een kolom tekstgetallen telt zonder foutmelding op tot 0. Dat is gevaarlijk bij een controle: een totaal van 0 lijkt een bevinding terwijl het alleen om het gegevenstype gaat.

WAARDE verandert het type, niet de cijfers, en SOM telt de omgezette kolom wel mee. Gehele getallen zonder scheidingstekens worden bij elke regio-instelling op dezelfde manier gelezen; daarom geeft dit voorbeeld op elke computer 21620.

Varianten

=--C2

=--C2

=SOM(WAARDE(C2:C6))

=SOM(WAARDE(C2:C6))

Waarom het werkt

WAARDE kent het procentteken, omdat het tekst leest zoals Excel getypte invoer leest; zolang er geen decimaalteken in de tekst staat, maakt de regio-instelling dus niet uit. ALS.FOUT vertaalt de ene mislukking die je verwacht, een tekst die geen getal is, naar een controleregel: vrijgesteld betekent geen btw. Omzetten en terugvallen zijn twee aparte stappen, dus je ziet bij elk resultaat welke stap het heeft opgeleverd.

Meer om op te letten

• Een percentage als 4,5% bevat een decimaalteken, en dat leest WAARDE volgens de regio-instelling. Houd zulke lijsten op hele percentages, of bekijk het voorbeeld op niveau Expert.

Varianten

=B2 * ALS.FOUT(WAARDE(C2), 0)

=B2 * ALS.FOUT(WAARDE(C2); 0)

=B2 * C2

=B2 * C2

Waarom het werkt

Tekstfuncties zoals DEEL geven altijd tekst terug, ook als elk teken een cijfer is. X.ZOEKEN vergelijkt type én inhoud, dus het uitgeknipte nummer moet eerst een echt getal worden voordat het in de ouderdomsanalyse een overeenkomst kan vinden. WAARDE vormt precies de brug tussen de tekst van de bankreferentie en de getallen in de subadministratie.

Meer om op te letten

• Staan sommige factuurnummers in kolom A zelf als tekst, dan worden die rijen door WAARDE aan de zoekkant nooit meer gevonden. Controleer kolom A met ISGETAL voordat je een Niet gevonden gelooft.

Varianten

=X.ZOEKEN(--DEEL(E2, 5, 5), A2:A6, B2:B6, "Niet gevonden")

=X.ZOEKEN(--DEEL(E2; 5; 5); A2:A6; B2:B6; "Niet gevonden")

=X.ZOEKEN(DEEL(E2, 5, 5), A2:A6 & "", B2:B6, "Niet gevonden")

=X.ZOEKEN(DEEL(E2; 5; 5); A2:A6 & ""; B2:B6; "Niet gevonden")

Waarom het werkt

WAARDE accepteert een minteken vóór een getal, niet erachter, dus de formule regelt het teken zelf: opsporen, afknippen, de rest omzetten en het teken er weer voor zetten. LET geeft elke stap een naam, en ruw wordt maar één keer gelezen. Het deel dat van de regio-instelling afhangt, zit in één stap, WAARDE(cijfers), en die volgt de instelling van wie het bestand opent.

WAARDE accepteert elke notatie die Excel herkent, en Excel herkent volgens jouw eigen regio-instelling: 1234.50 op een Nederlandse computer is niet 1234,5.

Gebruik deze opbouw niet als het decimaalteken van de export niet past bij de instelling van wie hem opent, bijvoorbeeld een Amerikaans bankbestand in Nederlandse Excel. Gebruik dan:

• NUMERIEKE.WAARDE(cijfers; "."; ","), waarmee je de scheidingstekens vastlegt, los van de instelling
• Power Query met de juiste landinstelling voor de kolom, de robuustere weg voor bestanden die steeds terugkomen, met als prijs een stap waarbij je moet vernieuwen in plaats van een formule die altijd actueel is
• de wizard Tekst naar kolommen, sneller voor een eenmalig bestand, met onder Geavanceerd een optie voor een minteken achteraan

Meer om op te letten

• Open je een bestand in Engelse notatie in Nederlandse Excel, dan kan 1234.50 een getal worden dat 100 keer te groot is, omdat de punt dan als scheidingsteken voor duizendtallen geldt, of #WAARDE! opleveren. Het eerste is het gevaarlijke geval, want het ziet eruit als een normaal bedrag. Leg het kolomtotaal naast het bankafschrift voordat je erop vertrouwt.
• Sommige exports zetten bij een bedrag van nul alleen een minteken in de cel. Dan is cijfers leeg en geeft WAARDE #WAARDE!. Bepaal of zo'n rij 0 betekent en vang dat af vóór de omzetting.

Varianten

=LET(
  ruw, SPATIES.WISSEN(C2),
  isNegatief, RECHTS(ruw, 1) = "-",
  cijfers, ALS(isNegatief, LINKS(ruw, LENGTE(ruw) - 1), ruw),
  bedrag, NUMERIEKE.WAARDE(cijfers, ",", "."),
  ALS(isNegatief, -bedrag, bedrag)
)

=LET(
  ruw; SPATIES.WISSEN(C2);
  isNegatief; RECHTS(ruw; 1) = "-";
  cijfers; ALS(isNegatief; LINKS(ruw; LENGTE(ruw) - 1); ruw);
  bedrag; NUMERIEKE.WAARDE(cijfers; ","; ".");
  ALS(isNegatief; -bedrag; bedrag)
)

=ALS(RECHTS(SPATIES.WISSEN(C2), 1) = "-", -WAARDE(LINKS(SPATIES.WISSEN(C2), LENGTE(SPATIES.WISSEN(C2)) - 1)), WAARDE(SPATIES.WISSEN(C2)))

=ALS(RECHTS(SPATIES.WISSEN(C2); 1) = "-"; -WAARDE(LINKS(SPATIES.WISSEN(C2); LENGTE(SPATIES.WISSEN(C2)) - 1)); WAARDE(SPATIES.WISSEN(C2)))