Bedingung in Prozentrechnung

Computer: Software & Programmierung Office Software
#1

Hi Leute!

Ich arbeite mit Excel 2003. In einer Spalte, die als Prozentformel hinterlegt ist, habe ich eine bedingte Formatierung hinterlegt, so dass der größte Wert farbig markiert ist. Die Formel ist an weitere Spalten gekoppelt und wenn in einer Zelle 0 steht, dann ergibt das gleich mal 100 % obwohl das nicht korrekt ist.

Geht es, dass ich die Formel so hinterlege, dass er statt der 0 den letzten Wert einer Zelle in einer Zeile übernimmt der über 0 liegt? Welche Formel muss ich hierzu einstellen?

Die Tabelle sieht etwa so aus (kann leider kein Bild hier einfügen, muss es also beschreiben):

D3 = 113,4
E3 bis J3 stehen weitere Werte
K3 = 109,9
I = -3,09 (Formel:frowning:K3-D3)/ABS(D3))

So sieht das ganze prima aus. Mein Problem:

D4 = 100,2
E4 bis G4 stehen weitere Werte
H4 und K4 steht 0 (manchmal auch nur in K4=0)
I = -100% (Formel wie oben)

Hier ist dann mein Problem! Welche Formel muss ich in I eingeben um dass eine korrekte Prozentzahl ermittelt wird mit dem letzten Wert >0?

Bin über eure Mithilfe sehr dankbar!

#2

Hallo Laetta,

indirekt kannst du hier schon was hochladen.
Dafür gibt es die FAQ:2606
Lade eine Mappe hoch mit dem obersten Hochladlink dort.

Gruß
Reinhard

#3

Hallo,
in einer Zelle 60 % heisst in Excel-lisch
der Zahlenwert ist 0,6
die Formatierung ist Prozent
die Sicht auf die Zelle gaukelt Dir etwas von 60 vor.
Vermutlich kommst Du damit durcheinander.
Gruss Helmut

#4

Hallo,

also hier mal eine Beispieltabelle:
http://www.file-upload.net/download-7446245/Beispiel…

Ich möchte dass der %Wert sich auf die letzte Zahl höher als 0 bezieht und nicht 100% herauskommen.

Wer kann mir die Formel sagen, die ich eingeben muss?

#5

http://www.file-upload.net/download-7446245/Beispiel…
Ich möchte dass der %Wert sich auf die letzte Zahl höher als 0
bezieht und nicht 100% herauskommen.

Hallo Laetta,

wenn keine Werte vorliegen lass die Zelle leer:

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Beispiel.xls]!Tabelle1
 │ A │ B │ C │ D │ E │ F │ G │ H │ I │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 1 │ Wo 1 │ Wo 2 │ Wo 3 │ Wo 4 │ Wo 5 │ Wo 6 │ Wo 7 │ Wo 8 │ %Gew │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 2 │ │ │ │ │ │ │ │ │ │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 3 │ 113,4 │ 112,2 │ 110,9 │ 111,0 │ 109,9 │ 109,7 │ 109,3 │ 109,9 │ -3,09% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 4 │ 74,1 │ 73,4 │ 72,6 │ 72,8 │ 71,0 │ 70,9 │ 71,0 │ 70,7 │ -4,59% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 5 │ 79,1 │ 76,8 │ 75,6 │ 74,4 │ 72,5 │ 72,3 │ 71,3 │ 70,7 │ -10,62% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 6 │ 76,6 │ 75,0 │ 74,8 │ 73,8 │ 71,4 │ 71,4 │ 70,2 │ 69,8 │ -8,88% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 7 │ 71,9 │ 70,3 │ 69,9 │ 69,1 │ 68,7 │ 68,7 │ │ │ -4,45% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 8 │ 84,3 │ 82,5 │ 81,9 │ 81,0 │ 80,8 │ 79,8 │ 79,6 │ 79,6 │ -5,58% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
 9 │ 74,0 │ 72,9 │ 71,7 │ 69,9 │ 69,9 │ 69,5 │ │ 71,2 │ -3,78% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
