Zwischenwert Sverweis

21.03.2015 20:09:35

Andreas

Bei der Eingabe von einem OEE Jahresdurchschnitt von z.B.89 hätte ich gerne die genaue Zielereichng in %. Also sozusagen den Zwischenwert erechnet die zu dem Bereich Sverweis passt. Ist so etwas möglich? Welche Formel müsste dann in C6 stehen? Danke für Eure Hilfe!
https://www.herber.de/bbs/user/96539.xlsx

AW: Zwischenwert Sverweis

21.03.2015 20:25:50

Alexander

Hallo Andreas,
was meinst du mit der genauen Zielerreichung in % und mit dem Zwischenwert?
Gruß Alex

AW: Zwischenwert Sverweis

21.03.2015 20:35:48

Andreas

Hallo, und danke das du mir helfen möchtest. Der normale Sverveis gibt ja in diesem Fall 100 als Ergebnis aus. Bei der Eingabe von OEE 89 würde ich mir erhoffen das Excel mir das genaue Ergebnis ausrechnet. (z.B 116). Ich weis aber selbst leider nicht wie mathematisch die "Abstufungen" zwischen z.B.85 und 100 OEE zu Errechnen wären. Ich hoffe diese Erklärung hilft weiter.

wenn ich Dein Ziel richtig interprtetiere ...

21.03.2015 20:51:17

neopa

Hallo Andreas,
... dann sind Deine Werte in B8:B13 schon Prozentwerte nur das diese bei Dir momentan noch kein Prozent-Zahlenformat aufweisen? Oder?
In dem Fall dann z.B. mit folgender Formel in C6 so:

=(SVERWEIS(B3;A8:B13;2)+WENNFEHLER((AGGREGAT(15;6;B8:B13/(A8:A13>=B3);1)-SVERWEIS(B3;A8:B13;2)) /(B3-AGGREGAT(14;6;A8:A13/(A8:A13
Gruß Werner
.. , - ...

AW: wenn ich Dein Ziel richtig interprtetiere ...

21.03.2015 20:58:59

Andreas

Ja richtig es sind schon % -Werte. Das von mir erwartete Ergebnis in C6 müsste ja zwischen 100 und 125 liegen.(Bei der Eingabe89). Die lange Formel von Werner gibt ja auch 100 als Ergebnis aus. Aber Danke für den Versuch.

habe übersehen, dass Du "nur" XL2007 hast ...

21.03.2015 21:12:45

neopa

Hallo Andreas,
... in der Version gab es die Funktion AGGREGAT() noch nicht und deshalb kannst Du das korrekte Ergebnis (106,25 nicht damit ermitteln. Ist aber auch ohne AGGREGAT() ermittelbar. Allerdings gehe ich jetzt erst einmal für heute wieder offline. Meinerseits dann Morgen wieder.
Gruß Werner
.. , - ...

AW: habe übersehen, dass Du "nur" XL2007 hast ...

21.03.2015 21:16:51

Andreas

OK, Danke für deine Hilfe. Würde mich über einen Weiteren Versuch morgen freuen.

AW: habe übersehen, dass Du "nur" XL2007 hast ...

21.03.2015 21:22:32

Andreas

Das wäre genau das von mir erwartete Ergebnis. würde mich also besonders auf morgen freuen Danke

mal aufgedröselt

22.03.2015 01:28:37

WF

Hi,
in D3 steht:
=SVERWEIS(B3;A8:B13;1)
in E3 steht:
=SVERWEIS(B3;A8:B13;2)
in D4 steht die Arrayformel:
{=INDEX(A8:A13;VERGLEICH(MIN(WENN(A8:A13>=B3;A8:A13));A8:A13;0))}
in E4 steht:
=SVERWEIS(D4;A8:B13;2;0)
das Ergebnis:
=WENN(B3 WF
Ich nehme an, Werte über 150 können nicht vorkommen.

D4 natürlich nur

22.03.2015 01:40:49

WF

in D4 steht die Arrayformel:
{=MIN(WENN(A8:A13>=B3;A8:A13))}
WF

AW: D4 natürlich nur

22.03.2015 11:42:14

Andreas

Danke an euch beide. Damit komme ich Prima weiter.

bei Tageslicht betrachtet ...

22.03.2015 09:59:48

neopa

Hallo Andreas,
... lag ich viel mehr daneben. Nicht nur, dass ich übersehen habe Du XL2007 im Einsatz hast, sondern meine Formel war auch nicht korrekt. Sorry, hatte gestern leider nicht getestet.
Und es gibt auch für XL2007 eine {}-freie hilfsspaltenfreie wie schnelle und sogar etwas kürzere Lösung. Siehe nachfolgende Formel in C6. Der Lösungsvorschlag von WF kann auch für frühere Excelversionen genutzt werden.
Und der Vollständigkeit halber noch die korrekte AGGREGAT()-Formellösung (ab Excel 2010) in D6:

	C	D
6	106,6666667	106,6666667

Formeln der Tabelle

Zelle	Formel
C6	=SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0)
D6	=WENNFEHLER(SVERWEIS(B3;A8:B13;2)+(B3-SVERWEIS(B3;A8:B13;1))*(AGGREGAT(15;6;B8:B13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;2))/(AGGREGAT(15;6;A8:A13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;1)); 0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

doch noch nicht munter genug gewesen ...

22.03.2015 10:24:31

neopa

Hallo Andreas,
... korrekter so:

	C	D
6	200	200

Formeln der Tabelle

Zelle	Formel
C6	=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0))
D6	=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(AGGREGAT(15;6;B8:B13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;2))/(AGGREGAT(15;6;A8:A13/(A8:A13>=B3); 1)-SVERWEIS(B3;A8:B13;1)); 0))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

AW: doch noch nicht munter genug gewesen ...

22.03.2015 11:43:40

Andreas

Danke Für Eure Mühe. Jetzt komme ich weiter. Da wäre ich selbst nicht drauf gekommen!

dann hast Du die Formel C6 nicht getestet....

22.03.2015 16:50:26

neopa

Hallo Andreas,
... denn sonst hätte Dir auffallen sollen, dass in der Formel sich auch auf die Zelle D4 bezieht, wo nichts oder was ganz anderes steht und somit die Formel ein fehlerhaftes Ergebnis liefert.
Die Formel C6 nun hoffentlich endlich korrekt (die Ergebniswertanzeige von heute Morgen war einmal für B3=89 dann für B3=175 und nun wieder für B3=89) ist nun auch länger als meine AGGREGAT()-Formellösung für Excel 2010 (nur deshalb habe ich mir die Formel jetzt eben nochmal angeschaut, weil mir beim Nachmittagskuchenessen eingefallen war, das es eigentlich nicht sein kann, dass die Formel ohne AGGREGAT() kürzer ist, wie heute Morgen geschrieben):

	C
6	106,6666667

Formeln der Tabelle

Zelle	Formel
C6	=WENN(B3<30;0;SVERWEIS(B3;A8:B13;2)+WENNFEHLER((B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); )); A8:B13;2;0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13); ))-SVERWEIS(B3;A8:B13;1)); 0))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...

