Created attachment 114905 [details]
Gnumeric SI units.png

Hello OfficeUser,

Thanks for your report. In order to catch interest of a developer, you need to give few more details:
- a screenshot in English would be preferred
- a list of SI units is necessary to be established
- a list of multiplier (k, M, G, T, P,..., m, µ, n, p, f...) is necessary, as well as their equivalent in all language (non-latin)
Some other precisions are also required:
- what should be displayed if value is higher than 1000 times the largest multiplier?
- what should be displayed if value is less than 1/1000 times the smallest multiplier?
- mass (kg) is a special case and should be detailed: what should be displayed for 100g?
- is the list of SI units editable?
- are combinations of SI units allowed? For instance, speed is in m/s
This last point seems difficult to tackle, because the list will be endless if combinations of units are allowed. On the other hand, almost all my units in physics or chemistry are combinations of units. Among the 29 units showed on your screenshot, I only use 9.

(In reply to Laurent BP from comment #2)
> Hello OfficeUser,
> 
> Thanks for your report. In order to catch interest of a developer, you need
> to give few more details:

[snip]

Status -> NEEDINFO

The unit verification feature currently being developed allows for the use of _any_ physical units (including SI units) - in fact it allows typing the unit as part of data entry (with Calc then doing the necessary splitting into value and unit, meaning that the cell can be used in formulas as expected).

If that gets merged then having a separate unit selector in the formatting dialog would probably be obsolete? (Although even right now, it's possible to enter units as part of a custom format through the format dialog - that's admittedly quite unobvious, and probably why Gnumeric offers the explicit unit formatting.)

(Please note: the unit verification feature is currently on a feature branch, and may or may not be merged into master anytime soon, or in the more distant future.)


If however the unit verification doesn't get merged, then this would be a nice feature to have (or it could even complement the unit verification feature) however we'd definitely need some more clarification on the questions that Laurent brought up (which units, what combinations etc etc).

nothing changed since last year, so still confirmed

Created attachment 127624 [details]
Draft of detailed implementation of SI units in number format

(In reply to Andrzej Hunt from comment #4)
> The unit verification feature currently being developed...
Is this feature still in development?

In the meantime, I had a look at what Gnumeric proposed and, mentioned in comment 2, the main limitation is that only simple base units are available. Something like area cannot be used. Moreover 1000 m² is NOT 1 km² ;-)

I wrote a first draft of how such feature could be implemented. Compared to Gnumeric implementation, I think it would be more useful if user can complete unit with:
- power of first unit. It is necessary to take it into account to chose multiplier
- complement of unit such as "/s"

There are still unresolved questions, but main one is:
- should first unit be chosen in a limited list or freely typed by user?

A first step may be to simply use multiplier without unit.

Agreed - the multipliers with no units would be very, very nice, and should be relatively simple to do – it's just a number format replacing 'E+03' with 'k', etc.

This is especially so, compared to the potentially massive undertaking involving unit verification between cells, which could end up sprawled all over.

--- To answer the questions asked originally ---

- a list of multiplier (k, M, G, T, P,..., m, µ, n, p, f...) is necessary, as well as their equivalent in all language (non-latin)

The list is at https://en.wikipedia.org/wiki/Metric_prefix in the box at right; we'd leave out those that are not integer powers of 1000.

If this moves forward, I can gather the values for Russian, Chinese, Japanese, and perhaps Zulu by asking relevant friends and relatives who are scientists and native speakers of those languages.

Past that, what process is usually taken for internationalizing new text?

- what should be displayed if value is higher than 1000 times the largest multiplier?
- what should be displayed if value is less than 1/1000 times the smallest multiplier?

Fall back on numeric engineering notation with no prefix if less than 10^-18 or as large as 10^21.

The other questions do not apply to this simpler feature, as it's just a display format.

(In reply to Laurent Balland from comment #7)
..subject originally raised in Bug 153993; moving discussion here.
> 
> In the meantime, I had a look at what Gnumeric proposed and, mentioned in
> comment 2, the main limitation is that only simple base units are available.
> Something like area cannot be used. Moreover 1000 m² is NOT 1 km² ;-)
> 

I agree that considering _units_ opens a huge can of worms.  Aside from the example you mention, one might expect that 10V / 1kΩ would produce a result in Amps: 12mA.  Such intelligence about units would be _far_ more complex than a simple display format.

And I think it's unnecessary to go that far.  I think prefix support alone, without considering units would be very useful and much simpler to implement.

(In reply to Matt Keveney from comment #9)
> (In reply to Laurent Balland from comment #7)
> ..subject originally raised in Bug 153993; moving discussion here.
>
> I agree that considering _units_ opens a huge can of worms.  Aside from the
> example you mention, one might expect that 10V / 1kΩ would produce a result
> in Amps: 12mA.  Such intelligence about units would be _far_ more complex
> than a simple display format.

This would indeed be a can of worms... except this is not what (as I perceive, since I do have the exact same wish) the initial request is about. Gnumeric indeed doesn't support unit conversion, we're only talking about _formatting_ here, e.g. display "m" (milli) instead of "E-03" or "k" instead of "E+03". Having the exponent part makes (IMHO) cell values harder to read than the usual SI prefix.


> And I think it's unnecessary to go that far.  I think prefix support alone,
> without considering units would be very useful and much simpler to implement.

Appending an SI unit in Gnumeric is supported in a list. It is up to the user to select the appropriate unit given the formula they're using (if any). It's not the responsibility of the program to do that. So only a transcription from scientific notation (that is a "E" followed by a sign and two digits) to the appropriate SI prefix ("k", "m", "n", "p", "M", "µ", "G", aso) is the object of this request.

From where I stand such a feature would make a __huge__difference as it is the only reason I'm still using Gnumeric despite a long list of features that only LOCalc/OOCalc provide.

Now as far as the unit is concerned, I see no need to support that from LO/OO Calc as the user still can append a custom string to the format string. I suppose the biggest challenge is to expose a "standard" way to format numbers, while accounting for engineering notation...

Just wanted to add my 2¢.