How to return a zero if either of the two components is zero?

[Clearwell_T]

Hi All,

First time poster here. And very early LibreCalc user.

I’m sure my question is ridiculously easy to answer for anyone with some experience, but I couldn’t figure out what search terms to use (inexperience with appropriate terminology) to find my answer, hence posting a new topic (mods please combine with other topics if necessary).

My question is:
I’m trying to create a simple formula that adds the numbers inside two cells together. This is obviously straight forward enough. What I would like to be able to encode in the formula is, how to return a zero value as output, if one of the source cells contains a zero?

ie:
Cell 1 = “some actual number” + Cell 2 = “some actual number” results in the correct summation as output.

But I want to code:
Cell 1 = “some actual number” + Cell 2 = “zero (or empty cell)” results in zero as output.

Or:
Cell 1 = “zero (or empty cell)” + Cell 2 = “some actual number” results in zero as output.

Any and all help is much appreciated.

Regards.

CT

[PKG]

@Clearwell_T
=IF(OR(A1=0;B1=0);0;SUM(A1:B1))

[Clearwell_T]

Hi PKG,

Absolutely brilliant!!

Thanks so much for your help.

Just out of interest, I spent ages looking at trying to use SUMIF(S) with the <> operator to solve this problem. Is that an approach that would also work??

As a total beginner, I hadn’t come across your use of Brackets and OR before. I will endeavour to research this coding further.

Thanks again. Much appreciated.

Regards.

CT

[mikekaganski]

@Clearwell_T

trying to use SUMIF(S) with the <> operator to solve this problem. Is that an approach that would also work?

You could invent a formula using SUMIFS for the task, but that would be a hack of using a wrong tool tor the job. The idea of SUMIF(S) is to allow to pick some values of a list, based on some criteria; a simple case is “sum only non-negatives” (note how it’s different from “sum all, but only if they are all non-negatives”).

[fpy]

@PKG

OR(

=IF(A1*B1, A1+B1, 0)

[Clearwell_T]

Thanks so much Guys,

I’m experimenting with your suggestions in my sheets as we speak, and learning more and more by the second!!

@mikekaganski
Thanks for the feedback. I actually have another amendment/refinement of my sheets that I was going to try next, and your “sum only non-negatives” example is exactly what I was thinking I would need (ie: picking out negative or non-negative numbers from a list for Counting/Summing/Averaging).

@fpy
I take it you’re suggesting a refinement or alternative implementation of PKG’s approach?
If so, a question if I may: Do the spaces in your formula have a meaning or function?

Cheers again all.

Regards.

CT

[robleyd]

@Clearwell_T

Do the spaces in your formula have a meaning or function

Ease of reading.

[fpy]

@Clearwell_T

suggesting

just a reminder of the logical interpretation of values :
(even if the use of “non-logical” is questionable here – should read non-boolean, I guess )

Logical Functions

@Clearwell_T

function

just readability.

[mikekaganski]

Even shorter variant:

=(A1+B1)*(A1*B1<>0)

Of course, shorter formula string doesn’t necessarily mean faster formula, nor easier to comprehend (and thus, to maintain). In general, the “find the shortest” is just a sport for fun

Note also, that different localizations have different localized argument separators (and other separators, too): e.g., en-US uses comma , as function argument separator, giving functions like =SUM(A:A, C:C); while many others use semicolon ; for that, resulting in =SUM(A:A; C:C). This (and other locale specifics, like decimal / thousand separators) could create problems, when you copy/paste from an answer to your cell. For the argument separator, the solution is to use the standard semicolon separator, when posting here; it is accepted everywhere, even if another one is used for display. Unfortunately, there’s no similar convenience method for other locale-specific things.

[fpy]

@mikekaganski

shorter

by =LEN(FORMULA()) ?

[mikekaganski]

Yes and yes, you are correct, that your formula will become shorter, if you strip spaces. And it may become even shorter, if you remove the ;0, relying on the default return value of FALSE (which is 0, but may get output as “FALSE” unexpectedly for “General” number format).

However, both my and your formulas, while demonstrate boolean arithmetic specifics of Calc, still are not as robust as @PKG’s. E.g., they would fail for A1 = B1 = 1E300, giving #NUM! instead of expected 2.00E+300.

[Villeroy]

@mikekaganski

Note also, that different localizations have different localized argument separators (and other separators, too): e.g., en-US uses comma

Semicolon works with all localizations because it does not conflict with any known decimal separator or thousands separator.
=LOOKUP(A1 ; B1:B7 ; C1:C7) changes to LOOKUP(A1 , B1:B7 , C1:C7) when entered into Einglish locale context.

[fpy]

@mikekaganski

specifics of Calc

rather common to (most) progamming languages; at least java, C, python, perl, SQL …

[mikekaganski]

In VBA, TRUE is -1. It’s best to avoid such generalizations, and be careful with expectations. These things are always specifics of some concrete convention (and no, conventions of spreadsheet formulas should not be mentally merged with any programming languages; such a wrong merge hits back often, e.g. when people expect Basic functions to work the same way as same-name Calc functions).

[Clearwell_T]

Hey, wow, thanks Guys.

I got a lot more out of this conversation than I would have naively assumed from the humble-ness of my simple question.

And I’m definitely the type who will insist to myself that I satisfy my own Rainman instincts, by opting to use the semi-colon as argument separation henceforth!?!

@fpy

@fpy

just readability.

OK, got it thanks. I thought it might have been yet another layer of Ninja level logical string reduction!!

Thanks again everyone.

Regards.

CT

[sokol92]

@mikekaganski

Of course, shorter formula string doesn’t necessarily mean faster formula,

We can try to measure the speed, although for this particular case it has no practical significance.

Sub Test
  TestSpeed "=IF(A1*B1;A1+B1;0)"
  TestSpeed "=(A1+B1)*(A1*B1<>0)"
End Sub

Sub TestSpeed(Byval formula As String)
  Dim oDoc As Object, oSheet As Object, t As Long 
  oDoc = StarDesktop.LoadComponentFromUrl("private:factory/scalc", "_default", 0, Array())
  oSheet=oDoc.Sheets(0)
  oSheet.getCellByPosition(10, 0).formula=formula
  t=getSystemTicks()
  oSheet.getCellRangeByPosition(10, 0, 10, 1000000).fillAuto 0, 1
  Msgbox  getSystemTicks() - t
End Sub

My results are 2970 and 1960.

[Clearwell_T]

Hi @mikekaganski,

@mikekaganski

The idea of SUMIF(S) is to allow to pick some values of a list, based on some criteria; a simple case is “sum only non-negatives”

If you’re still around, would you be able to suggest a formula that I can use as a starting point?? My situation is that I have a column of positive and negative currency values, and I would like to create the correct formulas to produce:

A) a Count of each of the positive and negative numbers (excluding any empty or zero valued cells)

