MODE function that misses '0' or blanks

george94

New Member
Joined
Dec 15, 2016
Messages
9
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!
:)
 
I would like it to take a mode of all the grades (not including the averages, which as you can see are not there anyway). As you can see, this data has no blank cells, whereas I do have other data that does. I would like it to ignore blank cells.

For some reason it has only added the unit grade header to one cell each when it should be spread across all grades.

I really appreciate this!
 
Upvote 0

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Great, thanks, but what is the expected result for the example you have given?

By the way, merged cells are not a great idea, although they look nice. Is this dataset going to grow, or will it be limited to the assessments listed?
 
Last edited:
Upvote 0
Not very au fait with the MODE thingy, but is it one of these you need?

=MODE.SNGL(A2:D2,E2:H2,L2:O2,S2:V2)

=MODE.MULT(A2:D2,E2:H2,L2:O2,S2:V2)

I'm a teacher, too, but languages are my thing. :)
 
Upvote 0
Neither of them seem to be working :/

I am probably going a really round a bout way of doing this - I do appologise.

Below is the exact grid and I would like in the MODE column the mode of all cells below where all the LC1s, LC2s, LC3s, LC4s, LC5s, LC6s are. As you can see, most of these are yet to be filled in, so I would like to ignore the blank cells.


Excel 2016 (Windows) 32 bit
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBCBD
1
2LC1LC1LC1LC1LC2LC2LC2LC2LC3LC3LC3LC3LC4LC4LC4LC4LC5LC5LC5LC5LC6LC6LC6LC6
3Student NameGenderGroupPPIEALFSMLACSENTeacherH/M/LAPSA8 TargetPathwayPlanningCarrying OutOutcomeEvaluationUnit GradeProgressPlanningCarrying OutOutcomeEvaluationUnit GradeProgressPlanningCarrying OutOutcomeEvaluationUnit GradeProgressPlanningCarrying OutOutcomeEvaluationUnit GradeProgressPlanningCarrying OutOutcomeEvaluationUnit GradeProgressPlanningCarrying OutOutcomeEvaluationUnit GradeProgressMODE
4
5namesF9D/Co 16/17CLWM7288817881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
6namesF9D/Co 16/17CLWM7287817981#DIV/0!#DIV/0!#DIV/0!#DIV/0!
7namesF9D/Co 16/17CLWM72635-27670#DIV/0!#DIV/0!#DIV/0!#DIV/0!
8namesF9D/Co 16/17CLWH7267707881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
9namesM9D/Co 16/17CLWH7268706870#DIV/0!#DIV/0!#DIV/0!#DIV/0!
10namesF9D/Co 16/17CLWH727707881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
11namesM9D/Co 16/17CLWM72354-37981#DIV/0!#DIV/0!#DIV/0!#DIV/0!
12namesM9D/Co 16/17CLWM7267707881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
13namesF9D/Co 16/17CLWH83836-27880#DIV/0!#DIV/0!#DIV/0!#DIV/0!
14namesM9D/Co 16/17CLWM7278817770#DIV/0!#DIV/0!#DIV/0!#DIV/0!
15namesF9D/Co 16/17CLWM7277705870#DIV/0!#DIV/0!#DIV/0!#DIV/0!
16namesF9D/Co 16/17CLWM72645-27770#DIV/0!#DIV/0!#DIV/0!#DIV/0!
17namesF9D/Co 16/17CLWM83375-3687-1#DIV/0!#DIV/0!#DIV/0!#DIV/0!
18namesM9D/Co 16/17CLWH726770486-1#DIV/0!#DIV/0!#DIV/0!#DIV/0!
19namesF9D/Co 16/17YCLWM7288817881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
20namesF9D/Co 16/17CLWM83566-2880#DIV/0!#DIV/0!#DIV/0!#DIV/0!
21namesM9D/Co 16/17CLWH727670756-1#DIV/0!#DIV/0!#DIV/0!#DIV/0!
22namesM9D/Co 16/17CLWM72656-1635-2#DIV/0!#DIV/0!#DIV/0!#DIV/0!
23namesM9D/Co 16/17CLWM72666-17981#DIV/0!#DIV/0!#DIV/0!#DIV/0!
24namesM9D/Co 16/17YCLWM7244-366-1#DIV/0!#DIV/0!#DIV/0!#DIV/0!
25namesF9D/Co 16/17CLWM7244-3666-1#DIV/0!#DIV/0!#DIV/0!#DIV/0!
26namesM9D/Co 16/17CLWL72656-1545-2#DIV/0!#DIV/0!#DIV/0!#DIV/0!
27namesF9D/Co 16/17CLWL7278817981#DIV/0!#DIV/0!#DIV/0!#DIV/0!
28namesF9D/Co 16/17CLWL72656-1881#DIV/0!#DIV/0!#DIV/0!#DIV/0!
Sheet3
Cell Formulas
RangeFormula
N2LC1
N3Planning
N58
N68
N76
N86
N96
N113
N126
N138
N147
N157
N166
N173
N186
N198
N205
N217
N226
N236
N244
N254
N266
N277
N286
O2LC1
O3Carrying Out
P2LC1
P3Outcome
P58
P67
P73
P87
P98
P107
P115
P127
P133
P148
P157
P164
P177
P187
P198
P206
P216
P225
P236
P265
P278
P285
Q2LC1
Q3Evaluation
U2LC2
U3Planning
U57
U67
U77
U87
U96
U107
U117
U127
U137
U147
U155
U167
U176
U184
U197
U208
U217
U226
U237
U246
U256
U265
U277
U288
V2LC2
V3Carrying Out
V58
V69
V76
V88
V98
V108
V119
V128
V138
V147
V158
V167
V178
V188
V198
V215
V223
V239
V256
V264
V279
W2LC2
W3Outcome
X2LC2
X3Evaluation
AB2LC3
AB3Planning
AC2LC3
AC3Carrying Out
AD2LC3
AD3Outcome
AE2LC3
AE3Evaluation
AI2LC4
AI3Planning
AJ2LC4
AJ3Carrying Out
AK2LC4
AK3Outcome
AL2LC4
AL3Evaluation
AP2LC5
AP3Planning
AQ2LC5
AQ3Carrying Out
AR2LC5
AR3Outcome
AS2LC5
AS3Evaluation
AW2LC6
AW3Planning
AX2LC6
AX3Carrying Out
AY2LC6
AY3Outcome
AZ2LC6
AZ3Evaluation
A3Student Name
A5names
A6names
A7names
A8names
A9names
A10names
A11names
A12names
A13names
A14names
A15names
A16names
A17names
A18names
A19names
A20names
A21names
A22names
A23names
A24names
A25names
A26names
A27names
A28names
B3Gender
B5F
B6F
B7F
B8F
B9M
B10F
B11M
B12M
B13F
B14M
B15F
B16F
B17F
B18M
B19F
B20F
B21M
B22M
B23M
B24M
B25F
B26M
B27F
B28F
C3Group
C59D/Co 16/17
C69D/Co 16/17
C79D/Co 16/17
C89D/Co 16/17
C99D/Co 16/17
C109D/Co 16/17
C119D/Co 16/17
C129D/Co 16/17
C139D/Co 16/17
C149D/Co 16/17
C159D/Co 16/17
C169D/Co 16/17
C179D/Co 16/17
C189D/Co 16/17
C199D/Co 16/17
C209D/Co 16/17
C219D/Co 16/17
C229D/Co 16/17
C239D/Co 16/17
C249D/Co 16/17
C259D/Co 16/17
C269D/Co 16/17
C279D/Co 16/17
C289D/Co 16/17
D3PPI
D19Y
D24Y
E3EAL
F3FSM
G3LAC
H3SEN
I3Teacher
I5CLW
I6CLW
I7CLW
I8CLW
I9CLW
I10CLW
I11CLW
I12CLW
I13CLW
I14CLW
I15CLW
I16CLW
I17CLW
I18CLW
I19CLW
I20CLW
I21CLW
I22CLW
I23CLW
I24CLW
I25CLW
I26CLW
I27CLW
I28CLW
J3H/M/LAPS
J5M
J6M
J7M
J8H
J9H
J10H
J11M
J12M
J13H
J14M
J15M
J16M
J17M
J18H
J19M
J20M
J21H
J22M
J23M
J24M
J25M
J26L
J27L
J28L
K3A8 Target
K57
K67
K77
K87
K97
K107
K117
K127
K138
K147
K157
K167
K178
K187
K197
K208
K217
K227
K237
K247
K257
K267
K277
K287
L3Pathway
L52
L62
L72
L82
L92
L102
L112
L122
L133
L142
L152
L162
L173
L182
L192
L203
L212
L222
L232
L242
L252
L262
L272
L282
R3Unit Grade
R58
R68
R75
R87
R97
R107
R114
R127
R136
R148
R157
R165
R175
R187
R198
R206
R217
R226
R236
R244
R254
R266
R278
R286
S3Progress
S51
S61
S7-2
S80
S90
S100
S11-3
S120
S13-2
S141
S150
S16-2
S17-3
S180
S191
S20-2
S210
S22-1
S23-1
S24-3
S25-3
S26-1
S271
S28-1
Y3Unit Grade
Y58
Y68
Y77
Y88
Y97
Y108
Y118
Y128
Y138
Y147
Y157
Y167
Y177
Y186
Y198
Y208
Y216
Y225
Y238
Y246
Y256
Y265
Y278
Y288
Z3Progress
Z51
Z61
Z70
Z81
Z90
Z101
Z111
Z121
Z130
Z140
Z150
Z160
Z17-1
Z18-1
Z191
Z200
Z21-1
Z22-2
Z231
Z24-1
Z25-1
Z26-2
Z271
Z281
AF3Unit Grade
AG3Progress
AG5#DIV/0!
AG6#DIV/0!
AG7#DIV/0!
AG8#DIV/0!
AG9#DIV/0!
AG10#DIV/0!
AG11#DIV/0!
AG12#DIV/0!
AG13#DIV/0!
AG14#DIV/0!
AG15#DIV/0!
AG16#DIV/0!
AG17#DIV/0!
AG18#DIV/0!
AG19#DIV/0!
AG20#DIV/0!
AG21#DIV/0!
AG22#DIV/0!
AG23#DIV/0!
AG24#DIV/0!
AG25#DIV/0!
AG26#DIV/0!
AG27#DIV/0!
AG28#DIV/0!
AM3Unit Grade
AN3Progress
AN5#DIV/0!
AN6#DIV/0!
AN7#DIV/0!
AN8#DIV/0!
AN9#DIV/0!
AN10#DIV/0!
AN11#DIV/0!
AN12#DIV/0!
AN13#DIV/0!
AN14#DIV/0!
AN15#DIV/0!
AN16#DIV/0!
AN17#DIV/0!
AN18#DIV/0!
AN19#DIV/0!
AN20#DIV/0!
AN21#DIV/0!
AN22#DIV/0!
AN23#DIV/0!
AN24#DIV/0!
AN25#DIV/0!
AN26#DIV/0!
AN27#DIV/0!
AN28#DIV/0!
AT3Unit Grade
AU3Progress
AU5#DIV/0!
AU6#DIV/0!
AU7#DIV/0!
AU8#DIV/0!
AU9#DIV/0!
AU10#DIV/0!
AU11#DIV/0!
AU12#DIV/0!
AU13#DIV/0!
AU14#DIV/0!
AU15#DIV/0!
AU16#DIV/0!
AU17#DIV/0!
AU18#DIV/0!
AU19#DIV/0!
AU20#DIV/0!
AU21#DIV/0!
AU22#DIV/0!
AU23#DIV/0!
AU24#DIV/0!
AU25#DIV/0!
AU26#DIV/0!
AU27#DIV/0!
AU28#DIV/0!
BA3Unit Grade
BB3Progress
BB5#DIV/0!
BB6#DIV/0!
BB7#DIV/0!
BB8#DIV/0!
BB9#DIV/0!
BB10#DIV/0!
BB11#DIV/0!
BB12#DIV/0!
BB13#DIV/0!
BB14#DIV/0!
BB15#DIV/0!
BB16#DIV/0!
BB17#DIV/0!
BB18#DIV/0!
BB19#DIV/0!
BB20#DIV/0!
BB21#DIV/0!
BB22#DIV/0!
BB23#DIV/0!
BB24#DIV/0!
BB25#DIV/0!
BB26#DIV/0!
BB27#DIV/0!
BB28#DIV/0!
BD3MODE
 
Upvote 0
George - you still haven't said what your expected outcome would be for the first example given. We really don't need the whole grid - just your expectations. We can work the rest out from that. :)
 
Upvote 0
From the original example I would expect the outcome of mode to be 6. :)

Hi again,

If you mean from Post #8, there appear to be only 4 non-contiguous ranges. MODE() will take up to 255 I believe (from Help). What are we missing?

Mark
 
Upvote 0
It for some reason did not work when I tried before, but now it is working! Thank you so much for your help. I do really appreciate it! :) :)
 
Upvote 0

Forum statistics

Threads
1,213,554
Messages
6,114,280
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top