Description: I create a checkbox linked to a cell. set the "Reference value (on)" to "25" and the "Reference value (off) to "0"(zero) No quotes are used in Calc. Used quotes here to isolate values used. Switching design mode off and clicking the check box to mark it as checked shows the check mark. And the displayed value of the cell changes to "25" and unchecking the box changes the text value of the cell to "0", it changes to match when the value of the checkbox cell changes. Checkbox control created and linked to H16. Values set as above. If I assign the value of another cell to equal the value of that cell like "=H16", then the value of the cell matches that of cell H16. If I create a sum() function in another cell that includes the H16 in it, the sum() value does NOT include the value of the checked cell. Using =SUM(H14:H20) does not handle the value of H16 properly. Formatting the cell H16 as a number has no effect. This behavior is the same on Armbian Libreoffice version 6.0.7.3 and Windows 10 Libreoffice 6.1.3.2 Workaround is to use the value() function when the cell value is included anywhere. As such: =SUM(H14:H15,H17:H20,VALUE(H16)) Using ranges around the affected cell and then using the value() function to include the desired cell is clumsy and should not be necessary. I'm guessing this behavior is endemic to Libreoffice and is to be found in all versions and hardware types. Steps to Reproduce: 1.Open Calc 2.create a checkbox control and link it to a cell and set the on and off values to 25 and 0. 3.include the value of the linked cell in a complex sum(include values of multiple cells. Not just one cell) in another cell. or any function that tries to evaluate the contents of the cell as a number. 4. Check or uncheck the checkbox. If the desired result is a number, then any function that relies on the result will fail. Actual Results: The value of the complex sum does not include the value of the cell linked to the checkbox. Expected Results: The value of the complex sum() function should include the value of the linked cell in its result. Reproducible: Always User Profile Reset: Yes Additional Info: The software should properly recognize the type of value when assigning the cell contents from the checkbox control. Formatting the cell as number has no effect. Other controls should be evaluated for similar behavior.
Can you attach a minimal sample file for test?
Created attachment 149340 [details] checkbox ref value test case IMHO this is not a bug, it works like designed, "RefValue" is defined as string, so one has to convert string to number. https://api.libreoffice.org/docs/idl/ref/servicecom_1_1sun_1_1star_1_1form_1_1component_1_1CheckBox.html#af3619002e30c0346b7e70ed1bbb96679
Created attachment 149341 [details] example of behavior in report check or uncheck the checkbox. The value in A3 will not change.
(In reply to Oliver Brinzing from comment #2) > Created attachment 149340 [details] > checkbox ref value test case > > IMHO this is not a bug, it works like designed, "RefValue" is defined as > string, > so one has to convert string to number. > > https://api.libreoffice.org/docs/idl/ref/ > servicecom_1_1sun_1_1star_1_1form_1_1component_1_1CheckBox. > html#af3619002e30c0346b7e70ed1bbb96679 Why do you think that that behavior is desirable? Why should it not treat a number value as a number?
(In reply to Andrew from comment #4) > Why do you think that that behavior is desirable? > Why should it not treat a number value as a number? how should the implementation know that 25 is a number and not an ID? and how to treat locale floating point numbers, e.g. 1.234,99 or 1,234.99? checkbox seems not have a property to set a locale number format, so all "RefValues" are treated as strings. if you enable Menu "View/Value Highlighting", you will see, that the checkbox will write "text" into the linked cell.
Spreadsheets are for manipulating numbers. That's literally what they're for.
If all values in the range to sum are numbers or text numbers not other kind of strings, then =SUMPRODUCT(A1:A2*1) can work, because it forces the conversion. And direct references to one cell with text numbers should work also. Another issue sure is that change the behavior can break existing forms. Forms don't take care of what the receiver does with the values, in this case a spreadsheet. If you want reopen as request for enhancement on the second box of Importance field, adapting the title.
(In reply to m.a.riosv from comment #7) > If all values in the range to sum are numbers or text numbers not other kind > of strings, then =SUMPRODUCT(A1:A2*1) can work, because it forces the > conversion. And direct references to one cell with text numbers should work > also. > > Another issue sure is that change the behavior can break existing forms. > > Forms don't take care of what the receiver does with the values, in this > case a spreadsheet. > > If you want reopen as request for enhancement on the second box of > Importance field, adapting the title. That would be a workaround as it is not the default behavior when you hit the ∑ button and select the range that includes the affected cell. This isn't on a form. It's on a sheet with the checkbox control on it. See the example I attached.
You know what? Never mind. I just did what I wanted to do in Google Sheets in about 10 minutes with no hassles. Google Sheets has no problem evaluating the value in a cell linked to a checkbox. So I'll mark this as resolved or whatever and move one more person from the Libreoffice is good column to the Libreoffice is crap column. Good job all. Carry on.