Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
I will ask my question through an example:
Possible countries: China, United States, Russia Possible customer types: Lost, Standard Questions: Q1, Q2 So a few data records may look like this: Country Type Q1 Q2 China Lost 5 4 China Standard 4 3 China Lost 2 3 Russia Standard 5 4 Russia Lost 3 3 Let's say I have a file with 5,000 records in it, all with a mix of the countries and customer types above. Someone comes and asks: "I want to know the mean score of the data at Question 1 amongst people who are in the country China with a customer type of Lost". (from the example above, the answer would be 3.5 - the average of Records 1 & 3) How would I accomplish this? Note that my project goes much deeper than this, but I figure if I can just get a basic foundation, I'll be able to build the rest (if this is even possible in the first place) Thanks for ANY assistance! Tom |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
On Jan 12, 5:33*pm, "Don Guillett" wrote:
Try this. Be sure to array enter by using ctrl+shift+enter instead of just enter =AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C2:D2 2)) Excellent! This may save me loads of time - thank you! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
Let us know how it works out. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tom K" wrote in message ... On Jan 12, 5:33 pm, "Don Guillett" wrote: Try this. Be sure to array enter by using ctrl+shift+enter instead of just enter =AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C2:D2 2)) Excellent! This may save me loads of time - thank you! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
On Jan 12, 8:06*pm, "Don Guillett" wrote:
Let us know how it works out. Thanks again - but of course, now I need more! It works great for the example I gave, but there was more to this than I thought. Let's say the scenario comes up where the person decides that they want the total average - not just the average of China Lost...but the average of everyone. This is within an application (with drop-down boxes, etc.), so it's interactive. The end-user can choose China & Lost.....but they can also simply choose Total & Total. (5+4+2+5+3)/ 6..... Or China & Total. (5+4+2)/3. Is there any way to incorporate that into AVERAGE(IF( ? Let me know if this isn't making sense.....guess I am looking for something like this: If enduser selects China & Lost: =AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C2:D2 2)) However, if enduser chooses China & All Types: =AVERAGE(IF((A2:A22="china")*(B2:B22=ALL RECORDS),C2:D22)) However, if enduser chooses All Countries & All Types: =AVERAGE(IF((A2:A22=ALL RECORDS)*(B2:B22=ALL RECORDS),C2:D22)) Country Type Q1 Q2 China Lost 5 4 China Standard 4 3 China Lost 2 3 Russia Standard 5 4 Russia Lost 3 3 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
Try incorporating an IF ie:
=IF(I3="total",AVERAGE(IF(A2:A22=H3,C2:C22)),AVERA GE(IF((A2:A22=H3)*(B2:B22=I3),C2:D22))) -- Don Guillett Microsoft MVP Excel SalesAid Software "Tom K" wrote in message ... On Jan 12, 8:06 pm, "Don Guillett" wrote: Let us know how it works out. Thanks again - but of course, now I need more! It works great for the example I gave, but there was more to this than I thought. Let's say the scenario comes up where the person decides that they want the total average - not just the average of China Lost...but the average of everyone. This is within an application (with drop-down boxes, etc.), so it's interactive. The end-user can choose China & Lost.....but they can also simply choose Total & Total. (5+4+2+5+3)/ 6..... Or China & Total. (5+4+2)/3. Is there any way to incorporate that into AVERAGE(IF( ? Let me know if this isn't making sense.....guess I am looking for something like this: If enduser selects China & Lost: =AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C2:D2 2)) However, if enduser chooses China & All Types: =AVERAGE(IF((A2:A22="china")*(B2:B22=ALL RECORDS),C2:D22)) However, if enduser chooses All Countries & All Types: =AVERAGE(IF((A2:A22=ALL RECORDS)*(B2:B22=ALL RECORDS),C2:D22)) Country Type Q1 Q2 China Lost 5 4 China Standard 4 3 China Lost 2 3 Russia Standard 5 4 Russia Lost 3 3 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
Tom,
you can use subtotals for this i have worked on your example and the result is as following. Country Type Q1 Q2 China Lost 5 4 China Standard 4 3 China Lost 2 3 China Average 3.666666667 3.333333333 Russia Standard 5 4 Russia Lost 3 3 Russia Average 4 3.5 India Standard 5 5 India Average 5 5 China Lost 2 1 China Lost 3 4 China Average 2.5 2.5 Grand Average 3.625 3.375 While using subtotals 1) change at every (country) 2) Use function (Average) 3) Add subtotals to (Q1 & Q2) please uncheck replace the current subtotals and check summary below data That will work for you -- Thanks Suleman Peerzade "Tom K" wrote: I will ask my question through an example: Possible countries: China, United States, Russia Possible customer types: Lost, Standard Questions: Q1, Q2 So a few data records may look like this: Country Type Q1 Q2 China Lost 5 4 China Standard 4 3 China Lost 2 3 Russia Standard 5 4 Russia Lost 3 3 Let's say I have a file with 5,000 records in it, all with a mix of the countries and customer types above. Someone comes and asks: "I want to know the mean score of the data at Question 1 amongst people who are in the country China with a customer type of Lost". (from the example above, the answer would be 3.5 - the average of Records 1 & 3) How would I accomplish this? Note that my project goes much deeper than this, but I figure if I can just get a basic foundation, I'll be able to build the rest (if this is even possible in the first place) Thanks for ANY assistance! Tom |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
On Jan 13, 1:44*am, Suleman Peerzade
wrote: Tom, you can use subtotals for this i have worked on your example and the result is as following. Country Type * *Q1 * * *Q2 China * Lost * *5 * * * 4 China * Standard * * * *4 * * * 3 China * Lost * *2 * * * 3 China Average * * * * * 3.666666667 * * 3.333333333 Russia *Standard * * * *5 * * * 4 Russia *Lost * *3 * * * 3 Russia Average * * * * *4 * * * 3.5 India * Standard * * * *5 * * * 5 India Average * * * * * 5 * * * 5 China * Lost * *2 * * * 1 China * Lost * *3 * * * 4 China Average * * * * * 2.5 * * 2.5 Grand Average * * * * * 3.625 * 3.375 While using subtotals 1) change at every (country) 2) Use function (Average) 3) Add subtotals to (Q1 & Q2) please uncheck replace the current subtotals and check summary belowdata This is a very interesting approach. (I like multiple ideas!) I will give it a whirl; I've played with the subtotal function in the past, but have never used it as part of a finished "product" :) Thanks!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing filtered data | Excel Worksheet Functions | |||
summing data | Excel Discussion (Misc queries) | |||
summing data | Excel Worksheet Functions | |||
Summing data | Excel Discussion (Misc queries) | |||
Summing Time Data | Excel Discussion (Misc queries) |