Funktion liefert im Arbeitsblatt #WERT!

Forumthread: Funktion liefert im Arbeitsblatt #WERT!

29.12.2020 16:13:44

Sultan

Hallo Excelspezialisten
Mit Makros habe ich keine Probleme und noch immer alles so hinbekommen wie ich es haben wollte, jedoch versuche ich mich seit einiger Zeit an Excelfunktionen zwecks Verwendung in Arbeitsmappen, meist mit mässigem Erfolg :(
Aktuell möchte ich wieder eine Funktion zur Verwendung im Tabellenblatt machen, der Wert welcher die Formel liefert ist jedoch nicht das von mir erwartete. Ich habe das Gefühl dass alles korrekt ist, was offensichtlich nicht der Wahrheit entspricht :D Könnt Ihr euch mal meine Funktion anschauen?
https://www.herber.de/bbs/user/142646.xls
Die Funktion soll die Werte aus der Tabelle nehmen, sortieren und dann den Mittelwert aus allen Werten (ausser den zwei höchsten und den zwei tiefsten Werten) bilden.

Function mittelmittel(ByVal liste As Variant)
'ermittelt den mittelwert ohne die zwei höchsten und zwei tiefsten werte
Dim laenge As Variant
laenge = UBound(liste) - 1
Dim temp As Variant
temp = 0
'sortieren
For i = 0 To laenge
For n = 0 To laenge
If liste(n) > liste(n + 1) Then
temp = liste(n + 1)
liste(n + 1) = liste(n)
liste(n) = temp
End If
Next
Next
temp = 0
'mittelwert aus den innersten Elementen (ohne die höchsten zwei und tiefsten zwei)
For n = 2 To UBound(liste) - 3
temp = temp + liste(n)
Next
mittelmittel = temp / (laenge - 4)
End Function

Vielen Dank und Freundliche Grüsse

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: Funktion liefert im Arbeitsblatt #WERT!

29.12.2020 16:49:03

onur


Public Function mittelmittel(ByRef liste)
'ermittelt den mittelwert ohne die zwei höchsten und zwei tiefsten werte
Dim laenge, temp
laenge = UBound(liste, 2)
liste = liste
'sortieren
For i = 1 To laenge
For n = 1 To laenge
If liste(1, n) > liste(1, i) Then
temp = liste(1, n)
liste(1, n) = liste(1, i)
liste(1, i) = temp
End If
Next n
Next i
temp = 0
'mittelwert aus den innersten Elementen (ohne die höchsten zwei und tiefsten zwei)
For n = 3 To UBound(liste, 2) - 2
temp = temp + liste(1, n)
Next
mittelmittel = temp / (laenge - 4)
End Function

AW: Funktion liefert im Arbeitsblatt #WERT!

29.12.2020 17:00:29

ralf_b

oder so ?

Function mittelmittel(liste As Range)
'ermittelt den mittelwert ohne die zwei höchsten und zwei tiefsten werte
Dim dsum As Double
Dim dmax As Double
Dim dmin As Double
With WorksheetFunction
dsum = .sum(liste)
dmax = .Large(liste, 1) + .Large(liste, 2)
dmin = .Small(liste, 1) + .Small(liste, 2)
End With
mittelmittel = (dsum - dmax - dmin) / (liste.Count - 4)
End Function

AW: Funktion liefert im Arbeitsblatt #WERT!

31.12.2020 07:05:23

Sultan

Vielen Dank, dies funktioniert so, leider verstehe ich nicht wieso mein code nicht funktioniert, habe ich da etwas Grundsätzliches (ev beim Einsatz von Schleifen) nicht verstanden?
Der Code an sich scheint mir in der Funktion schlüssig, wenn ich dies als sub ausführe liefert es auch Resultate, doch als Funktion... :(

AW: Funktion liefert im Arbeitsblatt #WERT!

31.12.2020 11:22:10

ralf_b

also deine Function läuft mitten drin schon auf einen Fehler und hat diesen zurückgegeben.
Ob byval oder byref ist egal, da das Objekt immer als Range reinkommt. Probiers aus.
schau dir mal deine "liste" im Überwachungs- oder Lokalfenster an. Liste ist eine Range.
liste(1,1) ist nicht eindimensional womit die Zugriffe auf liste(n) nicht funktionieren.
Und die 0 basierte Sortierung geht auch nicht da eine Range nicht 0 basiert ist.
Ubound(liste) liefert schon ein "Typen unverträglich" Fehler und gibt #Wert# aus
Hier eine Variante mit deiner Sortierung.
Durch die arr Variable wird die Berechnung vom Tabellenblatt und den Zellen entkoppelt.

Function mittelmittel2(liste As Range)
'ermittelt den mittelwert ohne die zwei höchsten und zwei tiefsten werte
Dim arr, temp
Dim laenge As Long
arr = liste
laenge = UBound(arr, 2)
'sortieren
For i = 1 To laenge - 1
For n = 1 To laenge - 1
If arr(1, n) > arr(1, n + 1) Then
temp = arr(1, n + 1)
arr(1, n + 1) = arr(1, n)
arr(1, n) = temp
End If
Next
Next
temp = 0
'mittelwert aus den innersten Elementen (ohne die höchsten zwei und tiefsten zwei)
For n = 3 To laenge - 2
temp = temp + arr(1, n)
Next
mittelmittel2 = temp / (laenge - 4)
End Function

gruß
rb

Beliebteste Forumthreads (12 Monate)

Funktion liefert im Arbeitsblatt #WERT!

Forumthread: Funktion liefert im Arbeitsblatt #WERT!

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread