Wenn Wert in Zelle = (zweit)größter Wert dann ...

[Friedrich Vosberg]

Moin.

Ich muss Folgendes in Excel darstellen: Vier Sensoren liefern Messwerte.
Diese werden wöchentlich summiert. Daraus wird wöchentlich ein
Durchschnitt gebildet.

Nun müssen Ressourcen an die vier Messstellen verteilt werden und zwar
nach folgendem Schlüssel:

Ressourcen werden nur verteilt an Messstellen, die über dem
Durchschnittswert liegen.

1. Bei drei über dem Durchschnitt liegenden Messwerten:

a) Sind alle über dem Durchschnittl iegenden Messwerte gleich hoch, wird
an jede Messstelle ein Sechstel der verfügbaren Ressourcen verteilt.

b) Gibt es zwei höchste Messwerte und einen weiteren zweithöchsten
Messwert über dem Durchschnitt, bekommen die beiden höchsten Messstellen
je zwei Fünftel der verfügbaren Ressourecen und die zweithöchste
Messstelle ein Fünftel.

c) Gibt es einen höchsten und zwei zweithöchste Messwerte, bekommt die
höchste Messstelle ein Drittel der verfügbaren Ressourcen und die beiden
zweithöchsten Messstellen je ein Zwölftel der verfügbaren Ressourcen.

2. Bei zwei über dem Durchschnitt liegenden Werten:

a) Gibt es einen höchsten und einen zweithöchsten Messwert über dem
Durchschnitt, bekommt die höchste Messstelle ein Drittel und die
zweithöchste Messstelle ein Sechstel der Ressourcen.

b) Liegen beide Messtellen gleichauf, bekommt jede ein Viertel der
Ressourcen.

3. Bei nur einer über dem Durchschnitt liegenden Messtelle bekommt diese
die Hälfte der verfügbaren Ressourcen.

Wie lautet die Formel, wenn in den Zellen der Spalten A bis D die
Messwerte der Messstellen eingetragen werden und in den Zellen der
Spalten E bis H die Ressourcenzuteilungen ausgegeben werden sollen,
wobei in Spalte E die Ressourcen für die Messtelle in Spalte A, in
Spalte F die Ressourcen für die Messtelle in Spalte B usw. ausgegeben
werden?

Danke im Voraus!

Gruß. Friedrich

[Claus Busch]

Hallo Friedrich,

Post by Friedrich Vosberg
Ich muss Folgendes in Excel darstellen: Vier Sensoren liefern Messwerte.
Diese werden wöchentlich summiert. Daraus wird wöchentlich ein
Durchschnitt gebildet.

kannst du mal den Aufbau deiner Tabelle erklären, damit man das
nachstellen kann. Wo sind die Blutdruckwerte und wo das Datum. Muss die
Woche auch noch berechnet werden oder ergibt die sich aus dem
Tabellenaufbau?
Es ist unnötige Zeit, hier eine Formel zu entwickeln, die nachher doch
nicht auf deine Daten passt.


Mit freundlichen Grüßen
Claus

[Friedrich Vosberg]

Post by Claus Busch

Post by Friedrich Vosberg
Ich muss Folgendes in Excel darstellen: Vier Sensoren liefern Messwerte.
Diese werden wöchentlich summiert. Daraus wird wöchentlich ein
Durchschnitt gebildet.

kannst du mal den Aufbau deiner Tabelle erklären, damit man das
nachstellen kann.

Spalte A: Wochennummer
Spalte B: Messwert am Regler 1
Spalte C: Messwert am Regler 2
Spalte D: Messwert am Regler 3
Spalte E: Messwert am Regler 4
Spalte F: Summe der Messwerte (=Summe(B:E))
Spalte G: Durchschnittlicher Messwert (=F/4)

Bei den Messwerten handelt es sich um natürliche Zahlen einschließlich
Null.

