ExcelBanter - custom formatting

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - custom formatting (https://www.excelbanter.com/excel-discussion-misc-queries/131391-custom-formatting.html)

Nuhuskr7

custom formatting

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.?

JE McGimpsey

custom formatting

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)))

In article ,
Nuhuskr7 wrote:

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.?

All times are GMT +1. The time now is 01:38 AM.