Bug 121499 - Wish: Add Number format for representation of school grades (Calc)
Summary: Wish: Add Number format for representation of school grades (Calc)
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: All All
: lowest enhancement
Assignee: Not Assigned
Depends on:
Blocks: Number-Format Calc-Enhancements
  Show dependency treegraph
Reported: 2018-11-18 20:07 UTC by Mike A. Fargo
Modified: 2018-12-10 12:17 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Note You need to log in before you can comment on or make changes to this bug.
Description Mike A. Fargo 2018-11-18 20:07:59 UTC
I bet that dozens of teachers will switch over to LibreOffice as soon as Calc can cope with grades as follows:

Value - German/English Representation (Shown)
0.67 - 1+/A+
1 - 1/A
1.33 - 1-/A-
1.67 - 2+/B+
2 - 2/B
5 - 5
5.33 - 5
[5.67 - 6+]

I tried to create such representation as custom format, but it doesn't work out.

Steps to Reproduce:
Entering 1- as number will interpret it as negative.
But to discriminate 1+ from 1, one has to switch to text format.

Actual Results:
Text format: This takes all chances to calculate straight forward. Plus it tends to add + or minus (auto completion) where it is not wanted.

Expected Results:
cf. e.g. https://en.wikipedia.org/wiki/Academic_grading_in_Germany

Reproducible: Always

User Profile Reset: Yes

Additional Info:
Comment 1 Mike A. Fargo 2018-11-18 20:18:16 UTC
I suggest to, first, collect further information about international grading systems, before implementing any specific. BUT, please, enable custom format evaluation to empower the user to define their individual grading system.

In most cases, one would get away with some kind of enumeration table that could be defined in the format string (like in a cref table). 

Yet another common grading system is:

++ 1
+ 2
o 3
- 4
-- 5

with possible half steps like

o+ 2.5
o- 3.5
Comment 2 Joel Madero 2018-11-20 07:48:44 UTC
I am going to push this to NEW only because the threshold for enhancements is so low. That being said, I seriously doubt this would ever be implemented and I would highly doubt the statement of "dozens of teachers will switch" - that's nonsense and we all know it (it's an attempt to push a pet wishlist not a reality of the world).

That being said, if some developer at some point wants to tackle this - good luck. Else, it will sit as a low priority enhancement for years to come.

@Mike - really no offense, just this is a super niche request and I really don't think any developer on their own time is going to tackle such a specific request that will impact a tiny fraction of our user base. If you and these dozens of teachers you think would switch want to fund an enhancement (I'd say likely in the thousands of euros for the time needed) then feel free to shoot me an email and I will get you in touch with people who may be able to assist.
Comment 3 Eike Rathke 2018-12-10 12:17:08 UTC
Such things just can't be expressed by the common number format codes, which have to stay somewhat compatible with what Excel uses, so if we'd come up with an own syntax there it must be clear that it could never be saved to Excel file formats. I doubt that dozens of teachers would accept that..

Also, you already gave two different examples of grading systems. There are quite a few more in the world, see the wiki page you cited. So that would have to be some free form associative array with range rules. Or something completely different.

Anyone interested in this is better off with an Add-In function or extension that implements the desired functionality than a cumbersome drilling of number format codes.

Apart from that, the first original request's example can be easily done using VLOOKUP() with range lookup, i.e. in B1:B16 have the sorted numbers {0.67,1,1.33,...,6} and in C1:C16 have the associated grades {"1+","1","1-",...,"6"}, then the formula =VLOOKUP(D1;B1:C16;2;TRUE()) yields the grade of the value in D1.