Below, I entered what goes into A2, A3, and A4, respectively. They
get entered as text. Notice that only the first line of this is
what's different.

A2
- Derivatives 36 * - -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A3
- Derivatives 36 - * -
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

A4
- Derivatives 36 - - *
- Economics 18 * - -
- Equity Analysis 72 - - *
- Ethical & Professional Stnds. 36 - - *
- Financial Statement Analysis 108 - - *
- Fixed Income Analysis 36 - * -
- General Portfolio Management 36 * - -
- Quantitative Analysis 18 - * -

In cell b2, I'd like to print out an output which corresponds to the
"Derivatives 36" score. The score is either "* - -", "- * -", or "- -
*". If it's the first case (with the asterisks all the way to the
left), then the score is 0.40, second case is a 0.6, and finally,
0.8.

I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
as an option, but I'm having great difficulty with the syntax. I'd
basically like to create an algorithm with the following syntax/logic
for B2:B4:

=if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
(isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)

But this is syntatically wrong. Keep in mind, that I'd like to enter
0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
is in the middle, and a 0.8 if the asterisk is at the right.

Once you help me with this, then I can figure out the rest for the
entire scores (for Economics, etc.) on my own.

Please give me guidance and/or suggestions.

With your posted values in A2:A4

Try something like this:

B2: (a search term....eg Economics 18)
C2: =0.2+MATCH(SUBSTITUTE(TRIM(MID(A2,SEARCH(B2,A2)+LEN(B2),6)),"*","|"),{"|
- -","- | -","- - |"},0)*0.2

Using Economics 18 in C2
that formula returns 0.4

Is that something you can work with?
***********
Regards,
Ron

XL2003, WinXP


"Stem sells" wrote:

> Below, I entered what goes into A2, A3, and A4, respectively. They
> get entered as text. Notice that only the first line of this is
> what's different.
>
> A2
> - Derivatives 36 * - -
> - Economics 18 * - -
> - Equity Analysis 72 - - *
> - Ethical & Professional Stnds. 36 - - *
> - Financial Statement Analysis 108 - - *
> - Fixed Income Analysis 36 - * -
> - General Portfolio Management 36 * - -
> - Quantitative Analysis 18 - * -
>
> A3
> - Derivatives 36 - * -
> - Economics 18 * - -
> - Equity Analysis 72 - - *
> - Ethical & Professional Stnds. 36 - - *
> - Financial Statement Analysis 108 - - *
> - Fixed Income Analysis 36 - * -
> - General Portfolio Management 36 * - -
> - Quantitative Analysis 18 - * -
>
> A4
> - Derivatives 36 - - *
> - Economics 18 * - -
> - Equity Analysis 72 - - *
> - Ethical & Professional Stnds. 36 - - *
> - Financial Statement Analysis 108 - - *
> - Fixed Income Analysis 36 - * -
> - General Portfolio Management 36 * - -
> - Quantitative Analysis 18 - * -
>
> In cell b2, I'd like to print out an output which corresponds to the
> "Derivatives 36" score. The score is either "* - -", "- * -", or "- -
> *". If it's the first case (with the asterisks all the way to the
> left), then the score is 0.40, second case is a 0.6, and finally,
> 0.8.
>
> I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
> as an option, but I'm having great difficulty with the syntax. I'd
> basically like to create an algorithm with the following syntax/logic
> for B2:B4:
>
> =if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
> (isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)
>
> But this is syntatically wrong. Keep in mind, that I'd like to enter
> 0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
> is in the middle, and a 0.8 if the asterisk is at the right.
>
> Once you help me with this, then I can figure out the rest for the
> entire scores (for Economics, etc.) on my own.
>
> Please give me guidance and/or suggestions.
>
>

On Sat, 18 Aug 2007 01:45:18 -0000, Stem sells <(E-Mail Removed)>
wrote:

>Below, I entered what goes into A2, A3, and A4, respectively. They
>get entered as text. Notice that only the first line of this is
>what's different.
>
>A2
>- Derivatives 36 * - -
>- Economics 18 * - -
>- Equity Analysis 72 - - *
>- Ethical & Professional Stnds. 36 - - *
>- Financial Statement Analysis 108 - - *
>- Fixed Income Analysis 36 - * -
>- General Portfolio Management 36 * - -
>- Quantitative Analysis 18 - * -
>
>A3
>- Derivatives 36 - * -
>- Economics 18 * - -
>- Equity Analysis 72 - - *
>- Ethical & Professional Stnds. 36 - - *
>- Financial Statement Analysis 108 - - *
>- Fixed Income Analysis 36 - * -
>- General Portfolio Management 36 * - -
>- Quantitative Analysis 18 - * -
>
>A4
>- Derivatives 36 - - *
>- Economics 18 * - -
>- Equity Analysis 72 - - *
>- Ethical & Professional Stnds. 36 - - *
>- Financial Statement Analysis 108 - - *
>- Fixed Income Analysis 36 - * -
>- General Portfolio Management 36 * - -
>- Quantitative Analysis 18 - * -
>
>In cell b2, I'd like to print out an output which corresponds to the
>"Derivatives 36" score. The score is either "* - -", "- * -", or "- -
>*". If it's the first case (with the asterisks all the way to the
>left), then the score is 0.40, second case is a 0.6, and finally,
>0.8.
>
>I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
>as an option, but I'm having great difficulty with the syntax. I'd
>basically like to create an algorithm with the following syntax/logic
>for B2:B4:
>
>=if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
>(isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)
>
>But this is syntatically wrong. Keep in mind, that I'd like to enter
>0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
>is in the middle, and a 0.8 if the asterisk is at the right.
>
>Once you help me with this, then I can figure out the rest for the
>entire scores (for Economics, etc.) on my own.
>
>Please give me guidance and/or suggestions.


