Hello,
For the past several hours I've been trying to figure out what I'm doing wrong in my LibreOffice Calc spreadsheet. Searches are not providing me with a reasonable (11k) number of results.. I hope someone here can help.
I've attached a copy of the sheet as a reference. System information in my signature.
Note row 23's results.
My understanding of the if() function indicates that cells C23, E23, F23, H23, I23 and J23 are reporting incorrectly. These should all have a result of 0.5.
To confirm, I copied these cells and pasted into Excel, and I'm getting the correct results.
What am I missing?
Thank you in advance for your time.
[Solved] Help with IF() function. Incorrect result.
[Solved] Help with IF() function. Incorrect result.
- Attachments
-
- Choice_Workskeet_Is_Broken.ods
- (11.81 KiB) Downloaded 80 times
Last edited by robleyd on Mon Oct 21, 2019 11:17 pm, edited 1 time in total.
Reason: Tagged [Solved]
Reason: Tagged [Solved]
Windows 10, 64-bit, LibreOffice 6.2.7.1 (x64)
Re: Help with IF() function. Incorrect result.
There is nothing wrong with your formula but something weird is going on with the document. I tried a Hard Recalculate to force re-evaluation of the formulas but nothing changed. However, if I change the width of the columns using the menu Format -> Columns, the formulas do get recalculated and show the correct 0.5 result. I wonder if there is some setting I do not know about. I will continue to poke around and maybe someone else will have a suggestions about the origin of the problem.
Edit: Oh, I see. THe column is just too narrow to show 0.5 so it is getting rounded up to the nearest integer, making it look like the wrong result is being returned. Put the formula
Code: Select all
|
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: Help with IF() function. Incorrect result.
Wow! Okay.
So how does one turn off that 'feature'? I'd rather get the ### indication that the column is too narrow to display the result compared to getting a result that appears to be incorrect.
Thank you!
So how does one turn off that 'feature'? I'd rather get the ### indication that the column is too narrow to display the result compared to getting a result that appears to be incorrect.
Thank you!
Windows 10, 64-bit, LibreOffice 6.2.7.1 (x64)
- MrProgrammer
- Moderator
- Posts: 4908
- Joined: Fri Jun 04, 2010 7:57 pm
- Location: Wisconsin, USA
Re: Help with IF() function. Incorrect result.
Hi, and welcome to the forum.
[Tutorial] Ten concepts that every Calc user should know
Alternate idea: Select Shrink to fit cell size in the Alignment section of the Styles and Formatting dialog or of the Format Cells dialog.Epartsman wrote:I'd rather get the ### indication that the column is too narrow to display the result compared to getting a result that appears to be incorrect.
Since your original problem (first post) is solved please go to that post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.Epartsman wrote:Wow! Okay.
[Tutorial] Ten concepts that every Calc user should know
Mr. Programmer
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
AOO 4.1.7 Build 9800, MacOS 13.6.3, iMac Intel. The locale for any menus or Calc formulas in my posts is English (USA).
Re: Help with IF() function. Incorrect result.
There is a default 'Numbers' format behaving the way you experienced. If you explicitly set a numbers format resulting in a specific width of the displayed value you will get the "###" alert if the column is too narrow.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München
Re: [Solved] Help with IF() function. Incorrect result.
Lupp,
Thank you for your insight.
I've looked through the default settings and I don't see anything that looks suspicious.
Could you provide me with some guidance on how to set up Calc so that it displays ### instead of rounding?
Many thanks,
Thank you for your insight.
I've looked through the default settings and I don't see anything that looks suspicious.
Could you provide me with some guidance on how to set up Calc so that it displays ### instead of rounding?
Many thanks,
Windows 10, 64-bit, LibreOffice 6.2.7.1 (x64)
Re: [Solved] Help with IF() function. Incorrect result.
There is no setting for this. Numbers are displayed in full length or not at all. Makes sense if you think about it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Help with IF() function. Incorrect result.
Thats not quite correct.Villeroy wrote:Numbers are displayed in full length or not at all.
-1- The "not at all" surely is meant to mean "replaced by a ### sequence".
-2- The default number format 'General' ('Standard' in some locales) behaves differently. Using it you actually get the behaviour Epartsman complained about (and which includes a few additional strangenesses in some cases).
To avoid this way of getting mislead we should always use clearly specified number formats, in most cases decimal or "scientific" formats with a fix number of figures in the fractional part.
In addition we should generally doubt the usefulness/acceptability of automatic rounding. Where rounded values should be generated for the display we should always consider if explicit rounding is needed.
A number displayed with the format code "#.00" e.g. should in many cases be obtained by a formula like
=ROUND(SomeNumericExpression; 2) to avoid bad surprises.
(And, of course, this is not related to the usage of the IF function or any other specifics of the used numeric expression.)
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
---
Lupp from München