In den Zellen der nun folgenden Spalten H bis K wird anhand derjenigen
Messwerte, die unter dem Durchschnitt liegen, aufgrund vorgegebener
Faktoren errechnet, wieviele Ressourcen den Reglern zur Verfügung
gestellt werden müssen. Bei Messwerten, die über dem Durchschnitt
liegen, müssen keine Ressourcen zur Verfügung gestellt werden:

Spalte H: =WENN(B<$G;($G-B)*0,11;0)
Spalte I: =WENN(C<$G;($G-C)*0,44;0)
Spalte J: =WENN(D<$G;($G-D)*0,15;0)
Spalte K: =WENN(E<$G;($G-E)*0,45;0)
Spalte L: =Summe(H:K)

Und nun müssen in den Zellen der Spalten M bis P die Ressourcen auf
diejenigen Regler verteilt werden, die über dem Durchschnittswert in
Spalte G liegen. D.h., wenn der Messwert in den Zellen der Spalten B bis
E kleiner oder gleich dem Wert in Spalte G ist, muss in der betreffenden
Zelle der Spalten M bis P 0 erscheinen. Das ist noch einfach. Wenn aber
der Messwert größer ist als der Durchschnitt, dann muss (a) ausgewertet
werden, ob insgesamt ein, zwei oder drei Messwerte über dem Durchschnitt
liegen, und - je nachdem - muss dann die Gesamtmenge der Ressourcen -
Zelle in Spalte L - nach folgendem Schlüssel verteilt werden:

1.) Verteilt wird immer nur die Hälfte der vorhandenen Ressourcen
(verteilbare Ressourcen).
2.) Liegt nur ein Messwert über dem Durchschnitt, bekommt dieser die
verteilbaren Ressourcen allein.
3.) Liegen zwei Messwerte über dem Durchschnitt und sind
a) diese beiden Messwerte gleich groß, bekommt jeder von ihnen eine
Hälfte der verteilbaren Ressourcen. Sind hingegen
b) die beiden Messwerte unterschiedlich, bekommt der größere von beiden
zwei Drittel der verteilbaren Ressourcen und der kleiner ein Drittel der
verteilbaren Ressourcen.
4.) Liegen drei Messwerte über dem Durchschnitt und
a) sind diese drei Messwerte gleich groß, bekommt jeder ein Drittel der
verteilbaren Ressourcen. Sind hingegen
b) zwei Messwerte gleich groß und größer als der dritte
überdurschnittliche Messwert, bekommen die beiden größeren je zwei
Fünftel und der kleinene Messwert ein Fünftel der verteilbaren
Ressourcen. Sind
c) die beiden kleineren überdurchschnittlichen Ressourcen gleich groß,
bekommt der größte Messwert zwei Drittel und die beiden kleineren
überdurchschnittlichern Ressourcen je ein Sechstel der verteilbaren
Ressourcen. Und wenn
d) die drei überdurschnittlichen Messwerte alle unterschiedlich sind,
dann bekommt der größte Messwert die Hälfte, der zweitgrößte Messwert
ein Drittel und der kleinste überdurschnittliche Messwert ein Sechstel
der verteilbaren Ressourcen.

Und in Spalte O verbleibt dann der Rest der nicht verteilten Ressourcen
(=L-Summe(M:P)).

Gruß. Friedrich

[Claus Busch]

Hallo Friedrich,

Post by Friedrich Vosberg
Spalte A: Wochennummer
Spalte B: Messwert am Regler 1
Spalte C: Messwert am Regler 2
Spalte D: Messwert am Regler 3
Spalte E: Messwert am Regler 4
Spalte F: Summe der Messwerte (=Summe(B:E))
Spalte G: Durchschnittlicher Messwert (=F/4)

ich bin davon ausgegangen, dass du Überschriften hast. Die folgende
Formel gehört also nach M2:
(WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=3);0,5/3;WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);2/5*0,5;WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);2/3*0,5;WENN(B2=MAX($B2:$E2);0,25;WENN(UND(B2=KGRÖSSTE($B2:$E2;2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);1/6*0,5;WENN(B2=KGRÖSSTE($B2:$E2;2);0,5/3;WENN(B2=KGRÖSSTE($B2:$E2;3);1/6*0,5;0))))))))*(ZÄHLENWENN($B2:$E2;">"&$G2)=3)
Ziehe sie nach rechts und unten so weit benötigt.