B) a Sum of each of the lists of positive and negative numbers.

and

C) an Average of each of the positive and negative numbers in the list.

Once again. any help you can offer is much appreciated. I’ll understand if you’re busy, and I’ll get my coat.

Regards.

CT

PS: Sorry, here goes the Aspbergers again: that should have read … "I would like to create the correct formulae to produce ,!

[Clearwell_T]

Hey @sokol92,

Wowsers. Full recruit’s salute to your nerdy flex there!!

But you will definitely be due for a ruddy good Geek-Slap® if you keep up those antics all day!!?!

Love it.

CT

[Villeroy]

A)
B)
C)
Just as documented: Statistical Functions Part One
COUNTIF(Range; Criterion) where Criterion is some text or number or a concatenation of an operator with a text or number.
COUNTIF(A1:A99 ; Z1) counts how often the text or number in Z1 occurs in A1:A99. Text comparison is case-insensitive. COUNTIF(A1:A99 ; "="&Z1) (concatenation with equal operator) is equivalent to COUNTIF(A1:A99 ; Z1).
COUNTIF(A1:A99; ">="&Z1) counts how often a text or number occurs in A1:A99 that is bigger or equal than the value in Z1.
With the criterion “hard coded” in the formula,COUNTIF(A1:A99 ; ">=0.5") counts how often a number greater than or equal to 0.5 occurs in A1:A99. I would prefer the solution with the reference to a cell because editing the cell is easier than editing a formula and because other formulas can reference the same reference Z1.
SUMIF and AVERAGEIF work in the exact same way plus one optional argument: SUMIF(Range; Criterion [; SumRange])
If you append the optional 3rd argument “SumRange” after “Range” and “Criterion”, that range will be summed up instead of the initial range. Since sums and averages apply to numbers only, any text in the cell ranges will be ignored and any textual criterion will raise an error (except for the concatenation of operator & number which is a text).

[erAck]

@sokol92

We can try to measure the speed

Note that doesn’t measure expression calculation but the overall process of inserting a formula over a large range of cells while creating a series, compiling all and interpreting all. You don’t know what actually happens for each nor in the background.