custom formatting

N

Nuhuskr7

I have a gradebook that contains "-" representing assignments earning 0
points because they weren't turned in, and 0 representing assignments earning
0 points that were turned in. How can I continue to view the "-", while
making it worth 0 points in calculation of average, median, etc.?
 
J

JE McGimpsey

One way:

Average:

=SUM(A1:A10)/COUNTA(A1:A10)

Median (array-entered: CTRL-SHIFT-ENTER or CMD-RETURN):

=MEDIAN(IF(A1:A10="-",0,IF(A1:A10<>"",A1:A10)))
 
Top