Description: I created a small spreadsheet with columns for quantity and price and a calculated column for the item total. I found that if the cell for quantity was left blank, the total would be computed as if it was 1 instead of 0. And also, if the quantity was entered but the price cell was blank, the calculation assumed the price was 1 USD and computed the total based on that. Steps to Reproduce: 1. Cell A1 = Quantity, leave cell blank 2. Cell B1 = price, enter 1.23 3. Set Column C to be equal to Product(A,B) 4. Cell A2 = Quantity, enter 2 5. Cell B2 = price, leave blank 6. Set Column C to be equal to Product(A,B) Actual Results: C1 = 1.23 C2 = 2.00 Expected Results: C1 = 0 since 0*1.23=0 C2 = 0 since 2*0=0 Reproducible: Always User Profile Reset: No Additional Info: simple.
Created attachment 186424 [details] Sample file showing error
PRODUCT() takes a number sequence, ignoring text and empty cells. See https://help.libreoffice.org/7.5/en-GB/text/scalc/01/04060106.html?DbPAR=CALC#bm_id3144386 and https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#PRODUCT and https://docs.oasis-open.org/office/OpenDocument/v1.3/os/part4-formula/OpenDocument-v1.3-os-part4-formula.html#__RefHeading__1017992_715980110 Not a bug.
This is a bug. Documentation for the Product function says: This function ignores any text or empty cell within a data range It is not ignoring the empty cell. It is filling the cell with "1". It should default to 0. A usage example for this would be making a spreadsheet of perhaps 100 standard items as a product order form. The user would then select maybe 10 items to order and then enter a quantity for those 10 items, leaving the cells in the quantity column blank for other 90 items. As is, the total column would calculate the price for the 10 items ordered, then add $90 for the 90 items unordered and left blank.
correction, it would add product(price,1) for the other 90 items when the price cell if filled but the quantity cell is left blank. Should be price*0, not price*1 when blank.
Created attachment 186456 [details] example spreadsheet
This seems to be a matter of clear language and specific details. @BugMagnet, this is probably not what you expect from this function. I indeed understand, but... Let's assume cell A2 is empty or has text. This cell would then be "ignored" by this function means... =PRODUCT(A1,A2) would be equivalent to =PRODUCT(A1) which would be equivalent to cell A1 by itself. A cell without an explicit number (or an equivalent to an explicit number) being "ignored" implies that for a cell to be considered as having a zero, the cell needs to actually be zero or result in zero, not just empty. I do understand that, in terms of "common" arithmetic, this doesn't make much sense. Unfortunately, this is how this function is "defined" (although, possibly additional details in ODF might be welcome). This is the reason for this report to be set as NAB. FWIW, other spreadsheet tools work in the same way with this function.
Might it be possible then to have some alternative options to this? Perhaps when such a column is defined as being numeric, to then assume blank is to be 0 for function purposes. Or the option to populate the column with 0 then an option to hide the 0? As you might guess, I do not use spreadsheets much, so was not at all aware that this was common behavior for other apps also. It just seems not to be intuitive and I could imagine create some serious errors if one is to rely on the mathematical functions that operate this way. For me, since this is an isolated use, now that I am aware of this behavior, I can do a workaround for my purposes, such as pre-populating all the quantity cells with 0s, which would then be changed to positive digit(s) for the items to be ordered.
The PRODUCT() function is more useful for ranges rather than multiplying single cells. You could just use A1*A2. Or you could add 2 columns with the formulas: =N(A1) =N(A2) and then use the PRODUCT() function on those new cells/columns. Or you could use: =PRODUCT(N(A1),N(A2)) Clearly A1*A2 wins in this case. I'll reset this report to NAB now. For future doubts, you could try first https://ask.libreoffice.org
I have to apologize for my display of ignorance. I tend to be very intuitive and this behavior isn't IMO. However, when I started to research this issue, I saw many have asked about the same thing over several years. And in doing so, I learned a little more of the powers and potentials available that this inexperienced user was totally unaware of. Following some examples on one of the webpage tutorials i found, I experimented with and settled on this rather elegant solution... For the row cell in the Total column: =IF(OR(ISBLANK(B2),ISBLANK(C2)),"",PRODUCT(B2,C2)) Then I wanted to experiment with a blank cell in the price column for which a quantity was entered, perhaps to indicate that item was temporarily unavailable. The total column was calculated correctly, ignoring that 4 units were desired but the quantity column total included this 4 even though unavailable. I know there will be some solution for this too but I have so much to learn. And with other pressing more critical matters, don't have much time for this right now. If I knew then what I know now, I would not have opened this ticket, so agree that NAB is true.
Created attachment 186475 [details] Situation Solved with If-Then-Else