Excel Mittelwert von nur gefüllten Zellen

%3CLINGO-SUB%20id%3D%22lingo-sub-1599209%22%20slang%3D%22de-DE%22%3EExcel%20Mean%20of%20only%20filled%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599209%22%20slang%3D%22de-DE%22%3E%3CP%3EHello%20dear%20friends%20of%20spreadsheet%20%3CLI-EMOJI%20id%3D%22lia_grinning-face-with-smiling-eyes%22%20title%3D%22%3Agrinning_face_with_smiling_eyes%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20desperately%20need%20your%20help!%20For%20the%20grade%20calculation%20of%20my%20favorite%20students%2C%20I%20created%20a%20table%20in%20Excel%20that%20automatically%20calculates%20the%20grades%20based%20on%20the%20points%20reached...%20So%20far%20I've%20been.%20Now%20I%20would%20like%20to%20have%20the%20average%20of%20all%20grades%20of%20a%20student%20calculated%2C%20but%20I%20just%20can't%20get%20it%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20%20Only%20when%20all%20the%20fields%20are%20filled%2C%20my%20previous%20formula%20calculates%20the%20average%2C%20but%20in%20reality%20not%20every%20student%20writes%20every%20test%2C%20so%20I%20will%20definitely%20have%20empty%20cells.%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%3F%3C%2FP%3E%3CP%3EHere%20are%20my%20previous%20formulas%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22272%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22199.71875px%22%20height%3D%2279%22%3EMeerkats%2015%20-%20Minute%20tests%3C%2FTD%3E%3CTD%20width%3D%22158.65625px%22%3EName%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2269.6875px%22%20height%3D%2232%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3ENo.%3C%2FTD%3E%3CTD%20width%3D%2290.03125px%22%3Eachievable%20points%3C%2FTD%3E%3CTD%20width%3D%2272.640625px%22%3Eachieved%20points%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E%3CFONT%20color%3D%22%230000FF%22%3E%25%3C%2FFONT%3E%3C%2FTD%3E%3CTD%20width%3D%2246.015625px%22%3E%3CFONT%20color%3D%22%23FF0000%22%3ENote%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2269.6875px%22%20height%3D%2223%22%3E12.08.20%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E1%3C%2FTD%3E%3CTD%20width%3D%2290.03125px%22%3E40th%3C%2FTD%3E%3CTD%20width%3D%2272.640625px%22%3E30%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E75%3C%2FTD%3E%3CTD%20width%3D%2246.015625px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2269.6875px%22%20height%3D%2223%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E2%3C%2FTD%3E%3CTD%20width%3D%2290.03125px%22%3E40th%3C%2FTD%3E%3CTD%20width%3D%2272.640625px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2246.015625px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2269.6875px%22%20height%3D%2223%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E3%3C%2FTD%3E%3CTD%20width%3D%2290.03125px%22%3E40th%3C%2FTD%3E%3CTD%20width%3D%2272.640625px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2246.015625px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2269.6875px%22%20height%3D%2223%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E4%3C%2FTD%3E%3CTD%20width%3D%2290.03125px%22%3E40th%3C%2FTD%3E%3CTD%20width%3D%2272.640625px%22%3E%26nbsp%3B%3C%2FTD%3E%3CTD%20width%3D%2240px%22%3E0%3C%2FTD%3E%3CTD%20width%3D%2246.015625px%22%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%3CFONT%20color%3D%22%230000FF%22%3E%25%20field%3A%3C%2FFONT%3E%20%3DD3%2F%24C3*100%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3EGrade%20field%3A%20%3CFONT%20color%3D%22%23000000%22%3E%3DWENN(E3%26gt%3B95.5%3B%22%201%22%3B%20IF(E3%26gt%3B81.5%3B%22%202%22%3B%20IF(E3%26gt%3B66.5%3B%22%203%22%3B%20IF(E3%26gt%3B49.5%3B%22%204%22%3B%20IF(E3%26gt%3B19%2C5%3B%22%205%22%3B%20IF(ISTLEER(D3)%3B%22%22%3B%22%206%22)))))))%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CFONT%20color%3D%22%23000000%22%3ENow%20I%20would%20like%20to%20have%20a%20field%20among%20the%20grades%20with%20the%20grade%20average.%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CFONT%20color%3D%22%23000000%22%3EI%20would%20be%20very%20grateful%20for%20every%20tip!%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20color%3D%22%23FF0000%22%3E%3CFONT%20color%3D%22%23000000%22%3EBest%20regards!!!!!!%3C%2FFONT%3E%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1599209%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%20on%20Mac%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1599277%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Mittelwert%20von%20nur%20gef%C3%BCllten%20Zellen%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1599277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F764715%22%20target%3D%22_blank%22%3E%40Oskar1848%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%C3%84ndere%20die%20Formel%20in%20E3%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DWENNS(E3%26gt%3B95%2C5%3B1%3BE3%26gt%3B81%2C5%3B2%3BE3%26gt%3B66%2C5%3B3%3BE3%26gt%3B49%2C5%3B4%3BE3%26gt%3B19%2C5%3B5%3BD3%3D%22%22%3B%22%22%3BWAHR%3B6)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3EUnd%20dann%20in%20F7%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DMITTELWERT(F3%3AF6)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hallo liebe Freunde der Tabellenkalkulation

 

Ich brauche dringend eure Hilfe! Für die Notenberechnung meiner liebsten Schüler habe ich in Excel eine Tabelle erstellt, die die Noten anhand der erreichten Punkte automatisch berechnet... soweit bin ich schon mal. Jetzt würde ich gerne von allen Noten eines Schülers den Mittelwert errechnen lassen, allerdings bekomme ich das einfach nicht hin Nur wenn alle Felder gefüllt sind, errechnet meine bisherige Formel den Mittelwert, aber in der Realität schreibt nicht jeder Schüler jeden Test mit, somit werde ich definitiv leere Zellen haben. 

Kann mir jemand helfen?

Hier meine bisherigen Formeln:

Erdmännchen 15 - MinutentestsName
DatumNr.erreichbare Punkteerreichte Punkte%Note
12.08.2014030753
 240 0 
 340 0 
 440 0 

%-Feld: =D3/$C3*100

Noten-Feld: =WENN(E3>95,5;"1";WENN(E3>81,5;"2";WENN(E3>66,5;"3";WENN(E3>49,5;"4";WENN(E3>19,5;"5";WENN(ISTLEER(D3);"";"6"))))))

 

Jetzt hätte ich so gerne ein Feld unter den Noten mit dem Notendurchschnitt.

Für jeden Tipp wäre ich sehr dankbar!

Beste Grüße!!!!!!

 

 

 

1 Reply

@Oskar1848 

Ändere die Formel in E3:

=WENNS(E3>95,5;1;E3>81,5;2;E3>66,5;3;E3>49,5;4;E3>19,5;5;D3="";"";WAHR;6)

Und dann in F7:

=MITTELWERT(F3:F6)