Dear visitor, welcome to Shisha & Wasserpfeifen Forum. If this is your first visit here, please read the Help. It explains in detail how this page works. To use all features of this page, you should consider registering. Please use the registration form, to register here or read more information about the registration process. If you are already registered, please login here.

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

1

Sunday, June 7th 2009, 1:44pm

Excel Help plx.

Folgendes Problem mit Excel:

Ich brauch ne Formel und zwar will ich folgendes machen.

Ich habe zwei Spalten, angenommen von A1-A10 und B1-B10
in jeder Zelle steht n datum, in jeder (20 daten also)

Jetzt will ich unten anzeigen, das alle daten in spalte B gezählt werden sollen,
die größter sind als das datum in spalte A, also das dann da unten steht "8" wenn zB
8 daten in spalte B größer sind als in spalte A.

Und noch eine Umkehrformel, das dann da steht "2" für die 2 Daten die dann zB
kleiner sind als die in spalte A

helium

Professional

Date of registration: Oct 18th 2005

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

2

Sunday, June 7th 2009, 3:36pm

Das geht eindeutig über meine Schulinformatik-Kentnisse hinaus! :D

Quoted

we're trapped in the belly of this horrible machine
and the machine is bleeding to death


Goodspeed You! Black Emperor

http://www.youtube.com/watch?v=qz2Xt3NAPQU

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

3

Sunday, June 7th 2009, 3:40pm

Zwar nicht elegant, aber es geht:

Du machst eine Hilfsspalte

=WENN(B1>$A$X;1;0) (x durch dein gewünschtes Feld ersetzen)
und ziehst das ganze nach durch, also bis
=WENN(B10>$A$X;1;0)
Wenn das Datum größer ist als spuckt die Formel 1 aus, ansonsten 0.

und für die Zählung:

=ZÄHLENWENN(C1:C10;1) (Anzahl Zellen größer)
=ZÄHLENWENN(C1:C10;0) (Anzahl Zellen kleiner)

Vielleicht fällt mir noch was schöneres ein..

Schokokönig

Intermediate

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

4

Sunday, June 7th 2009, 3:54pm

Vielleicht hilft auch www.excelformeln.de

Ich kann damit auch irgendwie nicht umgehen, ich drücke irgendwas und zack sind überall Formeln :D

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

5

Sunday, June 7th 2009, 4:22pm

So, ich hab's in zwei Zeilen:


=ZÄHLENWENN(B1:B10;">="&Ax)
=ZÄHLENWENN(B1:B10;"<"&Ax)

This post has been edited 1 times, last edit by "PeAzZe" (Jun 7th 2009, 4:26pm)


Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

6

Sunday, June 7th 2009, 6:54pm

Quoted

Original von PeAzZe
So, ich hab's in zwei Zeilen:


=ZÄHLENWENN(B1:B10;">="&Ax)
=ZÄHLENWENN(B1:B10;"<"&Ax)


