I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

Ron,

Sorry for the guessing game. Good guessing though. I tried it, but I'm
still a little skeptical. The drop-down lists of high, medium, low are
located in E10, E12, E15, E18, E22, E25, E28, E32, and E34. If 5 of those
are high, 2 are medium, and 2 are low is "medium" the correct "average" and
why? Thanks alot Ron.

"Ron Coderre" wrote:

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

Well....you tell me.
I treated the data as if it was rankings: High:1, Med:2, Low:3 and
calculated the average.

The average of 5 High, 2 Med, and 2 Low
is the average of {1,1,1,1,1,2,2,3,3}

Which is 1.6666....

which rounds to 2.

Unless you want to have:
High, Med-High, Med, Med-Low, Low

Am I on the right track here?

***********
Regards,
Ron


"Weave" wrote:

Ron,

Sorry for the guessing game. Good guessing though. I tried it, but I'm
still a little skeptical. The drop-down lists of high, medium, low are
located in E10, E12, E15, E18, E22, E25, E28, E32, and E34. If 5 of those
are high, 2 are medium, and 2 are low is "medium" the correct "average" and
why? Thanks alot Ron.

"Ron Coderre" wrote:

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

The data has no number value...just text. I want to get an average of how
many times the highs, mediums, and lows are listed.

"Ron Coderre" wrote:

Well....you tell me.
I treated the data as if it was rankings: High:1, Med:2, Low:3 and
calculated the average.

The average of 5 High, 2 Med, and 2 Low
is the average of {1,1,1,1,1,2,2,3,3}

Which is 1.6666....

which rounds to 2.

Unless you want to have:
High, Med-High, Med, Med-Low, Low

Am I on the right track here?

***********
Regards,
Ron


"Weave" wrote:

Ron,

Sorry for the guessing game. Good guessing though. I tried it, but I'm
still a little skeptical. The drop-down lists of high, medium, low are
located in E10, E12, E15, E18, E22, E25, E28, E32, and E34. If 5 of those
are high, 2 are medium, and 2 are low is "medium" the correct "average" and
why? Thanks alot Ron.

"Ron Coderre" wrote:

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

OK...I need a little clarification.

I understand that the values are text and not numbers. However, it's not
possible to actually average text.
Example: What's the average of "this is not a number"?

Consequently, I assigned a numeric value to each rating by listing them in
the MATCH function. The numbers are effectively the position of the text in
the string array {"High","Medium","Low"}:
High: 1
Medium: 2
Low: 3

I averaged those values. Then, using that average, I translated it back to
the High, Medium, Low scale.

My thinking (and hope) was....if the average works for numbers, then it
works for the translated numbers.

***********
Regards,
Ron


"Weave" wrote:

The data has no number value...just text. I want to get an average of how
many times the highs, mediums, and lows are listed.

"Ron Coderre" wrote:

Well....you tell me.
I treated the data as if it was rankings: High:1, Med:2, Low:3 and
calculated the average.

The average of 5 High, 2 Med, and 2 Low
is the average of {1,1,1,1,1,2,2,3,3}

Which is 1.6666....

which rounds to 2.

Unless you want to have:
High, Med-High, Med, Med-Low, Low

Am I on the right track here?

***********
Regards,
Ron


"Weave" wrote:

Ron,

Sorry for the guessing game. Good guessing though. I tried it, but I'm
still a little skeptical. The drop-down lists of high, medium, low are
located in E10, E12, E15, E18, E22, E25, E28, E32, and E34. If 5 of those
are high, 2 are medium, and 2 are low is "medium" the correct "average" and
why? Thanks alot Ron.

"Ron Coderre" wrote:

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift] when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values whereby the
values are displayed in text. There is only one text value listed per cell
for about 9 rows. I need the average results displayed in text. (i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the average of
highs-lows displayed in the words, "high", "medium", or "low" (w/out the
quotations).

=COUNTIF(Range,"Medium")

will count how many times medium is in a range called range


--
Regards,

Peo Sjoblom

(No private emails please)


"Weave" wrote in message
...
The data has no number value...just text. I want to get an average of how
many times the highs, mediums, and lows are listed.

"Ron Coderre" wrote:

Well....you tell me.
I treated the data as if it was rankings: High:1, Med:2, Low:3 and
calculated the average.

The average of 5 High, 2 Med, and 2 Low
is the average of {1,1,1,1,1,2,2,3,3}

Which is 1.6666....

which rounds to 2.

Unless you want to have:
High, Med-High, Med, Med-Low, Low

Am I on the right track here?

***********
Regards,
Ron


"Weave" wrote:

Ron,

Sorry for the guessing game. Good guessing though. I tried it,
but I'm
still a little skeptical. The drop-down lists of high, medium, low are
located in E10, E12, E15, E18, E22, E25, E28, E32, and E34. If 5 of
those
are high, 2 are medium, and 2 are low is "medium" the correct "average"
and
why? Thanks alot Ron.

"Ron Coderre" wrote:

Guessing here....

With High's, Medium's, and Low's dispersed in cells A1:A10,
Is this what you're looking for?
=CHOOSE(ROUND(AVERAGE(MATCH(A1:A10,{"High","Medium ","Low"},0)),0),"High","Medium","Low")

Note: Confirm that array formula by holding down [Ctrl] and [Shift]
when you
press [Enter].

Does that help?

***********
Regards,
Ron


"Weave" wrote:

I'm trying to get an average on high, medium, and low values
whereby the
values are displayed in text. There is only one text value listed
per cell
for about 9 rows. I need the average results displayed in text.
(i.e.
A1=high, A2=medium, A3=low, etc.). In a separate cell I need the
average of
highs-lows displayed in the words, "high", "medium", or "low"
(w/out the
quotations).