| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
ignoring negative numbers
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ignoring negative numbers
=SUMIF(A1:A4, "0")
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "threademporium" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ignoring negative numbers
thank you - worked fine
"Bernard Liengme" wrote: =SUMIF(A1:A4, "0") best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "threademporium" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
ignoring negative numbers
Thank you - worked fine
"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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Set negative numbers to zero. Do not calculate with negative valu | Excel Discussion (Misc queries) | |||
| Comparing numbers, ignoring sign | Excel Discussion (Misc queries) | |||
| Ignoring negative number | Excel Worksheet Functions | |||
| Ignoring Negative Values | Excel Discussion (Misc queries) | |||
| Ignoring letters behind numbers | Excel Worksheet Functions | |||
Linear Mode
