Quotes, Logic Signs

[CR2515]

Can someone explain why in the formula:

=COUNTIFS(Sales,">="&H1,Sales,"<"&G2)

quotes are used? I know about concatenation but why are the logic signs in quotes and why couldn't the formula just as well be written as:

=COUNTIFS(Sales,>=&H1,Sales,<G2)

???

I know words that appear in quotes are treated as text but in the case above are we really wanting the logic symbols to be treated as text? By this I mean don't they have to produce a TRUE or FALSE upon being entered; that is we want them to change and not stick as text wherever they're seen?

In Excel terms what are their different meanings (the two cases above)?

Thanks!

 

[Aladin Akyurek]

Re: Quotes, Logic Signs Help.

That's the syntax as designed: Formulas with COUNTIF(S), SUMIF(S), AVERAGEIF(S), MINIFS, and MAXIFS must simply obey that syntax.

The relational operators [ >, <, >=, <=, <>, = ] must be put between quotes, period.

Concatenation (&) is required when the criteria/conditions are located in cells:

COUNTIFS(A2:A8,"=KAD")

counts all occurrences of the KAD string in A2:A8.

If the KAD string is located in some cell, say, in X2, the contents of X2 must be put in the "..." part of the function:

In

=COUNTIFS(A2:A8,"="&X2)

the concatenation action evaluates X2 and showels the evaluation result, that is, the contents of X2 in "=", creating "=KAD".

By the way,

in ">10" 10 is treated as number and in "=KAD" as text.

To recap:

COUNTIFS(Sales,>=&H1) is illegal, while COUNTIFS(Sales,">="&H1) is correct syntactically.

 

[CR2515]

Re: Quotes, Logic Signs Help.

Many, many thanks. Bravo!

 

[CR2515]

Re: Quotes, Logic Signs Help.

One other thing when formula:
=IF(B29">="B$26,C$26,IF(B29>=B$25,C$25,IF(B29>=B$24,C$24,IF(B29>=B$23,C$23,IF(B29>=B$22,C$22,0)))))*B29

is entered, it works despite there not being any quotation marks around ">=" signs.

Why is this?

Which formulas are quotes not required around the logic symbols?

 

[Aladin Akyurek]

Re: Quotes, Logic Signs Help.

One other thing when formula:
=IF(B29">="B$26,C$26,IF(B29>=B$25,C$25,IF(B29>=B$24,C$24,IF(B29>=B$23,C$23,IF(B29>=B$22,C$22,0)))))*B29

is entered, it works despite there not being any quotation marks around ">=" signs.

Why is this?

Which formulas are quotes not required around the logic symbols?

=IF(B29">="B$26,C$26,IF(B29>=B$25,C$25,IF(B29>=B$24,C$24,IF(B29>=B$23,C$23,IF(B29>=B$22,C$22,0)))))*B29

is syntactically incorrect because of the B29">="B$26 bit. Excepting the database functions like COUNTIFS, etc, it's illegal (syntactically incorrect) to enclose the reational/comparison operators (=, >, <, etc.) between quotes of any kind. By the way, OR, AND, NOT, XOR, IF are used to effect logical operations.

So the formula quoted above should be:

=IF(B29>=B$26,C$26,IF(B29>=B$25,C$25,IF(B29>=B$24,C$24,IF(B29>=B$23,C$23,IF(B29>=B$22,C$22,0)))))*B29

This can probably be re-expressed as a look up formula:

=VLOOKUP(B29,B$22:C$26,2,0)*B29

 

[CR2515]

So for logical formulas you don't have to use quotes around the operators? Formulas such as and(), if(), or()?

But for you'd put the operators in quotes for formulas like sumifs(), averageifs(),countifs(), ect? What are these formulas classified again as?

If you've had to make a formula that's not found in excel like mode(if()) then would also put any operator in quotes?

Thanks for the help.

 

[Aladin Akyurek]

So for logical formulas you don't have to use quotes around the operators? Formulas such as and(), if(), or()?

But for you'd put the operators in quotes for formulas like sumifs(), averageifs(),countifs(), ect? What are these formulas classified again as?

If you've had to make a formula that's not found in excel like mode(if()) then would also put any operator in quotes?

Thanks for the help.

To recap:

1. COUNTIF(S), SUMIF(S), AVERAGEIF(S), MINIFS, and MAXIFS are range-processing database functions.

2. =, <, >, >=, <=, and <> are the so-called relational (comparison) operators.

3. OR, AND, IF, NOT, and XOR ore so-called logical operators.

4. Functions listed in [1] work with citeria (conditions). Most spreadsheet software, Lotus-1-2-3, Excel, Gnumeric, require putting criteria in-between double quotes, like in

">=0.25"

">9/7/2017"

"="&A1

etc.

5. Text values like Jon, London, etc. must be double-quoted in all types of formulas like in

=IF(A2="London","UK","")

6. Numbers are generally not double-quoted in formulas like in

=IF(X2=10,0.2,0)

7. Logical operators has nothing to do with the requirement stated in [1]. Examples:

=OR(A2="London",A2="Sinope")

=AND(C2=1,D2="new York")

8. Thus, double quoting relational operators (in [1]) and double quoting text values as in [5] and [7] must not be confounded.

9. Relational operators used with functions other than the ones mentioned in [1] are NOT double-quoted:

=IF(A1=10,1,0)

=SUMPRODUCT(--(A2:A4="London",--(B2:B4>="8/9/2017"+0))

 

[CR2515]

Thank you so much!
Very helpful.

 

[Aladin Akyurek]

Thank you so much!
Very helpful.

You are welcome.