Unable to format cells as currency

Discuss the spreadsheet application

Unable to format cells as currency

Postby effiencoop » Mon Nov 11, 2019 11:03 pm

What in the world?

The Currency formatted as Currency is not formatted as Currency.

pqe_1.png

pqe_2.png

pqe_3.png
Last edited by MrProgrammer on Mon Nov 18, 2019 4:59 am, edited 1 time in total.
Reason: Editted title; was: Calculated formatting
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby Villeroy » Mon Nov 11, 2019 11:45 pm

Why don't you upload the real thing instead of pictures of it?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27752
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated formatting

Postby Zizi64 » Mon Nov 11, 2019 11:54 pm

+1

Please upload a real, ODF type sample file here. We can not check the properties of the applied styles (and other things) on a picture.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 12:13 am

ods.
Attachments
PQE.ods
(13.22 KiB) Downloaded 9 times
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby Zizi64 » Tue Nov 12, 2019 12:39 am

From the LibreOffice Help:

IMDIV

The result is the division of two complex numbers.
Syntax

IMDIV("ComplexNumerator"; "ComplexDenominator")

ComplexNumerator, ComplexDenominator are complex numbers that are entered in the form "x+yi" or "x+yj".
Example

=IMDIV("-238+240i";"10+24i") returns 5+12i.


Why you use IMDIV()? That is designed for the complex numbers.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 12:49 am

If you are going to answer, do so.
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby Zizi64 » Tue Nov 12, 2019 12:58 am

If you are going to answer, do so.


I do not know, what you want to achieve:
There is not DIV() function in the Calc, but you can use the formula:
Code: Select all   Expand viewCollapse view
=INT(A2/B2)


or you can use a simple division:
Code: Select all   Expand viewCollapse view
=A2/B2
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 1:18 am

Zizi64 wrote:
If you are going to answer, do so.


I do not know, what you want to achieve:
There is not DIV() function in the Calc, but you can use the formula:
Code: Select all   Expand viewCollapse view
=INT(A2/B2)


or you can use a simple division:
Code: Select all   Expand viewCollapse view
=A2/B2



Thank you.
Code: Select all   Expand viewCollapse view
=A2/B2
displays the formatting properly as intended.

pqe_4.png
pqe_4.png (7.96 KiB) Viewed 404 times




Note to devs: Formatting is clearly broken.
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby FJCC » Tue Nov 12, 2019 1:24 am

I can sympathize with the devs not expecting anyone formatting complex numbers as currency
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculated formatting

Postby Zizi64 » Tue Nov 12, 2019 1:28 am

Note to devs: Formatting is clearly broken.

Why you think that? How the formatting (the direct/manual formatting or the Styles feature) is broken?
Please give us some details.

There are not developers in this Forum: Users help Users.
Tibor Kovacs, Hungary; LO6.1.6, 6.2.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-6.4.0; AOO4.1.6
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 8771
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Calculated formatting

Postby Villeroy » Tue Nov 12, 2019 1:32 am

IMDIV returns text. You shoot yourself into the foot by enforcing right alignment, so your text looks like numbers. Number formatting applies to decimal numbers only. Complex numbers are no decimals.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27752
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 1:41 am

I told it to format as currency.

It did not.

It is broken.

Look at it this way, I go grocery shopping on my riding brand new lawnmower. OpenOffice said NOOO go home and get your station wagon, or moped, bicycle!
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby RusselB » Tue Nov 12, 2019 1:59 am

Numeric formatting has no effect on results or entries that are of a textual nature.
If you are using formulas that return text that looks like numerics, and you want the numeric formatting to work, then you have to turn the text into a numeric...easily done using the VALUE function.... or another function/formula that returns a numeric result.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 2:11 am

RusselB wrote:Numeric formatting has no effect on results or entries that are of a textual nature.



Exactly. This is what is broken.
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby FJCC » Tue Nov 12, 2019 3:15 am

Nothing about this is broken. You have run up against a design feature that you do not like, at least in this instance. The rule is that changing the format of a cell will never change its value, even if the text "obviously" looks like a number. There are good reasons for this. The text 1,250 is one thousand two hundred fifty in the US but it is one and 250 one-thousandths in many other locales. The date (dates are numbers) 10/2/19 might be in October or in February, depending on the text convention used by the author. And the century of the year is unknown! Automatic conversion of text to numbers is full of problems and the decision for OpenOffice and many other computer applications is that the user must explicitly determine how to make such conversions.
Windows 10 and Linux Mint, since 2017
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
FJCC
Moderator
 
Posts: 7492
Joined: Sat Nov 08, 2008 8:08 pm
Location: Colorado, USA

Re: Calculated formatting

Postby RusselB » Tue Nov 12, 2019 3:22 am

The IMDIV function returns a text string, not a numeric.
This is so the function can work as intended, with complex numbers (aka numbers that have alphabetic characters included).
The fact that you are working with simple numbers using the IMDIV function does not change the type of output from that function.
If you open your original spreadsheet and enter the formula
Code: Select all   Expand viewCollapse view
=istext(C2)
in D2, you will get a response of TRUE
This means that the contents of C2 is being recognized as text, even though it appears to be numeric.
The numeric formatting, as already stated, does not have any effect on formula results that are textual in nature. This is why the numeric formatting wasn't working for you... You were trying to apply it to the wrong type of data.
You already noticed that the numeric formatting works correctly when the formula was changed to one that returns numeric data, rather than textual data.

IMO, it's not the formatting that is broken (as you claim), but your understanding of how the formatting works.
If you wish to dispute this, upload a spreadsheet that has numeric data that does not format correctly.
You can easily determine if the results of a formula are returning numeric or textual data using either the ISTEXT or ISNUMBER functions.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Calculated formatting

Postby effiencoop » Tue Nov 12, 2019 4:31 am

Yes yes design blah blah, standard spreadsheet blah blah.

OK spreadsheet, update. "very well. Lets see... divide this and then format it as OMG IT'S NOT A DECIMAL NUMBER! EXTERMINATE! EXTERMINATE!"

You WANT text like "11.14" to NOT format as currency when TOLD to do so? bahhahahahahah You are part of why the tech industry is a disaster.

/long live AHK
Open Office 4.1.7 on Windows 10
oo 4.1.7 on Windows 10
oo 4.1.7 on w10
effiencoop
 
Posts: 7
Joined: Mon Nov 11, 2019 10:22 pm

Re: Calculated formatting

Postby RusselB » Tue Nov 12, 2019 4:59 am

Enough. If you want to be able to format text that looks like numbers as numbers, then, I believe MS Excel does that.
If you want to use OpenOffice, then you have to work within the capabilities, or lack thereof.
OpenOffice 4.1.7 and LibreOffice 6.3.3.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
User avatar
RusselB
Moderator
 
Posts: 5854
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON


Return to Calc

Who is online

Users browsing this forum: No registered users and 8 guests