• Privacywetgeving
    Het is bij Helpmij.nl niet toegestaan om persoonsgegevens in een voorbeeld te plaatsen. Alle voorbeelden die persoonsgegevens bevatten zullen zonder opgaaf van reden verwijderd worden. In de vraag zal specifiek vermeld moeten worden dat het om fictieve namen gaat.

Gemiddelde leeftijd berekenen maar meerdere leeftijden in 1 cel

Status
Niet open voor verdere reacties.
M

Mieke1985

Gebruiker
Lid geworden
3 mrt 2011
Berichten
101
Hoi,

Ik zoek een formule(of oplossing) voor het volgende:

Ik wil graag het gemiddelde berekenen van een kolom met leeftijden. In sommige cellen staan meerdere leeftijden. Soms met een komma ertussen maar soms ook met 'en' er tussen.
Kan iemand mij helpen?

Het is iets wat maandelijks terugkomt. Deel van het bestand bijgevoegd.
 

Bijlagen

  • Gemiddelde leeftijd cellen met meerdere leeftijden.xlsx
    9,2 KB · Weergaven: 20
Met Power Query kan ik de leeftijden "uit elkaar trekken" (o.b.v een komma en het woordje "en") en daarna via wat bewerkingen de gemiddelde leeftijd berekenen. Als je dit eenmaal hebt kun je elke maand de tabel aanvullen of wijzigen en rolt het nieuwe gemiddelde eruit (na "vernieuwen").
 

Bijlagen

  • Gemiddelde leeftijd cellen met meerdere leeftijden (AC).xlsx
    17,9 KB · Weergaven: 14
Laatst bewerkt:
Bedankt voor je snelle reactie! Helaas is dit een Excel lijst voor op mijn werk en wij hebben geen Power Query.
Zou er nog een oplossing zijn?
 
Als je Excel hebt, heb je Power Query... :D

Menu Gegevens > Gegevens ophalen > Power Query editor starten
Of klik met de rechtermuisknop op de gemiddelde leeftijd en dan via tabel > query bewerken.

Je ziet dan (rechts) de stappen die losgelaten worden op je brongegevens om tot het eindresultaat te komen. Klik er maar eens op van boven naar beneden...
 
Oplossing met een UDF.

Plaats dit achter een module, open de VBA editor(Alt + F11) --> rechtermuisknop op het project --> invoegen module --> plak de code.

Code: 
Function jec(rng As Range) As Double
 With CreateObject("VBScript.RegExp")
  For Each it In rng
    .Global = True
    .Pattern = "\D"
    If .test(it) Then
      For Each xVal In Split(Application.Trim(.Replace(it, " ")))
        xTot = xTot + Val(xVal): x = x + 1
      Next
    Else
        xTot = xTot + it.Value: x = x + IIf(it.Value <> "", 1, 0)
    End If
  Next
 End With
jec = xTot / x
End Function

Edit: code iets aangepast

Daarna kun je deze oproepen met =jec(A2:A60)
 
Laatst bewerkt:
@Sylvester, wij komen op andere gemiddelden:d

In het voorbeeld van TS heb ik alles even uit elkaar gehaald en het moet uitkomen op 10,57. Dat is overigens de uitkomt van mijn UDF
 
Laatst bewerkt:
Die klopt ook uiteraard!:)
Enige nadeel is dat die niet automatisch update
 
ik kom ook op 10,57:)

Code: 
Function VenA(r As Range) As Double
  ar = r
  For j = 1 To UBound(ar)
    If ar(j, 1) <> "" Then
      x = Split(Replace(ar(j, 1), "en", ","), ",")
      For jj = 0 To UBound(x)
        t = t + 1
        s = s + Val(x(jj))
      Next jj
    End If
  Next j
  VenA = s / t
End Function
 
:)
Ik had het ook eerst met replace gedaan, maar toen zag ik dat er verschillende soorten tekst in de cellen stond.
Vervolgens maar voor Regex gegaan, die alle soorten tekst in 1 klap wegfiltert.:cool:
 
ik heb voor de lol en duidelijkheid jan en piet aan de lijst toegevoegd.
mijn functie haalt alle tekst weg
en slaat lege cellen over.
 
Laatst bewerkt:
Als je een getal met gelijk daarachter een komma hebt, gaat het mis bij die van jou.
Kleine fix lijkt me;)
 

Bijlagen

  • Gemiddelde leeftijd.xlsm
    19,3 KB · Weergaven: 10
JVeer, ik verwacht dat als Mieke 7,4 invult, dat ze 7 & 4 bedoelt. Hele jaren.
bijna niemand is precies een heel jaar oud.
 
Laatst bewerkt:
Ik denk dat jouw functie het ziet als 7,4 jaar. Dus geen heel jaar.

7,4 is numeriek, dus zal niet door je eerste IF statement heenkomen.
 
Laatst bewerkt:
JVeer, je hebt helemaal gelijk. ik heb mijn functie in jouw bestandje aangepast. nu doet hij het zelfde als de jouwe.

nu nog iets voor "John en arend: 11, Piet en kees: 8 " verzinnen
 

Bijlagen

  • Gemiddelde leeftijd 2.xlsm
    18,6 KB · Weergaven: 18
Laatst bewerkt:
Weer typisch een voorbeeld hoe code een foute struktuur moet verhelpen.
Wat is er moeilijk aan in een cel slechts 1 gegeven te plaatsen ?
Wees wijs: pas de struktuur aan aan de latere bewerkingen.

Helaas is dit een Excel lijst voor op mijn werk
Klik om te vergroten...
Als dit kenmerkend is voor de kwaliteit van jouw organisatie ........
 
Laatst bewerkt:
AlexCEL zei:
Als je Excel hebt, heb je Power Query... :D

Menu Gegevens > Gegevens ophalen > Power Query editor starten
Of klik met de rechtermuisknop op de gemiddelde leeftijd en dan via tabel > query bewerken.

Je ziet dan (rechts) de stappen die losgelaten worden op je brongegevens om tot het eindresultaat te komen. Klik er maar eens op van boven naar beneden...
Klik om te vergroten...

Bedankt dat wist ik niet. Ik ga er mee aan de slag!
 
snb zei:
Weer typisch een voorbeeld hoe code een foute struktuur moet verhelpen.
Wat is er moeilijk aan in een cel slechts 1 gegeven te plaatsen ?
Wees wijs: pas de struktuur aan aan de latere bewerkingen.


Als dit kenmerkend is voor de kwaliteit van jouw organisatie ........
Klik om te vergroten...


Beetje vreemde reactie? Korte toelichting; het is een onderdeel van een heel groot bestand waar meerdere personen in werken. Mijn ervaring is dat je wel instructies kan geven voor het invullen maar dat deze niet altijd nageleefd worden.

Allen bedankt voor jullie moeite!
 
SNB heeft in mijn ogen gelijk, pak het probleem aan bij de bron.
Hier een voorbeeld hoe je de instructies kunt afdwingen.
Ik ga er wel van uit dat de leeftijd niet hoger dan 99 jaar.:d
 

Bijlagen

  • leeftijden.xlsm
    16,3 KB · Weergaven: 22
Status
Niet open voor verdere reacties.
Steun Ons

Nieuwste berichten

Terug
Bovenaan Onderaan