Min.als en Max.als

Elke nieuwe versie van Excel heeft weer een aantal nieuwe formule-mogelijkheden.
Had Excel-2003 alleen nog maar de functies AANTAL.ALS en SOM.ALS, in Excel 2016 zitten er al acht.
Als je met Excel 365 of 2019 of hoger werkt, dan heb je de beschikking over MAX.ALS.VOORWAARDEN() en MIN.ALS.VOORWAARDEN.
Maar tot Excel 2016 waren die er nog niet maar gelukkig is daar de onderstaande workaround voor.

Eerst even het verschil tussen formules en functies. Vaak wordt de term formule gebruikt terwijl een functie wordt bedoeld.
Alle cellen met een numerieke waarde die beginnen met het =-teken bevatten een formule. Bijvoorbeeld:

• =1+1
• =2*A2
• =MijnNaam (als MijnNaam als naam gedefinieerd is)
• =SOM(A1:A3)

Een functie is een onderdeel van Excel waarmee berekeningen uitgevoerd kunnen worden. Ze beginnen altijd met het =-teken of komen direct na +, -, *, / of ; en worden altijd gevolgd door een (.
In bovenstaand rijtje met formules bevat alleen de laatste een formule met een functie.
Een formule hoeft dus geen functie te bevatten maar kan wel één of meer functies bevatten.

In Excel 2010 werden naast de functies AANTAL.ALS en SOM.ALS ook de uitgebreidere versies ervan in AANTALLEN.ALS en SOMMEN.ALS beschikbaar evenals de functies GEMIDDELDE.ALS en GEMIDDELDEN.ALS. Maar er bestond nog niet zoiets als MIN.ALS of MAX.ALS. In Excel 2016 is dit toegevoegd als MIN.ALS.VOORWAARDEN en MAX.ALS.VOORWAARDEN.

Voor degenen die nog niet beschikken over Excel 2016 en toch een MAX.ALS-formule wensen kunnen onderstaande oplossing gebruiken.

Stel je hebt een lijst met datums en daarbij de bedragen die je op die datums aan verschillende dingen hebt uitgegeven. Hoe weet je nu wat het duurste artikel per dag was? In onderstaande afbeelding heb je een kolom met datums en een kolom met bedragen.

In cel D3 kun je nu de formule =MAX(ALS($B$3:$B$12=B3;$C$3:$C$12)) plaatsen en in plaats van Enter te geven, sluit je de formule af met Cntrl+Shift+Enter. Daarmee maak je een matrixformule en Excel plaatst dan zelf de accolades om de formule heen.

Daarna kun je deze formule omlaag kopiëren waarbij per dag wordt aangegeven wat het hoogst uitgegeven bedrag is.

Hoe werkt de formule:

In rij B3 staat de datum 20-8-2016 en het bedrag 10, Met "ALS($B$3:$B$12=B3" controleert de matrixformule of er meer rijen zijn met dezelfde datum. Die zijn er (in rij 10). Van al deze rijen bekijkt de MAX-functie wat het hoogste bedrag is in de de reeks $C$3:$C$12. Het maximum van 10 en 20 is 20 en dit wordt dus op beide rijen weergegeven.

De MIN-formule kun je op dezelfde manier gebruiken.

Ook kun je bijvoorbeeld het kleinste getal groter dan nul uit een lijst halen of het grootste getal kleiner dan 30.
Voor het kleinste getal uit kolom C van bovenstaand voorbeeld wat groter moet zijn dan nul, gebruik je de matrixformule (dus opslaan met Cntrl+Shift+Enter: =MIN(ALS(C3:C12<>0;C3:C12)).
Voor het grootste getal kleiner dan 30 gebruik je de matrixformule =MAX(C3:C12<30;C3:C12)

En is iets niet duidelijk, stuur dan een berichtje via het contactformulier van mijn website.