Plus or Minus in Formula

English
#1

Need to use the symbol ± ("+ or −") in a formula.
Plus or Minus

=IF(RC[-3]="","",IF(RC[-4]+0.5=OR(RC[-12],RC[-10],RC[-8],RC[-6]),“Plus”,“Minus”))
What I am trying to achieve is that - if the contents of cell RC[-4] plus 0.5 equals the contents of any (as in OR group) of cells RC[-12],RC[-10],RC[-8],RC[-6], then it shows “Plus” and if not, it shows “Minus”. Current result, it only shows “Minus” when it should be “Plus” in some cases (rows).

I’ve tried various ways of achieving this but have failed. i.e. (1.) the above formula, (2.) to put the (Excel) ± symbol in IF(RC[-4]±0.5 into formula, (3.) =IF(RC[-3]="","",IF(OR(RC[-12],RC[-10],RC[-8],RC[-6])=RC[-4]+0.5,“Y”,“DIFF”))
Result “DIFF” even when it should be “Y”.

(By the way I use R1C1 because it is visually logical. It helps me in writing my spreadsheets which have hundreds of columns of different formulas)

Can anyone see/tell me what I am doing wrong and suggest a solution.
Thanks.

#4

Somebody looking for an already available answer concerning a related issue surely would not search for “plus or minus”, would he(f/m)? Answers given here are therefore likely to “vanish in the dark” soon.
What the question is actually about is “How to apply OR() with conditions concerning non-contiguous cells?” In place of “non-contiguous”, “arbitrary” may be preferable.

#5

LUPP
Hi thanks for your input and sugesstions - which I will try in due course - I really will try your solution.

As for your comment on "vanishing in the dark soon -
I am an artist in oil painting, although I’ve had a technical career all my life to earn money and pay my way - I suspect I’ve been around a lot long than you, AND when I first started using a computer you weren’t even a sparkle in your daddy’s eye. Oh and computers weren’t much better than a proximity switch controlled by a capacitor the size of a brick.

As an artist - the problem I have is that our two languages -me of Art- and you of Programming/Code - and in this case Spreadsheets - are sort of a south pole and north pole situation.

If I explained what I am trying to do - I doubt you would understand. However I do genuinely appreciate your “technical” input.

KEME1
Hi - thanks very much for your input. Just a point that I didn’t mention and that is that the value 0.5 can only turn up once in each row selection of the the 4 columns if it is there at all. These 4 column values are the product of quite a few previous column formulas that search and descriminate to produce among others this value 0.5.

I will try your formula, of course to see if its output is something I can use to proceed further with my spreadsheet that analyses this data.

What I usually find is that it is not what I am trying to do that is the problem but I am just using the wrong function. That is to do with my vision of logic, my weakness.

LUPP
Thanks for your subsequent comment - just to say that I have written a formula that does jump over rows - it selects any 1 row from any 22 previous rows. So you should have more confidence in this spreadsheet program.

Thank you both for your input.

#7

I was born in 1944 - almost at the same time as the first computers. My comment was also in no way about different views based on different professional experience. It was about the use of help forums (or Q&A sites): It would be advantageous to formulate questions (the core, which appears as a heading) in such a way that someone who has a similar problem can find the answer without getting distracted by words not actually related to the issue. As far as I could understand your intentions at all, they surely aren’t related substantially to “Plus or Minus”. Without any specifically “technical” way of looking at your additional explanations your question is about

  1. how to correctly use the OR() function in Calc formulas.
  2. how to probably simplify the usage if more than one parts inside the OR() have something in common.

The answers you got are basically, but now repeated in a slightly more technical language:

  1. Use OR(firstPart; secondPart; ...) in a way that every single part can return a logical result (TRUE or FALSE). The parts don’t remember something “already said previously” as humans tend to expect a listener when talking (at least when using Western languages).
  2. OR() can accumulate partial results if used with array-expressions on its argument positions. This can substantially simplify expressions, if the design of the sheet doesn’t thwart the effect - by interlacing sequences of rows or columns e.g. instead of allowing to reference contiguous ranges where accumulation might be useful.
    There are lots of accumulating functions. Regarding this when designing sheets is therefore recommendable.
#2

The OR() function is not a way to present a selection list to a compare operation. It is a logical function which takes logical values (TRUE and FALSE) and returns a logical value. The return value will be TRUE if any (one or more) of the given arguments evaluates to TRUE, and FALSE otherwise.

Your formula then proceeds to compare this logical value to +0.5. In most cases, the calculated value will not equal the returned TRUE value. More about that below.

What you need is to search for your value in a list. The list of compare items is not continuous, but you may still be able to use e.g. MATCH() to see whether the value is found in the range RC[-12]:RC[-6]. If the cells in between are likely to hold “false matches” you also need to filter according to even/odd column number. MATCH() will return an error if there is no match, so you need to use ISERROR() to catch that.

This might work (simplified formula which does NOT catch “false matches” in the alternate cells):
=IF(RC[-3]="";"";IF(ISERROR(MATCH(RC[-4]+0.5;RC[-12]:RC[-6];0);“Minus”;“Plus”)))

Logical (boolean) values in Calc can be used interchangeably with numerical values.

  • A returned TRUE value will yield the numerical value 1.
  • A returned FALSE value will yield the numerical value 0.
  • A formula expecting logical value will take zero as FALSE, and every nonzero number is taken as TRUE.
1 Like
#6

LUPP
Hi thanks for your input and sugesstions - which I will try in due course - I really will try your solution.

As for your comment on "vanishing in the dark soon -
I am an artist in oil painting, although I’ve had a technical career all my life to earn money and pay my way - I suspect I’ve been around a lot long than you, AND when I first started using a computer you weren’t even a sparkle in your daddy’s eye. Oh and computers weren’t much better than a proximity switch controlled by a capacitor the size of a brick.

As an artist - the problem I have is that our two languages -me of Art- and you of Programming/Code - and in this case Spreadsheets - are sort of a south pole and north pole situation.

If I explained what I am trying to do - I doubt you would understand. However I do genuinely appreciate your “technical” input.

KEME1
Hi - thanks very much for your input. Just a point that I didn’t mention and that is that the value 0.5 can only turn up once in each row selection of the the 4 columns if it is there at all. These 4 column values are the product of quite a few previous column formulas that search and descriminate to produce among others this value 0.5.

I will try your formula, of course to see if its output is something I can use to proceed further with my spreadsheet that analyses this data.

What I usually find is that it is not what I am trying to do that is the problem but I am just using the wrong function. That is to do with my vision of logic, my weakness.

                                          LUPP

Thanks for your subsequent comment - just to say that I have written a formula that does jump over rows - it selects any 1 row from any 22 previous rows. So you should have more confidence in this spreadsheet program.

Thank you both for your input.

#8

One thing I did mention, albeit in technical terms, is that the OR() function does not provide for successive comparison of its parameter values. Here is what happens in that IF() condition:

  • Right side: OR() checks all of the input parameters. If they are all zero the function returns FALSE/zero, and if one parameter contains a nonzero number it returns TRUE/one.
    Right side of the compare is 0 or 1. It cannot be any other value.
  • Left side: evaluated as <cell value>+0.5.
  • The IF condition is then
    • TRUE when the OR-ed cells are all zeroes and the left side <cell value> is exactly -0.5 (negative) Condition then evaluates to (0=0) which is TRUE.
    • TRUE when at least one of the OR-ed cells is a nonzero number and the left side <cell value> is exactly 0.5 (positive) Condition then evaluates to (1=1) which is TRUE.
      Those two specific TRUE conditions will yield a return value of “Y”
    • FALSE for every other constellation.
      This will happen for most cases, yielding the return value “DIFF” as you have noticed.
#3

In addition to what @keme1 stated I would like to stress that a sheet design needing to “jump over columns” (or over rows) is generally doubtable, and will again and again cause issues of the kind discussed here.
Check the attached example for more details.
disask83626everyNthcolrowAbstention.ods (17.3 KB)

1 Like
#9

LUPP/KEME1

Thanks for the reply, and I apologise for not titleing/categorising my question correctly. This is similarly a misunderstanding based on my “expectations” of the logic in dialogue boxes - -
as with spreadsheets the limitations of the function (OR) that I am trying to use. I just don’t think like a code writer. I like to use the word “Intuitive”.
I am not that technical and am obviously trying to use functions without fully understanding the context under which they operate/work.

I also apologise for my comment about your age, as you are actually four years older than I am.

Thank you for taking the time explaining in a detailed way whats going on.

Regarding my comment about a previous formula that compares and selects one cell entry (column a) and a previous entry (in column b) out of 22 previous rows to find any previous entry and measure/count the gap (variable) of empty rows between the two (found entries). This uses the AND function, so I got away with it somehow.

As I said previously, I will try the suggestions you have made to see if I can get the result I want to help me proceed.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.