Summing up Data

[Tom K]

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

 

[Don Guillett]

Try this. Be sure to array enter by using ctrl+shift+enter instead of just
enter

=AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C222))

 

[Tom K]

Try this. Be sure to array enter by using ctrl+shift+enter instead of just
enter

=AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C222))

Excellent! This may save me loads of time - thank you!

 

[Don Guillett]

Let us know how it works out.
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

Try this. Be sure to array enter by using ctrl+shift+enter instead of just
enter

=AVERAGE(IF((A2:A22="china")*(B2:B22="lost"),C222))

Excellent! This may save me loads of time - thank you!

 

[Suleman Peerzade]

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

 

[Tom K]

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"),C222))

However, if enduser chooses China & All Types:
=AVERAGE(IF((A2:A22="china")*(B2:B22=ALL RECORDS),C222))

However, if enduser chooses All Countries & All Types:
=AVERAGE(IF((A2:A22=ALL RECORDS)*(B2:B22=ALL RECORDS),C222))


Country Type Q1 Q2
China Lost 5 4
China Standard 4 3
China Lost 2 3
Russia Standard 5 4
Russia Lost 3 3

 

[Tom K]

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!!!

 

[Don Guillett]

Try incorporating an IF ie:
=IF(I3="total",AVERAGE(IF(A2:A22=H3,C2:C22)),AVERAGE(IF((A2:A22=H3)*(B2:B22=I3),C222)))

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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"),C222))

However, if enduser chooses China & All Types:
=AVERAGE(IF((A2:A22="china")*(B2:B22=ALL RECORDS),C222))

However, if enduser chooses All Countries & All Types:
=AVERAGE(IF((A2:A22=ALL RECORDS)*(B2:B22=ALL RECORDS),C222))


Country Type Q1 Q2
China Lost 5 4
China Standard 4 3
China Lost 2 3
Russia Standard 5 4
Russia Lost 3 3

 

[Tom K]

Try incorporating an IF ie:
=IF(I3="total",AVERAGE(IF(A2:A22=H3,C2:C22)),AVERAGE(IF((A2:A22=H3)*(B2:B22=I3),C222)))

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

 

[Don Guillett]

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
(e-mail address removed)

Try incorporating an IF ie:
=IF(I3="total",AVERAGE(IF(A2:A22=H3,C2:C22)),AVERAGE(IF((A2:A22=H3)*(B2:B22=I3),C222)))

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

 

[Tom K]

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!!!