Excel Averages III

W

Weave

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

Ron Coderre

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
 
W

Weave

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 said:
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 said:
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).
 
R

Ron Coderre

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 said:
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 said:
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 said:
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).
 
W

Weave

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 said:
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 said:
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 said:
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).
 
R

Ron Coderre

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 said:
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 said:
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 said:
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).
 
P

Peo Sjoblom

=COUNTIF(Range,"Medium")

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


--
Regards,

Peo Sjoblom

(No private emails please)


Weave said:
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 said:
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 said:
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).
 
W

Weave

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

Peo Sjoblom said:
=COUNTIF(Range,"Medium")

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


--
Regards,

Peo Sjoblom

(No private emails please)


Weave said:
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 said:
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).
 
W

Weave

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 said:
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 said:
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 said:
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).
 
R

Ron Coderre

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 said:
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 said:
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 said:
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).
 
W

Weave

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 said:
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 said:
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 said:
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).
 
R

Ron Coderre

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


Weave said:
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 said:
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 said:
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).
 
W

Weave

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 said:
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 said:
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 said:
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).
 
R

Ron Coderre

You're very welcome, Weave.

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


Weave said:
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 said:
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 said:
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).
 
W

Weave

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

Ron Coderre said:
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


Weave said:
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 said:
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).
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top