ignoring negative numbers
Hi,
assuming the values are in a1 - a4 use this
=SUM(IF(A1:A40,A1:A4))
'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array
Mike
"threademporium" wrote:
excel 2002 / windows xp
PROBLEM
I want to automatically add up a column of four values some of which could
be negative values.
I want to be able to add the values together with the rule that any negative
values can be excluded.
BACKGROUND
Each of the four values has been created through a formula. This formula is
a multiplication of 2 values which is then subject to minus calculation.
example 1
formula = £0.0270 x 4444 - £12.00 = £107.98
example 2
formula = £0.0270 x 412 - £12.00 = - £0.876 (negative number)
example 3
formula = £0.0270 x 678 - £12.00 = £6.306
example 4
= formula £0.0270 x 234 - £12.00 = - £5.682 (negative number)
When I add them up with my limited knowledge of excel I get £107.728 - what
I want to get is £114.286
thank you for taking a look at my question
Dave
|