At 16:35 02/02/2013 +0200, Hylton Conacher wrote:
I have a formula below which is giving me an Err: 502 on Calc 3.4.5
when I try and calculate a currency total using a sliding scale.
=SUM((((IF(b6>c2,d2,0),(IF(b6-c2)>c3,d3,0),(IF(b6-c2-c3)>c4,sum((b6-c2)-c3)*b4,0))))
That's not surprising. First, you have one more opening bracket than
closing ones, so your formula just has to be wrong! Let's guess you
have one too many at the beginning. And then let's simplify matters.
o There is no particular reason to use the SUM() function (though it
is certainly not wrong); it is probably easier to read if you just
use "+" to add values:
=((IF(B6>C2;D2;0) + (IF(B6-C2)>C3;D3;0) +
(IF(B6-C2-C3)>C4;sum((B6-C2)-C3)*B4;0)))
o Even after taking off the brackets that belonged to the SUM()
function (and the extra one), we still have two sets of unnecessary
brackets surrounding your entire expression. Let's remove those:
=IF(B6>C2;D2;0) + (IF(B6-C2)>C3;D3;0) + (IF(B6-C2-C3)>C4;sum((B6-C2)-C3)*B4;0)
o The first IF expression makes sense: the brackets include the
correct range of expression elements and the result will be a
numerical value. But the second IF has its opening bracket misplaced
before the IF: that makes no sense, of course. Don't be confused by
the opening bracket immediately after the "IF": that merely insists
that B6-C2 is calculated before the ">" is evaluated. But that
happens by default anyway, so the internal brackets, whilst not
wrong, are probably just confusing. Let's remove those two sets and
insert the bracket we really do need:
=IF(B6>C2;D2;0) + IF(B6-C2>C3;D3;0) + (IF(B6-C2-C3)>C4;sum((B6-C2)-C3)*B4;0)
o Now to the third IF expression. Goodness knows what that "SUM" is
doing in there! What elements do you think it is adding
together? Let's remove it:
=IF(B6>C2;D2;0) + IF(B6-C2>C3;D3;0) + (IF(B6-C2-C3)>C4;((B6-C2)-C3)*B4;0)
o Once again, we have two sets of brackets that are unnecessary -
because they merely insist that something happens which would happen
by default anyway - but the opening bracket for the IF() function is
again misplaced in front of the IF. Let's make those corrections:
=IF(B6>C2;D2;0) + IF(B6-C2>C3;D3;0) + IF(B6-C2-C3>C4;(B6-C2-C3)*B4;0)
Does that (simpler) formula work for you? (I put spaces around the
plus signs only for ease of reading; you may want to edit them out again.)
Does the same happen in newer versions of Calc?
Your original formula produces Error 508, which quite correctly
observes that the brackets are mismatched.
I trust this helps.
Brian Barker
--
For unsubscribe instructions e-mail to: [email protected]
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted
