What's wrong with this formula?

[Harvey Waxman]

{=IF(G18:G17800<>"",SUM(F18:F17800)/SUM(D1817800)-1)}

I want the percentage change between all the total of the items in
column D and Column F where the corresponding cell in column G is not
blank.

I get FALSE as the answer.

What am II doing wrong?

Thanks

 

[JE McGimpsey]

{=IF(G18:G17800<>"",SUM(F18:F17800)/SUM(D1817800)-1)}

I want the percentage change between all the total of the items in
column D and Column F where the corresponding cell in column G is not
blank.

I get FALSE as the answer.

Since some cells in G18:G17800 is blank, the conditional:

IF(G17:G17800<>""

returns FALSE, so the function returns FALSE overall.

One alternative:

=SUMIF(G18:G17800,"<>",F18:F17800)/SUMIF(G18:G17800,"<>",D1817800)- 1

 

[Harvey Waxman]

Since some cells in G18:G17800 is blank, the conditional:

IF(G17:G17800<>""

I don't get it. In this formula,

returns FALSE, so the function returns FALSE overall.

One alternative:

=SUMIF(G18:G17800,"<>",F18:F17800)/SUMIF(G18:G17800,"<>",D1817800)- 1

I hope you guys stay healthy for a long time. I'll never understand
some of these.

Thanks

 

[JE McGimpsey]

I don't get it. In this formula,
{=SUM(IF(G18:G10800<>"",J18:J10800))}, I don't get a FALSE.

Look at the difference between the two formulae. In the one that doesn't
work:

=IF(G18:G17800<>"", SUM(....))

the values in G18:G17800 are evaluated and if ALL of them are not blank,
then the true branch (the various SUM()s) is executed. If even one is
not blank, the false branch (the default "FALSE") is executed.

In your example that does work:

=SUM(IF(G18:G17800<>"",...

the same conditional is evaluated, and an array of true branch (the
values in J18:J17800) and false branch (the value FALSE) is returned,
and THEN that result is evaluated by SUM(), which ignores the FALSE
values.

 

[Harvey Waxman]

Look at the difference between the two formulae. In the one that doesn't
work:

=IF(G18:G17800<>"", SUM(....))

the values in G18:G17800 are evaluated and if ALL of them are not blank,
then the true branch (the various SUM()s) is executed. If even one is
not blank, the false branch (the default "FALSE") is executed.

In your example that does work:

=SUM(IF(G18:G17800<>"",...

the same conditional is evaluated, and an array of true branch (the
values in J18:J17800) and false branch (the value FALSE) is returned,
and THEN that result is evaluated by SUM(), which ignores the FALSE
values.

Thanks for your patience.

In this which you offered:
=SUMIF(G18:G17800,"<>",F18:F17800)/SUMIF(G18:G17800,"<>",D1817800)- 1
which does exactly what I want, I have never seen this operator "<>"
before. Does it mean 'nothing' and does "=" mean 'anything'?
Can this be used with an IF statement in the same way?

 

[Carl Witthoft]

In this which you offered:
=SUMIF(G18:G17800,"<>",F18:F17800)/SUMIF(G18:G17800,"<>",D1817800)- 1
which does exactly what I want, I have never seen this operator "<>"
before. Does it mean 'nothing' and does "=" mean 'anything'?
Can this be used with an IF statement in the same way?

RTFM comes to mind... but yes, there are many ways that "Not Equal" is
symbolized in different languages. Excel happens to use <>,
idiomatically "either greater than or less than" i.e. not equal. In
other languages you may see ~= or != .


Beware the often tricky difference between "=" and "==" , the first of
which is an operation setting the left side equal to the right, and the
second of which is a comparison. Except that Excel foolishly allows
"=" to be used as a comparison operator, e.g. IF(x=y,"same","notsame")

 

[Harvey Waxman]

In this which you offered:
=SUMIF(G18:G17800,"<>",F18:F17800)/SUMIF(G18:G17800,"<>",D1817800)- 1
which does exactly what I want, I have never seen this operator "<>"
before. Does it mean 'nothing' and does "=" mean 'anything'?
Can this be used with an IF statement in the same way?

RTFM comes to mind... but yes, there are many ways that "Not Equal" is
symbolized in different languages.[/QUOTE]

I know that <> means not equal to ; "<>0 " means not equal to 0. But
"<>" - is not equal to what? It's something I hadn't seen before.