List of Commands?

[Guest]

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

 

[Guest]

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

HTH,
Elkar

 

[Guest]

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,"*"&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

 

[Gord Dibben]

Check out Excel Help on "operators"


Gord Dibben MS Excel MVP

 

[Guest]

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

 

[Guest]

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

 

[Guest]

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))*(D11000="*"&A1&"*"))
Am I doing something wrong here? I know there should me more than zero.

Jennifer

 

[Guest]

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,D11000))))

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

 

[Guest]

Yay! It worked. Thank you!

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,D11000))))

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