das funzt aber net :(

hab in A1 bis A4 immer den 01.02.2009 eingetragen und in B1 bis B4 gewechselt mit
20.02.2009, 24.02.2009 etc

dann =ZÄHLENWENN(B1:B4;">="&Ax) in nen leeres feld gesetzt Ergebnis 0

und =ZÄHLENWENN(B1:B4;"<"&Ax) in nen leeres feld gesetzt Ergebnis 0

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

7

Sunday, June 7th 2009, 7:50pm

Es reicht, wenn du das Datum, nach dem gesucht wird, einmal einträgst, zB in A1

Wenn du Werte B1 bis B4 hast, dann

=ZÄHLENWENN(B1:B4;">="&A1)
=ZÄHLENWENN(B1:B4;"<"&A1)

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

8

Sunday, June 7th 2009, 8:33pm

Ja aber guck:

A1 15.02.09 B1 26.02.09
A2 12.02.09 B2 19.02.09
A3 01.02.09 B3 29.12.08
A4 08.02.09 B4 30.03.09

Jetzt soll unten stehen, wie oft in spalte B das Datum größer ist als in A (hier jetzt 3)
Und ich brauch auch, wie oft das datum in spalte B kleiner ist als in spalte A (hier jetzt 1)

Geht das mit deiner Formel? ich kriegs grad net hin

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

9

Sunday, June 7th 2009, 8:50pm

Achso, du willst den Maximalwert von A haben? Falls ja, dann

Source code

1
=ZÄHLENWENN(B1:B4;">="&MAX(A1:A4))  =ZÄHLENWENN(B1:B4;"<"&MAX(A1:A4))

(blöde Smileys)

This post has been edited 2 times, last edit by "PeAzZe" (Jun 7th 2009, 8:56pm)


Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

10

Sunday, June 7th 2009, 9:00pm



das sollte es erklären, mein problem^^

das wird ne liste mit ca 45 daten, also 2 x 45 daten in den spalten

und ich brauche die zahl, wie oft in der zweiten spalte das datum niedriger ist

und ich brauche die zahl, wie oft in der zweiten spalte das datum größer ist

This post has been edited 1 times, last edit by "Shishawunder" (Jun 7th 2009, 9:02pm)


PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

11

Sunday, June 7th 2009, 9:04pm

Größer oder kleiner als was?..

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

12

Sunday, June 7th 2009, 9:06pm

Quoted

Original von PeAzZe
Größer oder kleiner als was?..


siehste doch auf dem screen, die formel muss gucken, ob das datum in spalte b
größer ist als das in spalte A, für alle daten in spalte B, die größer sind als in spalte A
soll es zählen, sodass am ende unten steht, wie viele daten größer sind als A

und genau so auch umgekehrt

zweite formel soll gucken, wie oft das datum der spalte B kleiner ist als in A und
auch das zählen damit ich das unten stehen hab.

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

13

Sunday, June 7th 2009, 9:19pm

Sorry, aber aus deinem Geschreibsel werde ich nicht schlau.

Soll geprüft werden, ob B1 größer / kleiner A1 ist, also soll zellenweise verglichen werden, oder soll geprüft werden, ob B1 größer als A1,A2,A3 und A4 ist, also Zelle mit Spalte vergleichen?

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

14

Sunday, June 7th 2009, 9:27pm

Es wird immer A1 + B1, A2 + B2 etc verglichen werden

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

15

Sunday, June 7th 2009, 9:33pm

Dafür brauchst du dann eine Hilfsspalte:

=WENN(B1>A1;1;0)
=WENN(B2>A2;1;0)
etc.
und dann nach unten ziehen. Falls Bx > Ax steht dann 1 in der entsprechenden Zelle, ansonsten 0. Um das ganze zu zählen nimmst du wieder (hier ist die Hilfsspalte in Spalte C)
=ZÄHLENWENN(C1:C4;1) (Anzahl der Werte größer)
=ZÄHLENWENN(C1:C4;0) (Anzahl der Werte kleiner)

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

16

Sunday, June 7th 2009, 9:40pm

Quoted

Original von PeAzZe
Dafür brauchst du dann eine Hilfsspalte:

=WENN(B1>A1;1;0)
=WENN(B2>A2;1;0)
etc.
und dann nach unten ziehen. Falls Bx > Ax steht dann 1 in der entsprechenden Zelle, ansonsten 0. Um das ganze zu zählen nimmst du wieder (hier ist die Hilfsspalte in Spalte C)
=ZÄHLENWENN(C1:C4;1) (Anzahl der Werte größer)
=ZÄHLENWENN(C1:C4;0) (Anzahl der Werte kleiner)


verstanden, dann erklär kurz wat hilfsspalte is dann bin ich glücklich :D

PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

17

Sunday, June 7th 2009, 9:43pm

Okay?

helium

Professional

Date of registration: Oct 18th 2005

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

18

Sunday, June 7th 2009, 10:06pm

Wärst schneller fertig gewesen, wenn du die Daten manuell ausgewertet hättest. :D

So genug Troll-Posts für heute!

Quoted

we're trapped in the belly of this horrible machine
and the machine is bleeding to death


Goodspeed You! Black Emperor

http://www.youtube.com/watch?v=qz2Xt3NAPQU

Shishawunder

Großmeister

Date of registration: Mar 6th 2007

First name: Chris

Location: Deutschland

Favorite tobacco: Fakhur, Al Amir, Fumari

Thanks: 66

  • Send private message

member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months member for more than 144 months

19

Sunday, June 7th 2009, 10:07pm

verstanden, wie kann ich die hilfszeile dann verstecken, fürs drucken?

@ vorposter , manuell ist schön und gut, aber wenn man das einmal hat, geht das
so schneller

Außerdem arbeite ich atm nebenher in ner Verwaltung, die wollen alles mit Formeln

This post has been edited 1 times, last edit by "Shishawunder" (Jun 7th 2009, 10:08pm)


PeAzZe

Professional

Date of registration: May 26th 2005

First name: Sebastian

  • Send private message

member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months member for more than 168 months

20

Sunday, June 7th 2009, 10:25pm

Du markierst die gesamte Spalte, rechte Maustaste und dann ist da eine Option 'Ausblenden'