MODE function that misses '0' or blanks

[george94]

Hello,

I am a teacher and have a grade tracking grid. We would like to add a column to calculate the mode of all scores for each student.

The grid currently has 6 units of work which each has 4 grades in. I would like to find the mode for all grades whilst dismissing any fields left blank.

Can anybody help?

Thanks in advance!

 

[GTO]

I am not exactly following the layout and have never used MODE(), but does this help?

Excel Workbook
	B	C
3	0
4	0
5	2
6	0
7	0
8	2
9	2
10
11		2
Sheet2

Excel 2010

#VALUE!

Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.

Mark

 

[AliGW]

Sometimes people say MODE when they mean AVERAGE - might be the case here.

 

[pgc01]

Hi George
Welcome to the board

...
I would like to find the mode for all grades whilst dismissing any fields left blank.
...

You can use the MODE() directly. Any fields left blank will be ignored.

 

[pgc01]

Sorry, I saw that in the title you also mention the 0 values.

In that case go with GTO's solution.

 

[george94]

Thanks for the advice so far.

I can confirm that I definitely do mean mode in this instance.

I am sure that would work Mark, however, the cells that I would like to calculate the mode of are separated and when entered it shows that there are too many arguments used.

I would show an image of the spreadsheet, but I am new here and am not sure how to attach an image.

Thanks!

 

[AliGW]

George - grab the ForumTools add-in (link in my signature line) and then add your data here - it's more use than an image, anyway.

 

[george94]

Excel 2016 (Windows) 32 bit
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Unit 1 Grades				Unit 1 Grades					Average		Unit 2 Grades					Average		Unit 3 Grades					Average		Mode
2	5	6	6	7	5	6	6	7				6	6	7	5				6	5	6	7
Sheet1

 

[george94]

WOW!
It is sort of like this... It hasn't copied completely correct.

 

[AliGW]

OK - what's the expected outcome in your example?