[Solved] Help with IF() function. Incorrect result.

[Epartsman]

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.

[FJCC]

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 =C23 in M23, which is a wide column and you will see that C23 is actually equal to 0.5

[Epartsman]

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!

[MrProgrammer]

Hi, and welcome to the forum.

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.

Alternate idea: Select Shrink to fit cell size in the Alignment section of the Styles and Formatting dialog or of the Format Cells dialog.

Wow! Okay.

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.

[Tutorial] Ten concepts that every Calc user should know

[Lupp]

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.

[Epartsman]

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,

[Villeroy]

There is no setting for this. Numbers are displayed in full length or not at all. Makes sense if you think about it.

[Lupp]

Numbers are displayed in full length or not at all.

Thats not quite correct.
-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.)