Office: Medienabrechnung

hallo ihr fachmänner!!!

vorab möchte ich sagen, dass ich wirklich eine excel-niete bin!!! für euch ist mein problem bestimmt kein thema (hoffe ich)! ;-)
ok....darum gehts...
ich möchte eine liste erstellen, in der ich die im objekt abgelesenen zählerstände eintragen kann.
aus den zählerständen wird dann automatisch mit dem vormonat ein verbrauch ermittelt und ausgegeben.
aus allen schon angegebenen verbräuchen des jahres wird ein mittelwert errechnet und in die spalte vorne eingetragen.
so weit...so gut! jetzt kommts!
sollte der verbrauch, den ich dann aktuell für irgend einen monat mal eintrage, 20% höher oder niedriger sein als der mittlere verbrauch aller monate, soll die zahl sich dann rot färben. das habe ich mit der bedingten formatierung realisiert.
das problem ist jetzt aber, dass excel diesen "ausreißer" auch zur mittelwertermittlung heranzieht. das soll aber nicht passieren, weil sonst ja der mittelwert verfälscht wird!
wie kann ich excel sagen, dass der mittelwert nur aus den zahlen berechnet werden soll, die in dem +/- 20% bereich liegen?????
ich hoffe, ihr könnt mir da weiter helfen! habe zum besseren verständnis mal meine angefangene tabelle angehängt.

dazu muss ich sagen, dass diese lösung nur eine idee von mir ist! wenn jemand einen besseren vorschlag für die problematik der zähleraufnahme hat, bin ich natürlich für alles offen und dankbar!!!!!
ich meine....ich kann ja nicht der erste mensch sein, der vor dieser aufgabe steht!!!
vielleicht hat ja auch jemand schon sowas in der art fertig!!

ok...ich bedanke mich schon mal vorab für eure hilfe!!!!!!! :!:

 

Hallo!

Dein Beispiel ist etwas unglücklich gewählt, da die bisher eingetragenen Werte (F4 und I4) BEIDE außerhalb deines 20%-Korridors liegen...
Was soll hier als "bereinigter" Mittelwert denn nun ausgegeben werden?

Zum anderen: Den "normalen" Mittelwert (also MIT Ausreißern) brauchst du doch auch weiterhin als "Eich-", bzw. Referenzwert für deinen 20%-Korridor, oder verstehe ich da etwas falsch?

 

oh das ging ja schnell!! super!
also pass auf....
den verbrauch für januar muss man per hand eintragen, da man ja keinen wert zum brechnen hat aus dem vorjahr. den hab ich willkührlich gewählt. einen einfachen wert...10 m³ um das ergebnis besser kontrollieren zu können.
also wäre ja nach der eintragung für januar der mittelwert 10 m³.
dann kommt der februar. hier hab ich mit absicht einen viel höheren wert genommen um zu gucken ob das mit der bed. formatierung klappt.

zum thema mittelwert muss ich vielleicht noch was erklären.
der eigentliche mittelwert aus allen monaten ist mir eigentlich egal!
es geht darum.....
ist ein mieter zum beispiel mal im urlaub wird er logischer weise weniger wasser verbrauchen als sonst. der wert wird aber sicherlich in der +/- 20% grenze liegen. also kann er auch zur berechnung des mittelwertes herangezogen werden.

verbraucht ein mieter aber mehr wasser als mittelwert + 20% ist irgendwas nicht in ordnung...zum beispiel rohr an einer stelle undicht und man hat es nicht mitbekommen! also ist der mittelwert für mich die kontrolle, auf die ich mich in den einzelnen monaten beziehe.
ist jetzt ein ausreißer dabei, darf der natürlich nicht in den mittelwert mit rein! sonst verfälscht er den ja und ich habe keine korrekte kontrolle mehr in den folgemonaten.
mensch...ganz schön kompliziert! ich hoffe du hast das vertsanden! :-?

 

eigentlich ist ja nur interessant, ob er 20% drüber liegt....

 

Hallo!

Aber woher soll Excel nun wissen, ob der Januar- oder der Febraur-Wert der Ausreißer ist?
Das ist mir noch nicht ganz klar...

Wirf doch mal einen Blick auf die Funktion GESTUTZTMITTEL:

Sie rechnet zwar etwas anders als du wünscht, aber vielleicht reicht das dir ja schon (oder ist sogar besser).

Sonst müssten wir irgendwie doch auf den normalen Mittelwert als Referenz zurückgreifen und das Ganze in einer Matrixformel verpacken.
Das könnte dann so aussehen wie im Anhang:
Nur Ausreißer nach oben werden ignoriert.

 

Diese Formel reicht auch schon:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*D2);F4:AM4))

Achtung Matrixformel: Die Eingabe der Formel mit der Tastenkombination Strg+Shift+Enter abschließen!

 

hey....beide lösungen gefallen mir sehr gut!!! klasse! vielen dank!!! ;-)

bei dem versuch die aufgabe heute zu lösen, bin ich an ein generelles problem bei excel gestoßen, das ich so nicht lösen konnte.
ich wollte irgendwie versuchen, excel zu sagen, dass erst der errechnete verbrauch darauf kontrolliert werden soll, ob er 20% höher oder niedriger ist, als der bisherige mittelwert. ist er innerhalb des bereiches, wird ein neuer mittelwert gebildet, mit dem neuen verbrauch. liegt der verbrauch in diesem monat außerhalb, wird kein neuer mittelwert gebildet.

 

mensch schatzi!!! jetzt hast du mir so super geholfen und ich dachte das ist es nun aber.... :-?
ich habe ja mehr als einen zähler. die sollen ja untereinander aufgeführt werden. da du dich in deinen formeln aber auf D2 beziehst, geht das ja irgendwie nicht in den nächsten zeilen!
oder ich füge noch eine spalte ein mit dem tatsächlichen mittelwert für jeden einzelnen zähler. und auf die zelle kann ich dann in deiner formel immer wieder verweisen!? so müste es doch gehen oder?

 

Hallo!

Ganz einfach:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*D$2);F4:AM4))

und dann runterkopieren.

 

na nee......eben nicht.
jeder zähler hat doch seine eigenen verbräuche und seinen eigenen mittelwert.
also benötige ich doch für jeden einzelnen zähler sozusagen eine eigene zelle "D2"!
oder hab ich da jetzt einen denkfehler??? :-?

 

Ääähhhh, ja natürlich...

Dann mach es über eine Extraspalte, in der du die ungeglätteten Mittelwerte aufführst.
Oder du baust es direkt in die Formel ein:

=MITTELWERT(WENN((REST(SPALTE(F:AM);3)=0)*(F4:AM4<1,2*MITTELWERT(F4;I4;L4;O4;R4;U4;X4;AA4;AD4;AG4;AJ4;AM4));F4:AM4))

 

hallo nochmal

alles klar...so gehts!
vielen, vielen dank für die schnelle hilfe!
dafür gibts ne lupenreine 1,0 mit sternchen von mir!!! ;-)