Häufigkeit statt MIN(WENN bei mir eingebaut

22.03.2015 21:22:33

WF

ergibt dann in einer Formel kürzere:
=WENN(B3A13;B13;SVERWEIS(B3;A8:B13;2)+(B3-SVERWEIS(B3;A8:B13;1))*(SVERWEIS(D4;A8:B13;2; 0)-SVERWEIS(B3;A8:B13;2))/(INDEX(A8:A13;VERGLEICH(1;HÄUFIGKEIT(B3;A8:A13);))-SVERWEIS(B3;A8:B13;1) +1/9^9)))
WF

ergibt nicht wirklich das angestrebte, denn ...

23.03.2015 10:02:22

neopa

Guten Morgen WF,
... auch Dir ist das gleiche Versehen unterlaufen, wie mir gestern Morgen.
Du hast in ... *(SVERWEIS(D4;A8:B13;2;0) ... das D4 nicht durch den entsprechenden Formelteil ersetzt. Wenn Du das noch vornimmst, wird die End-Formel länger als die AGGREGAT()-Formellösung. Sie ist dann zwar etwas kürzer als meine HÄUFIGKEIT()-Formel, aber dafür fängt sie Eingaben in B3 von kleiner 30 und größer 150 nicht korrekt ab (wobei man natürlich darüber streiten könnte, ob es richtig ist, wenn ich für Eingaben in B3 größer 150 stets 200 als Ergebnis listen lasse.)
Gruß Werner
.. , - ...

stimmt - hab ich übersehen

23.03.2015 10:55:59

WF

.

AW: dann hast Du die Formel C6 nicht getestet....

23.03.2015 17:00:29

Josef

Hallo
Noch eine Variante.
Gruss Sepp
Tabelle2

	A	B	C
1		OEE Jahres Durchschnitt	Zielerreichung Per System mi
2		OEE Jahres Durchschnitt	Zielerreichung Per System mi
3	Masch 1	149	125
4
5	Masch1		Zielereichung genau: ?
6	Vorgabe Tabelle Zielerreichung		198,5
7	OEE	Zielerreichung
8	30	0
9	45	10
10	60	70
11	85	100
12	100	125
13	150	200

Formeln der Tabelle

Zelle	Formel
C3	=SVERWEIS(B3;A8:B13;2)
C6	=WENN(B3<30;0;WENNFEHLER(INDEX(B8:B14+(B3-A8:A13)*(B9:B14-B8:B13)/(A9:A14-A8:A13); VERGLEICH(B3;A8:A14;1)); 200))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4,8

das ist echt die Hammerformel, ...

23.03.2015 17:34:34

neopa

Hallo Sepp,
... die Du hier gezeigt hast. Knapper und einfacher kann man die Aufgabe nun wirklich nicht mehr lösen. Und mir hast Du damit u.a. INDEX() als noch wertvoller aufgezeigt, als ich die Funktion bisher eh schon gehalten habe. Wieder was gelernt :-)
Gruß Werner
.. , - ...

Zwischenwert Sverweis

Forumthread: Zwischenwert Sverweis

19
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Zwischenwert Sverweis

Forumthread: Zwischenwert Sverweis

19 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu diesem und ähnlichen Themen

19
Beiträge zum Forumthread

Beiträge zu diesem Forumthread