Formel zu Gross

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo nochmals
Habe die Formel so gebaut, dass alle 20 Blöcke berücksichtigt werden, es ist jedoch so, dass diese Prüfung mit einer anderen Tabelle vreknüpft werden sollte und dabei wird die Formel zu lang..

Tabelle1

	A	B	C	D	E	F	G	H
3
4	Wochen
5	1	2	3	4	5	6	7	8
6
7
8	Treffer
9	1	1	0	0	1	1	0	1
10
11
12	Terminblöcke
13	6	6	8	10	1	2	14	15
14	1		2		3		4
15

Formeln der Tabelle

Zelle	Formel
A9	=WENN(ODER(UND(A$5>=$A$13;A$5<=$B$13); UND(A$5>=$C$13;A$5<=$D$13); UND(A$5>=$E$13;A$5<=$F$13); UND(A$5>=$G$13;A$5<=$H$13); UND(A$5>=$I$13;A$5<=$J$13); UND(A$5>=$K$13;A$5<=$L$13); UND(A$5>=$M$13;A$5<=$N$13); UND(A$5>=$O$13;A$5<=$P$13); UND(A$5>=$Q$13;A$5<=$R$13); UND(A$5>=$S$13;A$5<=$T$13); UND(A$5>=$U$13;A$5<=$V$13); UND(A$5>=$W$13;A$5<=$X$13); UND(A$5>=$Y$13;A$5<=$Z$13); UND(A$5>=$AA$13;A$5<=$AB$13); UND(A$5>=$AC$13;A$5<=$AD$13); UND(A$5>=$AE$13;A$5<=$AF$13); UND(A$5>=$AG$13;A$5<=$AH$13); UND(A$5>=$AI$13;A$5<=$AJ$13); UND(A$5>=$AK$13;A$5<=$AL$13); UND(A$5>=$AM$13;A$5<=$AN$13)); 1;0)
B9	=WENN(ODER(UND(B$5>=$A$13;B$5<=$B$13); UND(B$5>=$C$13;B$5<=$D$13); UND(B$5>=$E$13;B$5<=$F$13); UND(B$5>=$G$13;B$5<=$H$13); UND(B$5>=$I$13;B$5<=$J$13); UND(B$5>=$K$13;B$5<=$L$13); UND(B$5>=$M$13;B$5<=$N$13); UND(B$5>=$O$13;B$5<=$P$13); UND(B$5>=$Q$13;B$5<=$R$13); UND(B$5>=$S$13;B$5<=$T$13); UND(B$5>=$U$13;B$5<=$V$13); UND(B$5>=$W$13;B$5<=$X$13); UND(B$5>=$Y$13;B$5<=$Z$13); UND(B$5>=$AA$13;B$5<=$AB$13); UND(B$5>=$AC$13;B$5<=$AD$13); UND(B$5>=$AE$13;B$5<=$AF$13); UND(B$5>=$AG$13;B$5<=$AH$13); UND(B$5>=$AI$13;B$5<=$AJ$13); UND(B$5>=$AK$13;B$5<=$AL$13); UND(B$5>=$AM$13;B$5<=$AN$13)); 1;0)
C9	=WENN(ODER(UND(C$5>=$A$13;C$5<=$B$13); UND(C$5>=$C$13;C$5<=$D$13); UND(C$5>=$E$13;C$5<=$F$13); UND(C$5>=$G$13;C$5<=$H$13); UND(C$5>=$I$13;C$5<=$J$13); UND(C$5>=$K$13;C$5<=$L$13); UND(C$5>=$M$13;C$5<=$N$13); UND(C$5>=$O$13;C$5<=$P$13); UND(C$5>=$Q$13;C$5<=$R$13); UND(C$5>=$S$13;C$5<=$T$13); UND(C$5>=$U$13;C$5<=$V$13); UND(C$5>=$W$13;C$5<=$X$13); UND(C$5>=$Y$13;C$5<=$Z$13); UND(C$5>=$AA$13;C$5<=$AB$13); UND(C$5>=$AC$13;C$5<=$AD$13); UND(C$5>=$AE$13;C$5<=$AF$13); UND(C$5>=$AG$13;C$5<=$AH$13); UND(C$5>=$AI$13;C$5<=$AJ$13); UND(C$5>=$AK$13;C$5<=$AL$13); UND(C$5>=$AM$13;C$5<=$AN$13)); 1;0)
D9	=WENN(ODER(UND(D$5>=$A$13;D$5<=$B$13); UND(D$5>=$C$13;D$5<=$D$13); UND(D$5>=$E$13;D$5<=$F$13); UND(D$5>=$G$13;D$5<=$H$13); UND(D$5>=$I$13;D$5<=$J$13); UND(D$5>=$K$13;D$5<=$L$13); UND(D$5>=$M$13;D$5<=$N$13); UND(D$5>=$O$13;D$5<=$P$13); UND(D$5>=$Q$13;D$5<=$R$13); UND(D$5>=$S$13;D$5<=$T$13); UND(D$5>=$U$13;D$5<=$V$13); UND(D$5>=$W$13;D$5<=$X$13); UND(D$5>=$Y$13;D$5<=$Z$13); UND(D$5>=$AA$13;D$5<=$AB$13); UND(D$5>=$AC$13;D$5<=$AD$13); UND(D$5>=$AE$13;D$5<=$AF$13); UND(D$5>=$AG$13;D$5<=$AH$13); UND(D$5>=$AI$13;D$5<=$AJ$13); UND(D$5>=$AK$13;D$5<=$AL$13); UND(D$5>=$AM$13;D$5<=$AN$13)); 1;0)
E9	=WENN(ODER(UND(E$5>=$A$13;E$5<=$B$13); UND(E$5>=$C$13;E$5<=$D$13); UND(E$5>=$E$13;E$5<=$F$13); UND(E$5>=$G$13;E$5<=$H$13); UND(E$5>=$I$13;E$5<=$J$13); UND(E$5>=$K$13;E$5<=$L$13); UND(E$5>=$M$13;E$5<=$N$13); UND(E$5>=$O$13;E$5<=$P$13); UND(E$5>=$Q$13;E$5<=$R$13); UND(E$5>=$S$13;E$5<=$T$13); UND(E$5>=$U$13;E$5<=$V$13); UND(E$5>=$W$13;E$5<=$X$13); UND(E$5>=$Y$13;E$5<=$Z$13); UND(E$5>=$AA$13;E$5<=$AB$13); UND(E$5>=$AC$13;E$5<=$AD$13); UND(E$5>=$AE$13;E$5<=$AF$13); UND(E$5>=$AG$13;E$5<=$AH$13); UND(E$5>=$AI$13;E$5<=$AJ$13); UND(E$5>=$AK$13;E$5<=$AL$13); UND(E$5>=$AM$13;E$5<=$AN$13)); 1;0)
F9	=WENN(ODER(UND(F$5>=$A$13;F$5<=$B$13); UND(F$5>=$C$13;F$5<=$D$13); UND(F$5>=$E$13;F$5<=$F$13); UND(F$5>=$G$13;F$5<=$H$13); UND(F$5>=$I$13;F$5<=$J$13); UND(F$5>=$K$13;F$5<=$L$13); UND(F$5>=$M$13;F$5<=$N$13); UND(F$5>=$O$13;F$5<=$P$13); UND(F$5>=$Q$13;F$5<=$R$13); UND(F$5>=$S$13;F$5<=$T$13); UND(F$5>=$U$13;F$5<=$V$13); UND(F$5>=$W$13;F$5<=$X$13); UND(F$5>=$Y$13;F$5<=$Z$13); UND(F$5>=$AA$13;F$5<=$AB$13); UND(F$5>=$AC$13;F$5<=$AD$13); UND(F$5>=$AE$13;F$5<=$AF$13); UND(F$5>=$AG$13;F$5<=$AH$13); UND(F$5>=$AI$13;F$5<=$AJ$13); UND(F$5>=$AK$13;F$5<=$AL$13); UND(F$5>=$AM$13;F$5<=$AN$13)); 1;0)
G9	=WENN(ODER(UND(G$5>=$A$13;G$5<=$B$13); UND(G$5>=$C$13;G$5<=$D$13); UND(G$5>=$E$13;G$5<=$F$13); UND(G$5>=$G$13;G$5<=$H$13); UND(G$5>=$I$13;G$5<=$J$13); UND(G$5>=$K$13;G$5<=$L$13); UND(G$5>=$M$13;G$5<=$N$13); UND(G$5>=$O$13;G$5<=$P$13); UND(G$5>=$Q$13;G$5<=$R$13); UND(G$5>=$S$13;G$5<=$T$13); UND(G$5>=$U$13;G$5<=$V$13); UND(G$5>=$W$13;G$5<=$X$13); UND(G$5>=$Y$13;G$5<=$Z$13); UND(G$5>=$AA$13;G$5<=$AB$13); UND(G$5>=$AC$13;G$5<=$AD$13); UND(G$5>=$AE$13;G$5<=$AF$13); UND(G$5>=$AG$13;G$5<=$AH$13); UND(G$5>=$AI$13;G$5<=$AJ$13); UND(G$5>=$AK$13;G$5<=$AL$13); UND(G$5>=$AM$13;G$5<=$AN$13)); 1;0)
H9	=WENN(ODER(UND(H$5>=$A$13;H$5<=$B$13); UND(H$5>=$C$13;H$5<=$D$13); UND(H$5>=$E$13;H$5<=$F$13); UND(H$5>=$G$13;H$5<=$H$13); UND(H$5>=$I$13;H$5<=$J$13); UND(H$5>=$K$13;H$5<=$L$13); UND(H$5>=$M$13;H$5<=$N$13); UND(H$5>=$O$13;H$5<=$P$13); UND(H$5>=$Q$13;H$5<=$R$13); UND(H$5>=$S$13;H$5<=$T$13); UND(H$5>=$U$13;H$5<=$V$13); UND(H$5>=$W$13;H$5<=$X$13); UND(H$5>=$Y$13;H$5<=$Z$13); UND(H$5>=$AA$13;H$5<=$AB$13); UND(H$5>=$AC$13;H$5<=$AD$13); UND(H$5>=$AE$13;H$5<=$AF$13); UND(H$5>=$AG$13;H$5<=$AH$13); UND(H$5>=$AI$13;H$5<=$AJ$13); UND(H$5>=$AK$13;H$5<=$AL$13); UND(H$5>=$AM$13;H$5<=$AN$13)); 1;0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

	A	B	C	D	E	F	G	H
3
4	Wochen
5	1	2	3	4	5	6	7	8
6
7
8	Treffer
9	1	1	0	0	1	1	0	1
10
11
12	Terminblöcke
13	6	6	8	10	1	2	14	15
14	1		2		3		4
15

Formeln der Tabelle

Zelle	Formel
A9	=WENN(ODER(UND(A$5>=$A$13;A$5<=$B$13); UND(A$5>=$C$13;A$5<=$D$13); UND(A$5>=$E$13;A$5<=$F$13); UND(A$5>=$G$13;A$5<=$H$13); UND(A$5>=$I$13;A$5<=$J$13); UND(A$5>=$K$13;A$5<=$L$13); UND(A$5>=$M$13;A$5<=$N$13); UND(A$5>=$O$13;A$5<=$P$13); UND(A$5>=$Q$13;A$5<=$R$13); UND(A$5>=$S$13;A$5<=$T$13); UND(A$5>=$U$13;A$5<=$V$13); UND(A$5>=$W$13;A$5<=$X$13); UND(A$5>=$Y$13;A$5<=$Z$13); UND(A$5>=$AA$13;A$5<=$AB$13); UND(A$5>=$AC$13;A$5<=$AD$13); UND(A$5>=$AE$13;A$5<=$AF$13); UND(A$5>=$AG$13;A$5<=$AH$13); UND(A$5>=$AI$13;A$5<=$AJ$13); UND(A$5>=$AK$13;A$5<=$AL$13); UND(A$5>=$AM$13;A$5<=$AN$13)); 1;0)
B9	=WENN(ODER(UND(B$5>=$A$13;B$5<=$B$13); UND(B$5>=$C$13;B$5<=$D$13); UND(B$5>=$E$13;B$5<=$F$13); UND(B$5>=$G$13;B$5<=$H$13); UND(B$5>=$I$13;B$5<=$J$13); UND(B$5>=$K$13;B$5<=$L$13); UND(B$5>=$M$13;B$5<=$N$13); UND(B$5>=$O$13;B$5<=$P$13); UND(B$5>=$Q$13;B$5<=$R$13); UND(B$5>=$S$13;B$5<=$T$13); UND(B$5>=$U$13;B$5<=$V$13); UND(B$5>=$W$13;B$5<=$X$13); UND(B$5>=$Y$13;B$5<=$Z$13); UND(B$5>=$AA$13;B$5<=$AB$13); UND(B$5>=$AC$13;B$5<=$AD$13); UND(B$5>=$AE$13;B$5<=$AF$13); UND(B$5>=$AG$13;B$5<=$AH$13); UND(B$5>=$AI$13;B$5<=$AJ$13); UND(B$5>=$AK$13;B$5<=$AL$13); UND(B$5>=$AM$13;B$5<=$AN$13)); 1;0)
C9	=WENN(ODER(UND(C$5>=$A$13;C$5<=$B$13); UND(C$5>=$C$13;C$5<=$D$13); UND(C$5>=$E$13;C$5<=$F$13); UND(C$5>=$G$13;C$5<=$H$13); UND(C$5>=$I$13;C$5<=$J$13); UND(C$5>=$K$13;C$5<=$L$13); UND(C$5>=$M$13;C$5<=$N$13); UND(C$5>=$O$13;C$5<=$P$13); UND(C$5>=$Q$13;C$5<=$R$13); UND(C$5>=$S$13;C$5<=$T$13); UND(C$5>=$U$13;C$5<=$V$13); UND(C$5>=$W$13;C$5<=$X$13); UND(C$5>=$Y$13;C$5<=$Z$13); UND(C$5>=$AA$13;C$5<=$AB$13); UND(C$5>=$AC$13;C$5<=$AD$13); UND(C$5>=$AE$13;C$5<=$AF$13); UND(C$5>=$AG$13;C$5<=$AH$13); UND(C$5>=$AI$13;C$5<=$AJ$13); UND(C$5>=$AK$13;C$5<=$AL$13); UND(C$5>=$AM$13;C$5<=$AN$13)); 1;0)
D9	=WENN(ODER(UND(D$5>=$A$13;D$5<=$B$13); UND(D$5>=$C$13;D$5<=$D$13); UND(D$5>=$E$13;D$5<=$F$13); UND(D$5>=$G$13;D$5<=$H$13); UND(D$5>=$I$13;D$5<=$J$13); UND(D$5>=$K$13;D$5<=$L$13); UND(D$5>=$M$13;D$5<=$N$13); UND(D$5>=$O$13;D$5<=$P$13); UND(D$5>=$Q$13;D$5<=$R$13); UND(D$5>=$S$13;D$5<=$T$13); UND(D$5>=$U$13;D$5<=$V$13); UND(D$5>=$W$13;D$5<=$X$13); UND(D$5>=$Y$13;D$5<=$Z$13); UND(D$5>=$AA$13;D$5<=$AB$13); UND(D$5>=$AC$13;D$5<=$AD$13); UND(D$5>=$AE$13;D$5<=$AF$13); UND(D$5>=$AG$13;D$5<=$AH$13); UND(D$5>=$AI$13;D$5<=$AJ$13); UND(D$5>=$AK$13;D$5<=$AL$13); UND(D$5>=$AM$13;D$5<=$AN$13)); 1;0)
E9	=WENN(ODER(UND(E$5>=$A$13;E$5<=$B$13); UND(E$5>=$C$13;E$5<=$D$13); UND(E$5>=$E$13;E$5<=$F$13); UND(E$5>=$G$13;E$5<=$H$13); UND(E$5>=$I$13;E$5<=$J$13); UND(E$5>=$K$13;E$5<=$L$13); UND(E$5>=$M$13;E$5<=$N$13); UND(E$5>=$O$13;E$5<=$P$13); UND(E$5>=$Q$13;E$5<=$R$13); UND(E$5>=$S$13;E$5<=$T$13); UND(E$5>=$U$13;E$5<=$V$13); UND(E$5>=$W$13;E$5<=$X$13); UND(E$5>=$Y$13;E$5<=$Z$13); UND(E$5>=$AA$13;E$5<=$AB$13); UND(E$5>=$AC$13;E$5<=$AD$13); UND(E$5>=$AE$13;E$5<=$AF$13); UND(E$5>=$AG$13;E$5<=$AH$13); UND(E$5>=$AI$13;E$5<=$AJ$13); UND(E$5>=$AK$13;E$5<=$AL$13); UND(E$5>=$AM$13;E$5<=$AN$13)); 1;0)
F9	=WENN(ODER(UND(F$5>=$A$13;F$5<=$B$13); UND(F$5>=$C$13;F$5<=$D$13); UND(F$5>=$E$13;F$5<=$F$13); UND(F$5>=$G$13;F$5<=$H$13); UND(F$5>=$I$13;F$5<=$J$13); UND(F$5>=$K$13;F$5<=$L$13); UND(F$5>=$M$13;F$5<=$N$13); UND(F$5>=$O$13;F$5<=$P$13); UND(F$5>=$Q$13;F$5<=$R$13); UND(F$5>=$S$13;F$5<=$T$13); UND(F$5>=$U$13;F$5<=$V$13); UND(F$5>=$W$13;F$5<=$X$13); UND(F$5>=$Y$13;F$5<=$Z$13); UND(F$5>=$AA$13;F$5<=$AB$13); UND(F$5>=$AC$13;F$5<=$AD$13); UND(F$5>=$AE$13;F$5<=$AF$13); UND(F$5>=$AG$13;F$5<=$AH$13); UND(F$5>=$AI$13;F$5<=$AJ$13); UND(F$5>=$AK$13;F$5<=$AL$13); UND(F$5>=$AM$13;F$5<=$AN$13)); 1;0)
G9	=WENN(ODER(UND(G$5>=$A$13;G$5<=$B$13); UND(G$5>=$C$13;G$5<=$D$13); UND(G$5>=$E$13;G$5<=$F$13); UND(G$5>=$G$13;G$5<=$H$13); UND(G$5>=$I$13;G$5<=$J$13); UND(G$5>=$K$13;G$5<=$L$13); UND(G$5>=$M$13;G$5<=$N$13); UND(G$5>=$O$13;G$5<=$P$13); UND(G$5>=$Q$13;G$5<=$R$13); UND(G$5>=$S$13;G$5<=$T$13); UND(G$5>=$U$13;G$5<=$V$13); UND(G$5>=$W$13;G$5<=$X$13); UND(G$5>=$Y$13;G$5<=$Z$13); UND(G$5>=$AA$13;G$5<=$AB$13); UND(G$5>=$AC$13;G$5<=$AD$13); UND(G$5>=$AE$13;G$5<=$AF$13); UND(G$5>=$AG$13;G$5<=$AH$13); UND(G$5>=$AI$13;G$5<=$AJ$13); UND(G$5>=$AK$13;G$5<=$AL$13); UND(G$5>=$AM$13;G$5<=$AN$13)); 1;0)
H9	=WENN(ODER(UND(H$5>=$A$13;H$5<=$B$13); UND(H$5>=$C$13;H$5<=$D$13); UND(H$5>=$E$13;H$5<=$F$13); UND(H$5>=$G$13;H$5<=$H$13); UND(H$5>=$I$13;H$5<=$J$13); UND(H$5>=$K$13;H$5<=$L$13); UND(H$5>=$M$13;H$5<=$N$13); UND(H$5>=$O$13;H$5<=$P$13); UND(H$5>=$Q$13;H$5<=$R$13); UND(H$5>=$S$13;H$5<=$T$13); UND(H$5>=$U$13;H$5<=$V$13); UND(H$5>=$W$13;H$5<=$X$13); UND(H$5>=$Y$13;H$5<=$Z$13); UND(H$5>=$AA$13;H$5<=$AB$13); UND(H$5>=$AC$13;H$5<=$AD$13); UND(H$5>=$AE$13;H$5<=$AF$13); UND(H$5>=$AG$13;H$5<=$AH$13); UND(H$5>=$AI$13;H$5<=$AJ$13); UND(H$5>=$AK$13;H$5<=$AL$13); UND(H$5>=$AM$13;H$5<=$AN$13)); 1;0)

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

Beliebteste Forumthreads (12 Monate)

6 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

6
Beiträge zum Forumthread

Beiträge zu diesem Forumthread