https://bugs.documentfoundation.org/show_bug.cgi?id=166631
Bug ID: 166631
Summary: Unexpected zero result from a price adjustment formula
when it works in 99% of cases
Product: LibreOffice
Version: 24.2.3.2 release
Hardware: x86-64 (AMD64)
OS: Windows (All)
Status: UNCONFIRMED
Severity: normal
Priority: medium
Component: Calc
Assignee: [email protected]
Reporter: [email protected]
Description:
Using logic and comparison functions within a formula, I devised a way to
adjust prices to n.25, n.49, n.75 and n.99 (e.g. $8.25, $7.49, etc.) when the
cents (fractional) portion is different.
My original formula is =IF(AND(C2>=0, C2<=0.22), B2-0.01, IF(AND(C2>=0.23,
C2<=0.46), B2+0.25, IF(AND(C2>=0.47, C2<=0.72), B2+0.49, IF(AND(C2>=0.73,
C2<=0.96), B2+0.75, IF(AND(C2>=0.97, C2<=0.99), B2+0.99)))))
(B2 is the integer value of the unadjusted price, C2 is the fractional value of
the unadjusted price))
This works for n thru n.99, except for n.22! (see my attached spreadsheet test
to see what I mean).
I rewrote this formula to use the same logic but expressed differently:
=IF(AND(C2>=0, C2<0.23), B2-0.01, IF(AND(C2>=0.23, C2<0.47), B2+0.25,
IF(AND(C2>=0.47, C2<0.73), B2+0.49, IF(AND(C2>=0.73, C2<0.97), B2+0.75,
IF(AND(C2>=0.97, C2<=0.99), B2+0.99)))))
This workaround formula produced no errors.
Steps to Reproduce:
1. See my attached spreadsheet testing these formulas using unadjusted prices
$21 through $23 with all cents values between.
2. Review values under adjusted_price_original (incorrect using n.22 unadjusted
prices) vs. adjusted_price_workaround (completely correct)
Actual Results:
When the price 21.22 or 22.22, the price gets adjusted to zero (0). See
adjusted_price_original column in my attached spreadsheet.
Expected Results:
When the price 21.22 or 22.22, the price should get adjusted to 20.99 or 21.99,
respectively. See adjusted_price_workaround in my attached spreadsheet.
Reproducible: Always
User Profile Reset: Yes
Additional Info:
Per the bottom instructions for resetting my UserProfile, I reloaded
LibreOffice Calc in safe mode, and the problem was resolved. It stayed resolved
when I loaded Calc again in normal mode.
What's odd though is that I was seeing this reported problem in one spreadsheet
before I copied it over to a new spreadsheet file, where the problem still
existed. Only resetting my UserProfile fixed it. Since my problem was so
specific, I am uploading it anyway, just in case I have exposed some sort of
real issue.
--
You are receiving this mail because:
You are the assignee for the bug.
