Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving averages in Excel | Excel Discussion (Misc queries) | |||
Stop Excel Rounding Dates | Excel Discussion (Misc queries) | |||
Hints And Tips For New Posters In The Excel Newsgroups | Excel Worksheet Functions | |||
Excel error - Startup (and Acrobat PDFMaker) | Setting up and Configuration of Excel | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) |