Array enter (enter using Ctrl-Shift-Enter) a formula like

=AVERAGE(IF(NOT(ISERROR(C2:C16)),C2:C16))

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
Bernie you have been a great help. Last question. Using the Averageif
Function. How can i average all numbers in my worksheet ignoring the
#DIV/0

"Bernie Deitrick" wrote:

For example,

Data!P2:Data!P2957

Should be:

Data!$P$2:$P$2957

HTH,
Bernie

"Kcope8302" wrote in message
...
Thank you, That worked for me. I do have one more question. I am having
an
issue with excel auto counting when i past an equation. When i past it,
the
program automatically starts count up from the origin point. Is there a
way
to state in an equation what is allowed to count up and what isnt.

Example:
=AVERAGEIFS(Data!P2:Data!P2957,Data!C2:Data!C2957, A2,Data!B2:Data!B2957,"Req")

The only point that I want to count is the A2. I would like the other
statements to remain as I had them. Is there a method of doing this
without
having to past and then go back and alter each cell by itself?

"Bernie Deitrick" wrote:

Put your list of 7 words in F2:F8, and the abbreviations in G2:G8.

Then, in B4, array enter (enter using Ctrl-Shift-Enter)

=INDEX($G:G,MIN(IF(ISERROR(SEARCH($F$2:$F$8,A4)),1 000,ROW($F$2:$F$8))))

and copy down to match column A.

HTH,
Bernie
MS Excel MVP


"Kcope8302" wrote in message
...
I am trying to search for specific words in column A and then print
an
abbrviated version of those specific words in Column B. There is a
set
of 7
words that I want to search for and if one is found i want to print
the
abbreviated word if not I want to move to the next word I am
searching
for.

I have been trying to use the If, Lookup and search functions. I
have
not
been successful. Example with 2 of the words I am searching for. I
want
this
to be one string so I can just place in column B.

=IF(SEARCH("Functional",A4),"Fs",IF(SEARCH("Techni cal",A4),"ts")