List of Commands?

J

Jennifer

I noticed a lot of you use different symbols in your functions, such as &, *,
^, etc. It there a glossary or a list of these commands somewhere that tells
you what they do and what they mean?

Thanks,

Jennifer
 
E

Elkar

Look up "calculation operators" in Excel Help. You should find a list there.

HTH,
Elkar
 
J

Jennifer

Thank you Elkar.

I found a list, but it wasn't exactly what I was looking for. The list had
more to do with math than anything else. I probably should have been more
specific.

For example, I used this function to find how many times a row of cells
contained a word from another cell.
=COUNTIF(D:D,"*"&A1&"*")
I saw it in someone else's function, and it worked for me.

But how do I know that the * symbol was the right one to use?

Thanks again,

Jennifer
 
D

Dave F

In this context, the * is a wildcard character.

In other contexts, * is a multiplication operator. As with everything,
context helps. With the formula you give below, you know that you're not
multiplying anything; therefore, the * has to operate as something else.
Experience suggests it is a wildcard operator.

The amerpsand (&) concatenates (joins) text strings together. It does not
have a mathematical function.

As to the question of how do you know what something in excel does: my
experience is that the collective knowledge of the participants of these
newsgroups is greater than Excel's help files ever will be. So: I tend to
rely on the newsgroups for answers to questions such as yours, and not
Excel's help.

Dave
 
E

Elkar

The asterisk here is used as a wildcard character (you can look this up in
Help too). There are two types of wildcards, * and ?.

The asterisk is used to represent a string of characters of any length
(includig zero length), while the question mark is used to represent a single
character. So, in your formula, it searches for the contents of cell A1 with
anything before or after it. If you'd used the ? instead, it would only
search for the contents of A1 with any single character before or after it.

HTH,
Elkar
 
J

Jennifer

Thank you.

So, I have another question then. Why does the Countif function work, but
when I used it in a Sumproduct function, it gives me zero?
=SUMPRODUCT((E1:E1000<EDATE(TODAY(),-6))*(D1:D1000="*"&A1&"*"))
Am I doing something wrong here? I know there should me more than zero.

Jennifer
 
E

Elkar

In your SUMPRODUCT function, the * is being treated as an asterisk, not a
wildcard. Try using the SEARCH function instead.

=SUMPRODUCT(--(E1:E1000<EDATE(TODAY(),-6)),--(ISNUMBER(SEARCH(A1,D1:D1000))))

SEARCH returns a number indicating the starting position where the text
string is found. If not found, it returns an error. Hence the use of the
ISNUMBER function.

HTH,
Elkar
 
J

Jennifer

Yay! It worked. Thank you!

Elkar said:
In your SUMPRODUCT function, the * is being treated as an asterisk, not a
wildcard. Try using the SEARCH function instead.

=SUMPRODUCT(--(E1:E1000<EDATE(TODAY(),-6)),--(ISNUMBER(SEARCH(A1,D1:D1000))))

SEARCH returns a number indicating the starting position where the text
string is found. If not found, it returns an error. Hence the use of the
ISNUMBER function.

HTH,
Elkar
Click to expand...
 
Top