Two ways, both assume that your strings are exactly as you posted -- with
spaces between the - * -

=IF(ISNUMBER(SEARCH("Derivatives 36 ~* - -",A2)),0.4,
IF(ISNUMBER(SEARCH("Derivatives 36 - ~* -",A2)),0.6,
IF(ISNUMBER(SEARCH("Derivatives 36 - - ~*",A2)),0.8,"Not Found")))

=CHOOSE(SEARCH("~*",MID(A2,SEARCH("Derivatives 36",A2)
+LEN("Derivatives 36"),6))/2,0.4,0.6,0.8)
--ron

Ron:

Thanks for your input. The good news is that the syntax behind your
logic statement is perfect.

However, I truly think that there maybe some other error preventing
the right answer from showing correctly (I tried the first option).
I'm always getting "0.4". I think that this may have something to do
with text and numerical characters. I've seen this error pop up in
the past.



On Aug 17, 10:24 pm, Ron Rosenfeld <ronrosenf...@nospam.org> wrote:
> On Sat, 18 Aug 2007 01:45:18 -0000, Stem sells <gestureofresp...@yahoo.com>
> wrote:
>
>
>
>
>
> >Below, I entered what goes into A2, A3, and A4, respectively. They
> >get entered as text. Notice that only the first line of this is
> >what's different.
>
> >A2
> >- Derivatives 36 * - -
> >- Economics 18 * - -
> >- Equity Analysis 72 - - *
> >- Ethical & Professional Stnds. 36 - - *
> >- Financial Statement Analysis 108 - - *
> >- Fixed Income Analysis 36 - * -
> >- General Portfolio Management 36 * - -
> >- Quantitative Analysis 18 - * -
>
> >A3
> >- Derivatives 36 - * -
> >- Economics 18 * - -
> >- Equity Analysis 72 - - *
> >- Ethical & Professional Stnds. 36 - - *
> >- Financial Statement Analysis 108 - - *
> >- Fixed Income Analysis 36 - * -
> >- General Portfolio Management 36 * - -
> >- Quantitative Analysis 18 - * -
>
> >A4
> >- Derivatives 36 - - *
> >- Economics 18 * - -
> >- Equity Analysis 72 - - *
> >- Ethical & Professional Stnds. 36 - - *
> >- Financial Statement Analysis 108 - - *
> >- Fixed Income Analysis 36 - * -
> >- General Portfolio Management 36 * - -
> >- Quantitative Analysis 18 - * -
>
> >In cell b2, I'd like to print out an output which corresponds to the
> >"Derivatives 36" score. The score is either "* - -", "- * -", or "- -
> >*". If it's the first case (with the asterisks all the way to the
> >left), then the score is 0.40, second case is a 0.6, and finally,
> >0.8.
>
> >I'm aware of "=IF(ISNUMBER(SEARCH("Derivatives 36 - * -", A2))......."
> >as an option, but I'm having great difficulty with the syntax. I'd
> >basically like to create an algorithm with the following syntax/logic
> >for B2:B4:
>
> >=if (isnumber(search"Derivatives 36 * - -", a2)), 0.4 (if
> >(isnumber(search"Derivatives 36 - * -", a2)), 0.6, 0.8)
>
> >But this is syntatically wrong. Keep in mind, that I'd like to enter
> >0.4 if the asterisk is all the way to the left, a 0.6 if the asterisk
> >is in the middle, and a 0.8 if the asterisk is at the right.
>
> >Once you help me with this, then I can figure out the rest for the
> >entire scores (for Economics, etc.) on my own.
>
> >Please give me guidance and/or suggestions.
>
> Two ways, both assume that your strings are exactly as you posted -- with
> spaces between the - * -
>
> =IF(ISNUMBER(SEARCH("Derivatives 36 ~* - -",A2)),0.4,
> IF(ISNUMBER(SEARCH("Derivatives 36 - ~* -",A2)),0.6,
> IF(ISNUMBER(SEARCH("Derivatives 36 - - ~*",A2)),0.8,"Not Found")))
>
> =CHOOSE(SEARCH("~*",MID(A2,SEARCH("Derivatives 36",A2)
> +LEN("Derivatives 36"),6))/2,0.4,0.6,0.8)
> --ron- Hide quoted text -
>
> - Show quoted text -

On Sat, 18 Aug 2007 06:03:45 -0000, Stem sells <(E-Mail Removed)>
wrote:

>Ron:
>
>Thanks for your input. The good news is that the syntax behind your
>logic statement is perfect.
>
>However, I truly think that there maybe some other error preventing
>the right answer from showing correctly (I tried the first option).
>I'm always getting "0.4". I think that this may have something to do
>with text and numerical characters. I've seen this error pop up in
>the past.

Without seeing your formula, it's hard to know. Did you perhaps omit the tilde
(~) before each asterisk?

Does your data differ in format from that which you posted with regard to the
<spaces>? If so, and if it is variable, you might want to remove the spaces
from the data to "normalize" it.

=IF(ISNUMBER(SEARCH("Derivatives36~*--",
SUBSTITUTE(A2," ",""))),0.4,
IF(ISNUMBER(SEARCH("Derivatives36-~*-",
SUBSTITUTE(A2," ",""))),0.6,
IF(ISNUMBER(SEARCH("Derivatives36--~*",
SUBSTITUTE(A2," ",""))),0.8,"Not Found")))


--ron