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
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 |
#6
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 |
#7
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!!! |
#8
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 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
On Jan 15, 8:41*am, "Don Guillett" wrote:
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))) Thanks! That does work great.... However (ugh), I should probably give you the FULL scope of what we are doing: There are EIGHT selection criterias. So it isn't just Country & Type - it's Country, Type, Field3, Field4, Field5, Field6, Field7, Field8 They can select "Total" for any or all of the fields, or any combination of the fields. (IE, they can select All countries, All Types, All Field 6s...but then break it down within Field3, Field4, Field5, Field7, Field8). So using the IFs will make for an extremely large formula; one with a LOT of possibilities - not sure if Excel could even handle such a thing. I just wish there was a way within the AVERAGE(IF where I can use a wildcard or something that says "Show all records if Total is Selected for this particular field". It's pretty much trying to get Excel to mimic the auto-filter feature...which I understand is what Average(IF does, but perhaps not to the extent needed here. I could send you the file if you wanted to take a closer look at what I am doing.... Tom |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
Sounds like it would be better to use a macro or perhaps a UDF (user defined
function). If all else fails, send the workbook to my address below along with an inserted sheet with snippets of these messages and complete explanations, including before/after examples. -- Don Guillett Microsoft MVP Excel SalesAid Software "Tom K" wrote in message ... On Jan 15, 8:41 am, "Don Guillett" wrote: 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))) Thanks! That does work great.... However (ugh), I should probably give you the FULL scope of what we are doing: There are EIGHT selection criterias. So it isn't just Country & Type - it's Country, Type, Field3, Field4, Field5, Field6, Field7, Field8 They can select "Total" for any or all of the fields, or any combination of the fields. (IE, they can select All countries, All Types, All Field 6s...but then break it down within Field3, Field4, Field5, Field7, Field8). So using the IFs will make for an extremely large formula; one with a LOT of possibilities - not sure if Excel could even handle such a thing. I just wish there was a way within the AVERAGE(IF where I can use a wildcard or something that says "Show all records if Total is Selected for this particular field". It's pretty much trying to get Excel to mimic the auto-filter feature...which I understand is what Average(IF does, but perhaps not to the extent needed here. I could send you the file if you wanted to take a closer look at what I am doing.... Tom |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Summing up Data
On Jan 15, 2:44*pm, "Don Guillett" wrote:
Sounds like it would be better to use a macro or perhaps a UDF (user defined function). If all else fails, send the workbook to my address below along with an inserted sheet with snippets of these messages and complete explanations, including before/after examples. I *think* I may have stumbled onto a solution, using a bunch of INDIRECT formulas and SEARCH formulas. I will post my "solution" for future reference IF I can prove that what I did works for all of the different scenarios! :) Thanks again!!! |
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) |