Excel Averages III

[Guest]

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

 

[Guest]

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

 

[Guest]

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.

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

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

 

[Guest]

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

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.

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

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

 

[Guest]

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.

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

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.

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


:

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

 

[Guest]

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

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.

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

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.

:

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


:

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

 

[Peo Sjoblom]

=COUNTIF(Range,"Medium")

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


--
Regards,

Peo Sjoblom

(No private emails please)

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.

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

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.

:

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


:

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

 

[Guest]

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

=COUNTIF(Range,"Medium")

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


--
Regards,

Peo Sjoblom

(No private emails please)

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.

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


:

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.

:

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


:

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

 

[Guest]

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

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

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.

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


:

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.

:

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


:

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

 

[Guest]

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

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

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

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.

:

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


:

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.

:

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


:

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

 

[Guest]

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.

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

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

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


:

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.

:

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


:

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.

:

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


:

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

 

[Guest]

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

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

Does that help?

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

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.

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

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

:

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


:

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.

:

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


:

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.

:

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


:

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

 

[Guest]

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.

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

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

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


:

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.

:

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


:

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.

:

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


:

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

 

[Guest]

You're very welcome, Weave.

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

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.

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

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

:

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


:

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.

:

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


:

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.

:

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


:

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

 

[Guest]

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

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

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

Does that help?

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

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.

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


:

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

:

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


:

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.

:

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


:

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.

:

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


:

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