[Solved] Ignoring negative value when summing a column

Discuss the spreadsheet application

[Solved] Ignoring negative value when summing a column

Postby roberthawkins » Fri Dec 07, 2018 11:41 am

I'm trying to sum a column which has a negative number in it which I wish to ignore.
It is presently: J4=SUM(C6:C37). A negative value will always occure in the column to be summed which I wish to ignore.
When I add a value to B6 for instance, the correct value comes up in C6 because its formula is: =B6-B5. this continues down the column. However, C7 then shows a negative number because B7 has not yet got a value (that comes next day. Summing C6:C37 unfortunately takes that negative value into account. i'd like to ignore it in the J4 sum.
Cheers
Bob
Last edited by robleyd on Sat Dec 08, 2018 1:54 am, edited 3 times in total.
Reason: Title Edited
windows 10, Open Office 4.1.6
roberthawkins
 
Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

Re: Ignoring 0 value when summing

Postby Lupp » Fri Dec 07, 2018 12:04 pm

Welcome to the forum!

There is the standard function SUMIF() to do what you want:
Code: Select all   Expand viewCollapse view
=SUMIF(C6:C37;">=0";C6:C37)
Also consult the help to know in what way the parameter positions are interpreted.

===Editing===
I missed to mention the probably sometimes preferrable alternatives
Code: Select all   Expand viewCollapse view
=SUMPRODUCT(C6:C37;C6:C37>=0)  and
=SUMPRODUCT(C6:C37+ABS(C6:C37))/2
The first formula actually relies on the basic functionality of SUMPRODUCT while the second one only makes use of the ForceArray specification for its parameters.
===End Edit===


roberthawkins (subject) wrote:Ignoring 0 value when summing
roberthawkins wrote:I'm trying to sum a column which has a negative number in it which I wish to ignore...
I would suggest you go back to your original post and edit the subject to get it consistent.
If the answer helped you, please also prefix "[SOLVED]" to the subject.
Last edited by Lupp on Fri Dec 07, 2018 4:09 pm, edited 1 time in total.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Ignoring 0 value when summinga column.

Postby roberthawkins » Fri Dec 07, 2018 3:47 pm

Thanks Lupp, I've modified my request as best I can. Hope it makes sense now.
windows 10, Open Office 4.1.6
roberthawkins
 
Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

Re: Ignoring 0 value when summinga column.

Postby Lupp » Fri Dec 07, 2018 4:15 pm

If the suggested formula solved your problem you might again consider to prefix " [SOLVED]" to the subject of your original question. This is expected to help visitors to find solved requests among those they may get listed with their query.

The other hint was about the "Ignoring 0". You don't want to ignore 0, but negative values. The subject is misleading therefore. In addition: Ingnoring zeroes when summing doesn't change anything.
On Windows 10: LibreOffice 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Let's create a powerful UFO: United Free Office!
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 2339
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Ignoring negative values when summinga column.

Postby roberthawkins » Fri Dec 07, 2018 6:15 pm

OK, not a mathematician so I didn't know what you meant. I didn't put solved because it isn't yet.
Thanks for the tip and I've ammended the subject line.
Cheers
Bob
windows 10, Open Office 4.1.6
roberthawkins
 
Posts: 4
Joined: Fri Dec 07, 2018 11:34 am

Re: Ignoring 0 value when summinga column.

Postby Zizi64 » Fri Dec 07, 2018 6:26 pm

However, C7 then shows a negative number because B7 has not yet got a value (that comes next day.


Try to use the formula
Code: Select all   Expand viewCollapse view
=IF(ISBLANK(B7);"";B7-B6)

for eliminate negative values at non existing entries.


...or please upload your sample file here.
Tibor Kovacs, Hungary; LO4.4.7, LO6.1.5 on Win7-10 x64Prof.
PortableApps, winPenPack: LO3.3.0-6.2.1 and AOO4.1.5
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 7766
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Ignoring 0 value when summinga column.

Postby roberthawkins » Fri Dec 07, 2018 9:51 pm

Thanks Zizi64, that worked a treat,
Bob
windows 10, Open Office 4.1.6
roberthawkins
 
Posts: 4
Joined: Fri Dec 07, 2018 11:34 am


Return to Calc

Who is online

Users browsing this forum: robleyd and 26 guests