Re: [libreoffice-users] Calc SUM Error 502 - How to add results of three IF formulae?

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: users+help@global.libreoffice.org
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