10 │ 96,4 │ 93,2 │ 92,1 │ 91,2 │ 89,1 │ 88,5 │ 87,1 │ 86,1 │ -10,68% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
11 │ 66,1 │ 65,2 │ 64,7 │ 63,7 │ 62,1 │ 62,1 │ 61,8 │ 61,3 │ -7,26% │
───┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼───────┼─────────┤
12 │ 88,9 │ 86,7 │ 85,6 │ 85,2 │ 85,2 │ 83,8 │ 83,1 │ │ -6,52% │
───┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴───────┴─────────┘
Benutzte Matrixformeln:
I3 : {=INDEX(A3:H3;MAX((A3:H3"")\*SPALTE(A:H)))/A3-1}
I4 : {=INDEX(A4:H4;MAX((A4:H4"")\*SPALTE(A:H)))/A4-1}
I5 : {=INDEX(A5:H5;MAX((A5:H5"")\*SPALTE(A:H)))/A5-1}
usw. in I

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)
Zahlenformate der Zellen im gewählten Bereich:
A1:A12,B1:B12,C1:C12,D1:smiley:12,E1:E12,F1:F12,G1:G12,H1:H12,I1:I2
haben das Zahlenformat: 0,0
I3:I12
haben das Zahlenformat: 0,00%

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#6

Hallo Reinhard,

danke für die Hilfe. Leider funktioniert die von dir angegebene INDEX Formel nicht. Es zeigt mir dann #BEZUG! als Fehlermeldung in der Zelle an. Ich habe lediglich die Zellenbezeichnungen entsprechend der Liste abgeändert statt A3 in D10:K10 und D:K und /D10-1

Die Formatierungen der Zellen D10 bis K10 sind auf Zahl mit einer Kommastelle gesetzt und die Formatierung in L10 auf Prozent mit zwei Dezimalstellen.

Woran kann das liegen?

Danke schonmal!

Benutzte Matrixformeln:
I3 : {=INDEX(A3:H3;MAX((A3:H3"")*SPALTE(A:H)))/A3-1}
I4 : {=INDEX(A4:H4;MAX((A4:H4"")*SPALTE(A:H)))/A4-1}
I5 : {=INDEX(A5:H5;MAX((A5:H5"")*SPALTE(A:H)))/A5-1}
usw. in I

(Matrixformeln nicht mit „Enter“ sondern mit
„Strg+Shift+Enter“ eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von
Excel erzeugt.)
Zahlenformate der Zellen im gewählten Bereich:
A1:A12,B1:B12,C1:C12,D1:smiley:12,E1:E12,F1:F12,G1:G12,H1:H12,I1:I2
haben das Zahlenformat: 0,0
I3:I12
haben das Zahlenformat: 0,00%

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#7

Hallo Laetta,

Leider funktioniert die von dir angegebene INDEX Formel nicht.
Es zeigt mir dann #BEZUG! als Fehlermeldung in der Zelle an.
Ich habe lediglich die
Zellenbezeichnungen entsprechend der Liste abgeändert statt A3
in D10:K10 und D:K und /D10-1

in deiner hochgeladenen Mappe gehen die Daten von A1 bis I12.

Die Formatierungen der Zellen D10 bis K10 sind auf Zahl mit
einer Kommastelle gesetzt und die Formatierung in L10 auf
Prozent mit zwei Dezimalstellen.

J,K,L sind leer.
In I hast du bedingte Formatierung die sich auf L9:L31 bezieht,
macht wenig Sinn wenn L leer ist.

Woran kann das liegen?

Vieles möglich. Lade mal eine Tabelle hoch die zu deinen Aussagen
passt.

Gruß
Reinhard

#8

Hi Reinhard,

hier die vollständige Tabelle wie sie tatsächlich aufgebaut ist. Ich hatte halt bisher nur den Zellenteil zum berechnen rauskopiert:

http://www.file-upload.net/download-7446658/Beispiel…

#9

http://www.file-upload.net/download-7446658/Beispiel…

Hallo Laetta,

jetzt klappt auch deine bed. Formatierung :smile:
Die Prozentwerte habe ich jetzt nicht verifiziert, mach das bitte mal
sicherheitshalber.

Tabellenblatt: C:\DOKUME~1\ich2\LOKALE~1\Temp\[Beispiel\_2.xls]!Tabelle1
 │ J │ K │ L │
───┼─────────┼─────────┼──────────┤
 8 │ Woche 7 │ Woche 8 │ %Verlust │
