Office: (Office 2010) Formel für Lagerreichweite

Hallo Gemeinde,
ich hänge leider die letzten zwei Tage auf der Arbeit an folgendem Problem.

Ich habe in Spalte A2 den Lagerbestand eines Produktes.
In B2, die Verkaufsmengen des aktuellen Monats, in C2 die Verkaufsmengen des nächsten Monats usw. Hier mal kurz dargestellt mit Beispielwerten

A2=Aktueller Lagerbestand
B2=VKMenge Monat1 (400) --> Gesamt 400
C2=VKMenge Monat2 (300) --> Gesamt 700
D2=VKMenge Monat3 (200) --> Gesamt 900
E2=VKMenge Monat4 (400) --> Gesamt 1500
F2=VKMenge Monat5 (800) --> Gesamt 2300
G2=VKMenge Monat6 (200) --> Gesamt 2500
H2=VKMenge Monat7 (100) --> Gesamt 2600
I2=VKMenge Monat8 (400) --> Gesamt 3000

Zelle K2 soll mir nun für unterschiedliche Lagerbestände die Lagerreichweite in Wochen geben. Der Monat wird konstant mit 4 Wochen berechnet.

Wenn A2 = 700 dann K2 = 8 Wochen
Wenn A2 = 800 dann K2 = 10 Wochen
Wenn A2 = 1700 dann K2 = 17 Wochen
Wenn A2 = 2320 dann K2 = 20,4 Wochen (2300 = 5 Monate oder 20 Wochen + 20/200 * 4 = 20,4 Wochen)
Wenn A2 2715 dann K2 = 29,15 Wochen (2600 = 7 Monate oder 28 Wochen + 115/400*4 = 29,15 Wochen)

Leider weiß ich nicht, wie ich das in einer Formel ausdrücken kann. Würde mich über jegliche Hilfe sehr freuen.
Besten Dank im Voraus aus dem nächtlichen Hamburg

:)

 

K2 definierter Name: q bezieht sich auf: =TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1)))
K2 definierter Name: p bezieht sich auf: =VERGLEICH(A2;q)
K2: =4*(WENN(B2>A2;A2/B2;p+(A2-INDEX(q;p))/(INDEX(q;p+1)-INDEX(q;p))))

oder, falls Dir das Definieren von Namen nichts sagt, auch gleich:

K2: =4*(WENN(B2>A2;A2/B2;VERGLEICH(A2;
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1)));))+(A2-INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1))););VERGLEICH(A2;
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1)));))))/(INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1))););VERGLEICH(A2;
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1)));))+1)-INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1))););VERGLEICH(A2;
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN(B2;;;;SPALTE(A1:H1)));))))))

Die Umkehrfunktion ("welche Menge wird in den nächsten 7,5 (=A5) Wochen abgerufen?") lautet:

=SUMMENPRODUKT((A5/4>SPALTE(A:H)-1)*(A5/4-SPALTE(A:H)+1)*(B4:I4-A4:H4))

Die Mengen stehen jetzt in B4:I4, damit A4 leer ist - wichtig!

 

Hallo Lupo,
danke dir erstmal für die schnelle ANtwort. Ich hatte versucht das Problem zu vereinfachen um es Beschreibbar zu machen. Leider hätte ich wohl einige Restriktion nennen müssen:

Es können keine Hilfszeilen oder Hilfswerte eingetragen werden. In der Tabelle befinden sich ca. 500 Zeilen mit jeweils einem Produkt. Eine Hilfszeile pro Artikel würde also ein mega Aufwand sein und die Tabelle unübersichtlich machen. Hast du da eventuell noch ne andere Lösung?

Werde in der Zwischenzeit versuchen dein Ansatz komplett zu verstehen und ggf. umzuschreiben auf meine Bedürfnisse.

LG
buddboy

 

Meine Lösung benötigt nur K2 (nur davon rede ich!). Keine Hilfszelle. Das zeigt wieder mal, dass die Anleitungen nicht befolgt werden, weil man sie nicht versteht und zu faul ist, sie wenigstens auszuprobieren, obwohl ein Antworter sie sogar getestet hat.

Darüber hinaus: Ich habe Dir 2 Lösungen genannt. Die eine direkt, die andere formelverkürzend. Verwechsle defN nicht mit HilfsZ.

 

Hey Lupo,
danke erneut. Den Schuh muss ich mir wohl anziehen. Ich hatte folgenden Abschnitt falsch verstanden „Die Mengen stehen jetzt in B4:I4, damit A4 leer ist - wichtig!“ Fettes sorry: Ich hab in versucht die Formel auf meine Tabelle anzuwenden:

=4*(WENN([@[VK_Total]]>[@[Lager EU_Total]];[@[Lager EU_Total]]/[@[VK_Total]];VERGLEICH([@[Lager EU_Total]];
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1)));))+([@[Lager EU_Total]]-INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1))););VERGLEICH([@[Lager EU_Total]];
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1)));))))/(INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1))););VERGLEICH([@[Lager EU_Total]];
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1)));))+1)-INDEX(
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1))););VERGLEICH([@[Lager EU_Total]];
INDEX(TEILERGEBNIS(9;BEREICH.VERSCHIEBEN([@[VK_Total]];;;;SPALTE(T1:Z1)));))))))


[@[VK_Total]] = B2
[@[Lager EU_Total]] = A2
Die Werte für „VKMenge Monat1“ bis „VKMenge Monat8“ stehen in den Spalten U bis AA. Ich habe daher die Spalten T bis Z oben in die Formel geschrieben. Leider auch hier ohne Erfolg. Den ersten Ansatz klappt auch in der Beispieltabelle. Bei der Übertragung auf meine Tabelle, klappt es aber leider nicht
Wäre dir sehr dankbar, wenn du dir das Ganze nochmal anschauen könntest. Entschuldige nochmal bitte meine vorschnelle Antwort vorhin

 

SPALTE(A2:G2) statt SPALTE(T1:Z1)

Ich sitze noch an einer kürzeren Formel. Mein Gehirn hat im Moment zu wenig Power, muss etwas frühstücken.