Mit freundlichen Grüßen
Claus

[Friedrich Vosberg]

(WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=3);0,5/3;WENN(UND(B2=M
AX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);2/5*0,5;WENN(UND(B2=MAX($B2:$E2);
ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);2/3*0,5;WENN(B2=MAX($B2:$E2)
;0,25;WENN(UND(B2=KGRÖSSTE($B2:$E2;2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$
E2;2))=2);1/6*0,5;WENN(B2=KGRÖSSTE($B2:$E2;2);0,5/3;WENN(B2=KGRÖSSTE($B
2:$E2;3);1/6*0,5;0))))))))*(ZÄHLENWENN($B2:$E2;">"&$G2)=3)

Da ist noch irgendwie ein Fehler drin, denn die Zelle L2 kommt in der
Formel ja gar nicht vor. Aber die Zelle L2 enthält doch die Gesamtmenge
an Ressourcen, von denen die Hälfte verteilt werden muss.

Gruß. Friedrich

-- http://www.rafv.de

post (at) vatolin (punkt) de

[Claus Busch]

Hallo Friedrich,

Post by Friedrich Vosberg
Da ist noch irgendwie ein Fehler drin, denn die Zelle L2 kommt in der
Formel ja gar nicht vor. Aber die Zelle L2 enthält doch die Gesamtmenge
an Ressourcen, von denen die Hälfte verteilt werden muss.

das mit der Zelle L2 war sehr versteckt und leicht zu überlesen. Du
brauchst nur 0,5 mit $L2/2 zu ersetzen:
=UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;">"&$G2)=1)*$L2/2+WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);1/2*$L2/2;WENN(B2=MAX($B2:$E2);2/3*$L2/2;WENN(B2=KGRÖSSTE($B2:$E2;2);1/3*$L2/2;0)))*(ZÄHLENWENN($B2:$E2;">"&$G2)=2)+(WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=3);$L2/2/3;WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);2/5*$L2/2;WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);2/3*$L2/2;WENN(B2=MAX($B2:$E2);1/2*$L2/2;WENN(UND(B2=KGRÖSSTE($B2:$E2;2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);1/6*$L2/2;WENN(B2=KGRÖSSTE($B2:$E2;2);$L2/2/3;WENN(B2=KGRÖSSTE($B2:$E2;3);1/6*$L2/2;0))))))))*(ZÄHLENWENN($B2:$E2;">"&$G2)=3)


Mit freundlichen Grüßen
Claus

[Friedrich Vosberg]

Claus Busch <***@t-online.de> wrote:
=UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;">"&$G2)=1)*$L2/2+WENN(UND(B2=MA
X($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);1/2*$L2/2;WENN(B2=MAX($B2:$E2);2/3*
$L2/2;WENN(B2=KGRÖSSTE($B2:$E2;2);1/3*$L2/2;0)))*(ZÄHLENWENN($B2:$E2;">"
&$G2)=2)+(WENN(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=3);$L2/2/3;WEN
N(UND(B2=MAX($B2:$E2);ZÄHLENWENN($B2:$E2;B2)=2);2/5*$L2/2;WENN(UND(B2=MA
X($B2:$E2);ZÄHLENWENN($B2:$E2;KGRÖSSTE($B2:$E2;2))=2);2/3*$L2/2;WENN(B2=
MAX($B2:$E2);1/2*$L2/2;WENN(UND(B2=KGRÖSSTE($B2:$E2;2);ZÄHLENWENN($B2:$E
2;KGRÖSSTE($B2:$E2;2))=2);1/6*$L2/2;WENN(B2=KGRÖSSTE($B2:$E2;2);$L2/2/3;
WENN(B2=KGRÖSSTE($B2:$E2;3);1/6*$L2/2;0))))))))*(ZÄHLENWENN($B2:$E2;">"&
$G2)=3)
Merci beaucoup!

Gruß. Friedrich