───┼─────────┼─────────┼──────────┤
 9 │ │ │ │
───┼─────────┼─────────┼──────────┤
10 │ 109,3 │ 109,9 │ -3,09% │
───┼─────────┼─────────┼──────────┤
11 │ 71,0 │ 70,7 │ -4,59% │
───┼─────────┼─────────┼──────────┤
12 │ 71,3 │ 70,7 │ -10,62% │
───┼─────────┼─────────┼──────────┤
13 │ 70,2 │ 69,8 │ -8,88% │
───┼─────────┼─────────┼──────────┤
14 │ │ │ -4,45% │
───┼─────────┼─────────┼──────────┤
15 │ 79,6 │ 79,6 │ -5,58% │
───┼─────────┼─────────┼──────────┤
16 │ 0,0 │ 71,2 │ -3,78% │
───┼─────────┼─────────┼──────────┤
17 │ 87,1 │ 86,1 │ -10,68% │
───┼─────────┼─────────┼──────────┤
18 │ 61,8 │ 61,3 │ -7,26% │
───┼─────────┼─────────┼──────────┤
19 │ 83,1 │ │ -6,52% │
───┼─────────┼─────────┼──────────┤
20 │ 59,7 │ 59,1 │ -5,29% │
───┼─────────┼─────────┼──────────┤
21 │ 87,5 │ 87,3 │ -4,49% │
───┼─────────┼─────────┼──────────┤
22 │ 97,3 │ 97,2 │ -5,72% │
───┼─────────┼─────────┼──────────┤
23 │ 93,9 │ 94,9 │ -3,06% │
───┼─────────┼─────────┼──────────┤
24 │ 69,1 │ │ -2,95% │
───┼─────────┼─────────┼──────────┤
25 │ 84,9 │ 85,1 │ -6,38% │
───┼─────────┼─────────┼──────────┤
26 │ 0,0 │ 70,3 │ -8,22% │
───┼─────────┼─────────┼──────────┤
27 │ 92,3 │ 91,3 │ -9,33% │
───┼─────────┼─────────┼──────────┤
28 │ 107,3 │ 106,9 │ -3,69% │
───┴─────────┴─────────┴──────────┘
Benutzte Matrixformeln:
L10: {=INDEX(A10:K10;MAX((A10:K10"")\*SPALTE(A:K)))/D10-1}
L11: {=INDEX(A11:K11;MAX((A11:K11"")\*SPALTE(A:K)))/D11-1}
L12: {=INDEX(A12:K12;MAX((A12:K12"")\*SPALTE(A:K)))/D12-1}
usw. in L

(Matrixformeln nicht mit "Enter" sondern mit "Strg+Shift+Enter" eingeben.
Die Spezialklammern nicht manuell eingeben, sie werden von Excel erzeugt.)
Zahlenformate der Zellen im gewählten Bereich:
J8:J28,K8:K28,L8:L9
haben das Zahlenformat: 0,0
L10:L28
haben das Zahlenformat: 0,00%

Tabellendarstellung erreicht mit dem Code in FAQ:2363

Gruß
Reinhard

#10

Hi Reinhard,

vielen Dank. Die Formel funktioniert!!! DANKEEEEEEE!!!

#11

Hast du mir auch noch eine Lösung dafür, wenn Woche 1 und ggf. Woche 2 den Wert 0,0 hat? Wenn ich diesen einfach raus lasse kommt die Meldung #DIV/0!

#12

Habe eine Lösung gefunden, allerdings nur mit manueller eingabe je Zelle. Gibt es da etwas was ich in einer Vorlage hinterlegen kann, dass leere Zellen nicht berechnet werden und automatisch die erste Zelle mit Ziffen nach der Spalte D einkalkuliert werden?

#13

Hast du mir auch noch eine Lösung dafür, wenn Woche 1 und ggf.
Woche 2 den Wert 0,0 hat? Wenn ich diesen einfach raus lasse
kommt die Meldung #DIV/0!

Hallo Laetta,

geht auch eine Lösung mit Vba?
Du schreibst dann in L10 nur rein
=Proz(D10:K10)
und kopierst das bis L20.

Um das zu erreichen mußte nur Makros aktivieren beim Mappenstart.
Bin kein Formelpapst, Vba wäre mir lieber als Excelfunktionen.

Gruß
Reinhard