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 thei
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
 
Hi QTE

sounds like homework to me :)

i'll let the others comment on which is the most efficient formula to use
but will answer one of the questions for you -

what are the quotation marks for and what do they do?
quotation marks are using within formulas and functions to signify text.
In this case (third formula) the quotation marks surround nothing (an empty
string) which means return nothing (not a space / not a zero - just nothing)
if false is returned on the IF test.

Cheers
JulieD
 
....which means return nothing (not a space / not a zero -
just nothing)

Technically, it returns a zero length *text* string. This
can cause errors in formulas downstream that test for or
use numeric values referenced in those cells.

Biff
 
Hi Biff

can you please provide an example of a problem "downstream" as all my
(quick) tests have the same result if i use force a cell to "" and if the
cell has not been "touched" (other than an ISBLANK())

Cheers
JulieD
 
Hi,
Here's a good example where the "" caused some problems:
http://tinyurl.com/36mox
Click to expand...

Not a good example : it doesn't apply here.

In the current case, all zero length strings or FALSE are 'intermediary results'
within the formula and will have been eaten up by the MIN() function (giving 0
if all are FALSE or "") returning a scalar. So there are NO cases where
'downstream' problems can occur.

Regards,

Daniel M.
 
Not a good example : it doesn't apply here.

Correct!

The point I was trying to make, but possibly failed to
convey, is that a null string, "", is not nothing. It is
something that may need to be considered.

Let me rephrase my original post:

In some cases, a cell that contains the null string, "",
can cause problems downstream if one does not account for
the possibility of the presence of the null string.

Biff
 
Back
Top