Excel Averages III
 

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).

Excel Averages III
 

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).

Excel Averages III
 

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).

Excel Averages III
 

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).

Excel Averages III
 

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).

Excel Averages III
 

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).

Excel Averages III
 

=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).

Excel Averages III
 

Thank yoiu Peo...I just need to average how many times "medium" is listed as
oppose to the "highs" and "lows."

"Peo Sjoblom" wrote:

=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).

Excel Averages III
 

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

As far as the 3 highs, 3 mediums, 3 lows...good question. Now you've stumped
me (ha, ha). Didn't think about that one.

"Ron Coderre" wrote:

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

OK...Let's see if this is progress:

=IF(SUMPRODUCT(--(COUNTIF(A1:A9,{"High","Medium","Low"})=(ROWS(A1:A 9)/3)))=3,"Even
Dist",CHOOSE(MODE(MATCH(A1:A9,{"High","Medium","Lo w"},0)),"High","Medium","Low"))

Does that help?

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


"Weave" wrote:

As far as the 3 highs, 3 mediums, 3 lows...good question. Now you've stumped
me (ha, ha). Didn't think about that one.

"Ron Coderre" wrote:

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

oops...forgot my reply about the most occurrences. this seems to work pretty
good. if there are two or more of the same it results to the higher finding
which is fine. ron i wish i could buy you a coke. thank you so much.

"Ron Coderre" wrote:

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

You're very welcome, Weave.

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


"Weave" wrote:

oops...forgot my reply about the most occurrences. this seems to work pretty
good. if there are two or more of the same it results to the higher finding
which is fine. ron i wish i could buy you a coke. thank you so much.

"Ron Coderre" wrote:

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).

Excel Averages III
 

i copied and pasted this formula, but it's not working.

"Ron Coderre" wrote:

OK...Let's see if this is progress:

=IF(SUMPRODUCT(--(COUNTIF(A1:A9,{"High","Medium","Low"})=(ROWS(A1:A 9)/3)))=3,"Even
Dist",CHOOSE(MODE(MATCH(A1:A9,{"High","Medium","Lo w"},0)),"High","Medium","Low"))

Does that help?

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


"Weave" wrote:

As far as the 3 highs, 3 mediums, 3 lows...good question. Now you've stumped
me (ha, ha). Didn't think about that one.

"Ron Coderre" wrote:

I was a littel puzzled by that, myself.

This returns the value with the most occurrences:

=CHOOSE(MATCH(MAX(COUNTIF(A1:A9,{"High","Medium"," Low"})),COUNTIF(A1:A9,{"High","Medium","Low"}),0), "High","Medium","Low")

But if there are 3 High's, 3 Medium's, and 3 Low's which do you want to see?

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


"Weave" wrote:

Do I need to assign a numeric value to the text? What about a formula that
can average "the most common occurrence" text?

"Ron Coderre" wrote:

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).