Formula Syntax - What it means?

  • Thread starter Thread starter QTE
  • Start date Start date
Q

QTE

Hi Excel Forum,

I believe the following formulas all find the MINIMUM value that is NO
= zero (<>0) and produce the same results. I am unsure of their syntax
Can you explain the syntax of the following formulas and th
differences between them:

=MIN(IF(A1:A20,A1:A20))

=MIN(IF(A1:A20<>0,A1:A20))

=MIN(IF(A1:A20<>0,A1:A20,""))

How does the first formula work and, if there is a zero 0 avoi
returning a zero 0 without mentioning NOT = zero <>0?

what are the quotation marks for and what do they do?

When would it be preferable to use one syntax rather than another?

What signifies or denotes an array formula?

Your assistance is appreciated.

Kind regards,
QT
 
Hello QTE,
Try this:
In A1:A5 enter -2,-1,0,1,2
In B1 ENTER =IF(A1,A1) and copy down to B5. Excel must look at A1 and see if
it is "true". But it is a number! Excel treats all non-zero numeric values
as TRUE. So you will get -2,1, FALSE in B1:B5
In C1 enter =IF(A1<>0,A1) and copy down to C5. You will get the same
results. Now the test is "Is A1 not zero" rather than"Is A1 TRUE"
In D1 enter =IF(A1<>),A1,0) and copy to D5. Now you will get -2,-1, empty
cell, 1,2. The syntax for IF is =IF(condition,true_result, false_result). In
B1 and C1 we did not bother to say what Excel should return when the result
is false, so Excel supplied FALSE (very kind!) In D1 we have specified what
then the condition is false we want nothing (null, zilch, narda) to be
returned. We do with an empty set of quotes.

Which is best? Well it was shorter to type B1 than C1 or D1.

Here endeth the lesson. For info on array formulas visit Chip's site at
www.cpearson.com.

Best wishes
 
Hi Bernard,

Thank you so much for your explanation and examples, and referral to
www.cpearson.com (your link has a stray full-stop after the word com
so you get the page not found error if using that link).

www.cpearson.com - Good formula array information.

Cheers
QTE

Bernard said:
*Hello QTE,
Try this:
In A1:A5 enter -2,-1,0,1,2
In B1 ENTER =IF(A1,A1) and copy down to B5. Excel must look at A1 an
see if
it is "true". But it is a number! Excel treats all non-zero numeri
values
as TRUE. So you will get -2,1, FALSE in B1:B5
In C1 enter =IF(A1<>0,A1) and copy down to C5. You will get the same
results. Now the test is "Is A1 not zero" rather than"Is A1 TRUE"
In D1 enter =IF(A1<>),A1,0) and copy to D5. Now you will get -2,-1
empty
cell, 1,2. The syntax for IF is =IF(condition,true_result
false_result). In
B1 and C1 we did not bother to say what Excel should return when th
result
is false, so Excel supplied FALSE (very kind!) In D1 we hav
specified what
then the condition is false we want nothing (null, zilch, narda) t
be
returned. We do with an empty set of quotes.

Which is best? Well it was shorter to type B1 than C1 or D1.

Here endeth the lesson. For info on array formulas visit Chip's sit
at www.cpearson.com.

Best wishes
Click to expand...
[/QUOTE]
 
Back
Top