Number
The number to format.
DecimalPlacesMinimum
The minimum number of decimal places a number should be formatted with. Must be greater than or equal to 0 and less than or equal to the maximum number of decimal places. If omitted, 0 is assumed.
DecimalPlacesMaximum
The maximum number of decimal places a number should be formatted with. Must be greater than or equal to the minimum number of decimal places. If this parameter is omitted and the DecimalPlacesMinimum parameter is also omitted, this parameter is set to 3. If this parameter is omitted, and the DecimalPlacesMinimum parameter is not omitted, this parameter is set to that parameter. If this parameter is less than the DecimalPlacesMinimum parameter, this parameter is set to that parameter.
RoundingMode
The mode to use when rounding numbers. These are the supported values:
| Formula | Description |
|---|---|
| RoundingMode.CeilingRoundingMode,Ceiling | Rounds towards positive infinity. For example, 2.1 becomes 3 and -2.1 becomes -2. |
| RoundingMode.FloorRoundingMode,Floor | Rounds towards negative infinity. For example, 2.9 becomes 2 and -2.9 becomes -3. |
| RoundingMode.ExpandRoundingMode,Expand | Rounds away from zero. For example, 2.1 becomes 3 and -2.1 becomes -3. |
| RoundingMode.TruncateRoundingMode,Truncate | Rounds towards zero. For example, 2.9 becomes 2 and -2.9 becomes -2. |
| RoundingMode.HalfCeilingRoundingMode,HalfCeiling | Rounds to the nearest neighbor, rounding towards positive infinity on ties. For intance, 2.5 becomes 3 and -2.5 becomes -2. |
| RoundingMode.HalfFloorRoundingMode,HalfFloor | Rounds to the nearest neighbor, rounding towards negative infinity on ties. For example, 2.5 becomes 2 and -2.5 becomes -3. |
| RoundingMode.HalfExpandRoundingMode,HalfExpand (default) | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds away from zero. For example, 2.5 becomes 3 and -2.5 becomes -3. |
| RoundingMode.HalfTruncateRoundingMode,HalfTruncate | Rounds to the nearest neighbor. If the value is exactly halfway between two numbers, it rounds towards zero. For example, 2.5 becomes 2 and -2.5 becomes -2. |
| RoundingMode.HalfEvenRoundingMode,HalfEven | Rounds to the nearest neighbor, rounding to the nearest even number on ties. This mode is known as Banker's Rounding because it is, on average, free of bias. For example, 2.5 becomes 2 and 3.5 becomes 4. |
Locale
The locale to use when doing the conversion.
A locale represents a language and possibly a region. If omitted, the configured locale is used.
Use a value such as Locale.EnglishLocale,English to provide a locale (the full list is below). If your chosen locale is not part of the list, use the LOCALE function instead.
All locales may not be available. If a locale is not available, a fallback locale can be used. To provide such fallback locales, use the LOCALE function.
The LOCALE function accepts so-called Unicode Locale Identifiers. Every value in the list below corresponds to such an identifier.
| Formula | Unicode Locale Identifier | Description |
|---|---|---|
| Locale.ArabicLocale,Arabic | ar | Arabic |
| Locale.BulgarianLocale,Bulgarian | bg | Bulgarian |
| Locale.ChineseCnLocale,ChineseCn | zh-CN | Chinese (simplified), as used in mainland China |
| Locale.ChineseTwLocale,ChineseTw | zh-TW | Chinese (traditional), as used in Taiwan |
| Locale.CroatianLocale,Croatian | hr | Croatian |
| Locale.CzechLocale,Czech | cs | Czech |
| Locale.DanishLocale,Danish | da | Danish |
| Locale.DutchLocale,Dutch | nl | Dutch |
| Locale.DutchBeLocale,DutchBe | nl-BE | Dutch, as used in Belgium |
| Locale.EnglishLocale,English | en | English |
| Locale.EnglishAuLocale,EnglishAu | en-AU | English, as used in Australia |
| Locale.EnglishCaLocale,EnglishCa | en-CA | English, as used in Canada |
| Locale.EnglishInLocale,EnglishIn | en-IN | English, as used in India |
| Locale.EnglishNzLocale,EnglishNz | en-NZ | English, as used in New Zealand |
| Locale.EnglishUkLocale,EnglishUk | en-GB | English, as used in the United Kingdom |
| Locale.EnglishUsLocale,EnglishUs | en-US | English, as used in the United States of America |
| Locale.EnglishZaLocale,EnglishZa | en-ZA | English, as used in South Africa |
| Locale.FinnishLocale,Finnish | fi | Finnish |
| Locale.FrenchLocale,French | fr | French |
| Locale.FrenchBeLocale,FrenchBe | fr-BE | French, as used in Belgium |
| Locale.FrenchCaLocale,FrenchCa | fr-CA | French, as used in Canada |
| Locale.FrenchChLocale,FrenchCh | fr-CH | French, as used in Switzerland |
| Locale.GermanLocale,German | de | German |
| Locale.GermanAtLocale,GermanAt | de-AT | German, as used in Austria |
| Locale.GermanChLocale,GermanCh | de-CH | German, as used in Switzerland |
| Locale.GreekLocale,Greek | el | Greek |
| Locale.HebrewLocale,Hebrew | he | Hebrew |
| Locale.HindiLocale,Hindi | hi | Hindi |
| Locale.HungarianLocale,Hungarian | hu | Hungarian |
| Locale.IndonesianLocale,Indonesian | id | Indonesian |
| Locale.ItalianLocale,Italian | it | Italian |
| Locale.ItalianChLocale,ItalianCh | it-CH | Italian, as used in Switzerland |
| Locale.JapaneseLocale,Japanese | ja | Japanese |
| Locale.KoreanLocale,Korean | ko | Korean |
| Locale.NorwegianLocale,Norwegian | nb | Norwegian (bokmål) |
| Locale.PersianLocale,Persian | fa | Persian |
| Locale.PolishLocale,Polish | pl | Polish |
| Locale.PortugueseLocale,Portuguese | pt | Portuguese |
| Locale.PortugueseBrLocale,PortugueseBr | pt-BR | Portuguese, as used in Brazil |
| Locale.RomanianLocale,Romanian | ro | Romanian |
| Locale.RussianLocale,Russian | ru | Russian |
| Locale.SlovakLocale,Slovak | sk | Slovak |
| Locale.SpanishLocale,Spanish | es | Spanish |
| Locale.SpanishArLocale,SpanishAr | es-AR | Spanish, as used in Argentina |
| Locale.SpanishEsLocale,SpanishEs | es-ES | Spanish, as used in Spain |
| Locale.SpanishMxLocale,SpanishMx | es-MX | Spanish, as used in Mexico |
| Locale.SwedishLocale,Swedish | sv | Swedish |
| Locale.ThaiLocale,Thai | th | Thai |
| Locale.TurkishLocale,Turkish | tr | Turkish |
| Locale.UkrainianLocale,Ukrainian | uk | Ukrainian |
| Locale.VietnameseLocale,Vietnamese | vi | Vietnamese |
Returns
A number formatted using engineering notation.
Returns a number formatted using engineering notation. FORMAT.ENGINEERING (350000) FORMAT.ENGINEERING (350000) returns "350E3", if the configured locale is US English, which should be interpreted as 350 * 10 ^ 3350 * 10 ^ 3. Engineering notation is an effective means of conveying very small or very large numbers.
The integer part of the number returned from FORMAT.ENGINEERING is always between 1 (inclusive) and 1000 (exclusive). By contrast, the number returned from FORMAT.SCIENTIFIC is always between 1 (inclusive) and 10 (exclusive).
FORMAT.ENGINEERING takes the configured locale into account when converting numbers. For instance, a decimal point is used when the locale is US English and a decimal comma is used when the locale is French.
This function is not commonly supported by spreadsheets.
Decimal places
The second and third parameters specify the number of decimal places in the formatted number. If neither parameter is provided, the function uses the minimum number of decimal places needed, up to a maximum of three. If only the second parameter is given, it specifies an exact number of decimal places.
This formula returns "3.50E3", using exactly two decimal places:
If a third parameter is given, the second parameter is interpreted as the minimum number of decimal places that should be used and the third parameter is interpreted as the maximum number of decimal places that should be used.
This formula uses between 2 and 6 decimal places:
As 3500 is an integer and no decimal places are required, "3.5005E3" is returned (for US English).
However, if the first parameter is set to 3500.1234567893500,123456789, "3.500123E3" is returned. The returned value respects the maximum number of decimal places to use, which is set to 6.
Related functions
- Use FORMAT to format regular numbers.
- Use FORMAT.CURRENCY to format monetary values (-0.75 can be formatted as "($0.75)").
- Use FORMAT.FRACTION to format fractions (0.75 can be formatted as "3/4").
- Use FORMAT.PERCENTAGE to format percentages (0.75 can be formatted as "75%").
- Use FORMAT.ENGINEERING to use engineering notation (15,000 can be formatted as "15E3").
- Use FIXED to round a number to the left of the decimal separator (7,523 can be formatted as "7,500").
Examples
Returns the array { "3.50E3", "350.00E-6" }{ "3.50E3"; "350.00E-6" } if the configured locale is US English. The number of decimal places is set to 2. When invoked with an array, FORMAT.ENGINEERING is invoked once per array element and the results are collected in an array.
Returns "3.50E3" if the configured locale is US English, using exactly two decimal places.
Returns "3.50E3" if the configured locale is US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.
Returns "3.500123E3" if the configured locale is US English. As both the second and the third parameter are specified, 2 is taken to mean the minimum number of decimal places that should be used, and 6 is taken to mean the maximum number of decimal places that should be used.