Description: I have a spreadsheet I use for tracking daily percentage of change from the previous row. The unpopulated row should be blank but gives a #VALUE! in the cell in question. I easily recreated this and will attach an example. Steps to Reproduce: 1.Create a column of numbers (A1 thru A4 for example)the values of which can be greater or less than the number above. Follow suit for column B. For Column C, total A+B for the row. Drag the SUM formula down to C6 while A and B 5 and 6 are unpopulated. 2.In column D on the second row, the formula should be as follows: =IF(C1="","",SUM(C2-C1)/C1)*100. Drag the formula down to D6. 3.Expected result is D5 and D6 should remain blank (following the TRUE portion of the formula) but the cell gets a #VALUE! since it's looking at D4 for the FALSE portion of the IF. (Use TRACE ERROR to see this) Actual Results: Cell is populated unexpectedly. Expected Results: Expect the cells to remain blank. Reproducible: Always User Profile Reset: No OpenGL enabled: Yes Additional Info: IF should have ignored the FALSE portion of the formula.
Created attachment 146189 [details] Sample file to show perceived problem This shows the issue.
C5 and C6 aren't blank and don't contain empty strings, which would qualify them to fulfill `C5=""` condition. They contain a number (zero) as the result of the formula adding two empty cells, and if you need to account for that specific condition in your formula, then do that explicitly. E.g., =IF(OR(C1="";C1=0);"";SUM(C2-C1)/C1)*100 Closing NOTABUG. Please reopen if you think it's closed by mistake; please explain why you do so.
Ah, I see that the description in comment 0 is incorrect wrt the sample file... need to recheck.
1. The original description was wrong when described the column C: it should say that a formula like "=IF(A1="";"";SUM(A1;B1))" is used there. 2. There is no bug in Calc; rather, there's an error in the formula in D, where the result of "IF(C1="","",SUM(C2-C1)/C1)" is unconditionally multiplied by 100. In case of cells with empty neighbors, this results in ""*100, which is naturally wrong, which is reported as #VALUE!. The proper formula is "=IF(C2="";"";SUM(C2-C1)/C1*100)" Closing NOTABUG.